BI Blogs

I’m now back in my hotel room, having escaped from the Focus Pubs at the end of Day 1 of the UKOUG Technology and E-Business Suite Conference in Birmingham. It’s been a long day, with an 8am meeting on the stand in the exhibition, a stint in the speaker ready room prepping my demonstrations, and then delivery of two sessions this afternoon on OBIEE, Essbase and Oracle Warehouse Builder 11gR2.

This year we’ve brought most of the consulting team along to the conference, as a way of getting to see people who are often working away on-site, and so that everyone can get to meet the great delegates, speakers and partners at the conference. Here’s the team at the start of the day, having just set up the demo and waiting for the opening of the exhibition.

And here’s things at the end of the day, during the Exhibition Hall Drinks Reception. We had our famous Rittman Mead beer available again, and it was great to catch up with people such as Paul Vallee, Connor McDonald, Jonathan Lewis, Lisa Dobson and everyone else who stopped by.

The two sessions we ran today were on Essbase and OBIEE Integration, and on the new 11gR2 release of OWB. The Essbase and OBIEE session went well; we had Venkat doing the demos, myself doing the positioning, and then a great questions and answer session at the end where we discussed attendees experiences with the integration and gave some feedback on what worked for us. The slides are available here if you’re interested.

The second session was particularly well attended and was on the 11gR2 version of OWB. Now if you’ve been following our blog, we’ve posted a series of articles on this new release, and we’re particularly excited by the new code template (ODI) features in this release, especially as the integration between the ODI and OWB technologies seems to have been done so well. We were lucky enough to be joined by Craig Stewart, our Oracle ODI representative in EMEA and someone we know well from his attendance at various user group events and indeed our own BI Forum earlier in the year in Brighton. Craig was able provide a “hot off the presses” roadmap slide on OWB and ODI 12g which he’s kindly agreed for me to reproduce here:

What we can take from this are a few points:

• Going forward, the OWB product name will be de-emphasized in favour of “In-Database ETL”, to differentiate itself from Oracle’s strategic data integration platform, ODI
• The 11gR2 version of OWB will be the last in it’s current packaging, and license-wise it’ll now be incorporated into ODI Enterprise Edition. The free OWB functionality will still remain but will be renamed “In-Database ETL” as above.
• ODI 12g is the target version for integration between the ODI and OWB platforms
• There will be an ODI 11g release out in the first half of next year (usual disclaimers apply
• ODI is the strategic data integration platform for Oracle

Again, the slides are available for download here if you want to take a look, and they cover code templates, heterogeneous sources, OBIEE integration, change data capture and web service integration.

Craig is actually going to join me again tomorrow for the ODI and Oracle BI Applications session I’ll be running (14.30 – 15.30, Tuesday, Hall 10b), so again if you’ve got questions on the ETL direction for the BI Applications and the role that ODI will play, come along and we’ll be pleased to answer any questions.

Finally, if you’re around tomorrow (Tuesday) evening, we’re running a BI “Appreciation” drinks session at the Pitcher and Piano, Brindley Place, from 6pm – 9pm. If you’re a BI user, customer, developer, partner etc, or you just know us and would like to join us for drinks and canapes, come down in the evening and it’ll be great to see you.

In the first instalment in this series, we covered the steps that we carry out when promoting the initial version on an OBIEE project from development into production. The process is fairly straightforward and it’s generally a case of using the right tools (the Catalog Manager, for example) and following the list of steps. Things get a bit more interesting when promoting subsequent versions of the project from one environment to another, chiefly because you will probably want to preserve what’s in production (reports generated by users, for example, and maybe some hot fixes to the RPD) rather than just overwrite it all with what’s in the development environment.

One way of looking at this is as a flowchart, where the route we end up taking depends on how much development has also taken place in the production environment:

If you want to keep life simple, then don’t allow users to create any reports in production; don’t allow changes to the RPD in production; instead, do all development in the development environment and then just repeat the initial product deployment tasks each time you want to promote some changes, in the process over-writing whatever’s in production. In reality though, this isn’t really practical, as users will at a minimum want to create their own reports and there may be some hot-fixes applied to the production RPD that you want to preserve. If this is the case, you’ll want a follow a slightly more nuanced approach to promoting from development to production, making use of the three-way merge facility in the BI Administrator tool, the selective archive and unarchive facility in the Catalog Manager, and the new Content Accelerator Framework plug-in to the Catalog Manager that’s now available for download from OTN.

Assuming you’re working to this situation and your development team have updated the development RPD, created a few new reports based on these changes and now want to promote them to production, here’s the steps we’d typically follow:

1. If you’re sure that no changes have been made in the production RPD that are not already reflected in the development one, you can just copy across the development RPD to production and have done with it. The safer way to update the production RPD would be to three-way merge the development one into it, as this will preserve the changes made in production or at least prompt you to choose between them and any subsequent updates in development that conflict with them.

To start a three-way merge, you’ll need the following three RPD files

i. A copy of the original RPD as initially put into production (this is known as the “baseline” RPD)
ii. A copy of the current production RPD (which may now differ from the baseline RPD)
iii The development RPD that you want to merge into the current production one.

Once you’ve got all these files together, we can start on the three-way merge of the RPDs.

2. To start merging, open up the BI Administration tool using an offline copy of the current production repository.

3. From the application menu, select File > Merge… and then select the baseline RPD as the original RPD.

4. The merge process will then parse the original and current production RPDs to identify and differences between them. If this is the first time you have updated the production RPD, and you’ve not made any changes to it in production, then this process will come up blank, otherwise it’ll point out any changes between the original and current production RPDs.

5. Now you can select the development RPD that you’d like to merge into the production one. Press the Select… button next to the Modified Repository area and pick up the development RPD. It’s most likely that there will be no conflicts (there may be though if you’ve applied hotfixes to the production one), and so you’ll need to press the Stats button to see what items will be added, deleted or updated during the merge process.

6. Now that the production RPD has been updated, it’s time to consider the web catalog. Again, if you don’t let users develop reports in production, or at least you don’t let them develop in the Shared area, you can just archive and then unarchive the Shared and/or the Users folders from the development web catalog to the production one, using the Catalog Manager, to copy across the new reports. If you just want to migrate a few individual reports though, there’s a new option in the form of the Content Accelerator Wizard that you can use for this purpose.

The Content Accelerator Wizard (CAF) is a plug-in to the Catalog Manager that migrates reports between two environments, and will also create any logical column calculations that the reports depend on but that aren’t found in the target RPD. Coupled with a standard RPD three-way merge this is quite a neat way to migrate individual reports from development to production, as you don’t need to have two copies of the Catalog Manager open at one time and it takes care of any calculated columns that you need.

After installing CAF and making sure that the Catalog Manager is pointing towards a JDK 1.6 or higher, open the Catalog Manager to point to the development environment, and select the report you wish to promote (or “clone”) into production.

7. When the clone process starts, you are prompted to select the target web catalog (this need to be an online catalog, and can be either local or a remote server), and the location of the source and target BI Server repository (RPD) file, which have to be connected to offline. The RPD files are required so that the cloning process can create any logical calculations that are needed by the cloned report (if the report requires full logical columns, you’ll need to add these to the target RPD using a three-way merge).

The wizard then prompts you to select the target subject area that the cloned report will be mapped on to.

8. Next comes the mapping process. The logical columns in the source web catalog need to be mapped to their corresponding columns in the target web catalog, together with dimension levels, something that you need to do manually but that can be saved, as a CSV file, to use again in later migrations. Any new calculated columns are added automatically by CAF, but if there isn’t a corresponding target column to map regular columns to then you’ll need to bring it in via an RPD three-way merge.

9. Finally, the migration takes place, and you can choose to perform a consistency check as well as view the log of migrated objects.

10. Once the migration has happened, you can take a look in the target web catalog and see the new report, ready to run and within the /Shared/Cloned folder. The target RPD will have any new calculated logical columns added, with “Autogen” as the name prefix.

So there you have it, one method to do incremental updates of OBIEE projects. In the next instalment, we’ll take a look at version control using Subversion and TortoiseSVN.

Today’s the first full day of the UKOUG Technology & EBS Conference in Birmingham, UK, and I’m currently getting my presentations ready for later on today. This year we’ve again got a stand in the main exhibition hall, where we’ll be running demos of OBIEE, Warehouse Builder and the rest of the Oracle BI technology stack. Yesterday though was all about getting up to Birmingham, getting the stand set up and then off in the evening for the second annual ACE Director meal.

The evening was organized by Debra Lilly and myself, with Todd from the OTN team kindly bringing along the credit card and passing along messages from Vikki, Lillian, Justin and the team back from HQ. We had an excellent turnout with 25 ACEs and ACE Directors from around the world, including such names as Steven Feuerstein, Bryn Llewellyn, and Mogens Norgaard, and it was a great chance to catch up with people such as Marco, Dimitri, Dan Morgan, Tim and others that I tend to see only at other conferences around the world.

Today’s a busy day for me, with the following two sessions running later today:

• “Oracle BI EE and Essbase Integration Step-by-Step”, Monday 14:45 – 15:45, presented by myself and Venkat Janakiraman
• “Oracle Warehouse Builder 11gR2 New Features”, Monday 16:15 – 17:00, presented by myself

We’ve also got a fun demo running on the stand (Stand 50) where we’re using OBIEE as part of a football management simulation, where you can pretend to be an investor looking to put money into any of the Premier League teams and seeing, through the use of analytics, writeback and the dashboard where this money would be put to best use. I’m trying to persuade Chris to rig the numbers so that Arsenal always get relegated and Spurs win the league, but I think at least the latter is a bit beyond the powers of Oracle BI – perhaps we need to deploy Essbase in the background. Anyway, if you’re at the conference then be sure to pop by stand 50 if you get a moment, otherwise hopefully I’ll see a few people at the various conference sessions.

The people in an audience who ask what seems like a rude question to a speaker on stage are often the ones worth listening to. Take, for example, the guy the other night. He talked about his movie camera, but he might as well have been talking about BI tools.

He said that he had had a Bolex 16-milimeter movie camera back when he was a kid, and so anyone else could have, too. To him, the idea that new technology like small video cameras and inexpensive desktop editing was now unleashing a burst of moviemaking was just not true. “It’s nonsense. You know it is,” he told the Oscar-winner Walter Murch, who sat listening patiently on stage.

I didn’t write down Murch’s reply, but I have my own: Great as the Bolex may have been, amateur filmmaking back then was slow, expensive, and lonely. There were no swarms of fellow filmmakers and no audience on YouTube. Bolex Schmolex.

Cinema, and business, depend on more than technology.

Murch’s main point, which he explained for almost an hour, was about cinema’s quick success a century ago. It took off, he said, because the popular culture was prepared for it, not just because the technology had arrived.

Just one of the “three fathers of cinema,” as Murch calls them, had anything to do with the technology: Thomas Edison. Beethoven, and introduced dynamism into music instead of the ordered music of Haydn and Mozart. Also Flaubert, another name as shorthand for the new painters and fiction writers who discarded fantasy and aristocratic life for everyday reality.

Does he mean that everyone in the bargain matinee seats a Beethoven fan? No, but I wish I’d asked how it worked. For now, I go with the teabag theory: a bit of pungent herbs have a way of permeating the surrounding medium. Just ask Sarah Palin.

From Beethoven’s dynamism, it’s a short leap into the vocabulary we know today: fast cuts, close-ups followed by panoramas, stories interlaced with other stories, and so on.

Imagine a tool that falls into a culture that’s not ready. Say some ancient toymaker invented the wheel but for centuries afterward the adults kept dragging freight around on sleds. That’s apparently what the Aztecs did. Same thing happened to the steam engine invented by the Greeks.

I wish I could raise my hand now to ask Murch a few questions: For example, could cinema have taken root with a Mozartian vocabulary instead of a Beethovenian one? I suppose we’d have nothing like “Citizen Kane” and a lot of films like “This is My Railroad” (1940; Southern Pacific).

Who can say about movies, though? It’s much easier to speculate whether BI can take root in an organization with no fathers or mothers of data analysis.

I’m currently sitting in speaker ready room at the UKOUG Technology & EBS Conference up in Birmingham, and the team are on the stand now ready to meet people at the exhibition. I’ve got an hour or so free now this morning, and so I thought it’d be a good opportunity to blog about one of the sessions we’re delivering later this week at the conference.

The session I’m thinking about is our OBIEE Masterclass that’s running for two hours on the last day of the conference. In previous years we’ve covered the basics of OBIEE development together with performance tuning tips for the BI Server and the underlying Oracle database, and as we’re still waiting for the 11g release to come out, we thought we’d devote this year’s session to some of the perennial questions that come up on the forums, on our blog and when we work with customers. We’re therefore spending half of the masterclass on OBIEE data modeling questions, specifically how do we model normalized data sources, single-table sources and un-conformed star schemas, whilst the second half of the session is being devoted to software configuration management for OBIEE projects.

Software Configuration Management is a topic that seems to come up on most projects, with customers at least wanting to version control their project and have a means to promote code between development, test and production environments. Now there’s not really a defined, standard way to do this with OBIEE projects, and certainly on this blog we’ve talked about various ways to do this, and others have added to the debate with alternatives and suggestions on how to do things better. We’ve also had a utility released, the “Content Accelerator Framework”, that some people have talked about as being a good tool for promoting changes between environments, and now that OBIEE projects are getting more mature and more “enterprise” we’ve also had requests for tips on code branching, code merging and other common software configuration management (SCM) techniques.

So this seemed to me a good area to cover in our OBIEE masterclass. As we’ve got a fair few experts on OBIEE within the company, including people like Venkat J, Adrian Ward, Borkur Steingrimsson and Christian Berg, we had a fair bit of internal debate on what works, what doesn’t work and what’s practical within a project. If you’re interested, I’ve uploaded the resulting slides here and over the next few blog posts, I’ll go through what we came up with, starting in this post with tips on the initial deployment of a project.

To get back to basics though, if you’re working with OBIEE on a project, there are a number of SCM tasks that you’ll want to carry out:

• We’ll want to set up separate development (DEV), production (PROD), and perhaps test (TST) and user acceptance testing (UAT) environments
• We’ll then want to be able to promote OBIEE metadata and configuration files between these environments
• You’ll want to be able to promote individual reports between environments, and create them for users to use
• You’ll typically want to version your project, so that you can identify releases and refer back to previous versions of the project
• You may want to branch the code, or work concurrently on separate but linked projects, and then merge the code back into a single code stream

Within an OBIEE system (disregarding the BI Apps or the underlying data warehouse for the time being), there are a number of project elements that you’ll want to include in this process, including:

• The BI Server repository file (the “RPD” file)
• The BI Presentation Server web catalog (the “webcat”)
• various configuration files (the BI server .INI files, instanceconfig.xml etc)
• various other artifact files (used for setting up writeback, for example), and
• various web files (CSS etc) if you’ve customized the dashboard UI

For the purposes of this posting, we’ll assume you’ve just got DEV and PROD environments (if you have TEST, UAT etc as well, the process is very similar with additional steps in between when you promote code from one environment to the other). For now though we’ll assume you have just a DEV environment, which is where the developers edit and develop the RPD together with a set of shared reports and dashboards; and PROD environment, where the RPD is “read-only” but users create their own reports in their own web catalog folders.

The first thing to bear in mind here, is that you should generally try and have a single BI Server per physical/virtual server, which you may end up clustering with other single BI Servers on other single physical/virtual servers. There are a few workarounds where you can set up a single BI Server with multiple RPDs attached, or indeed install multiple BI Server instances on the same physical/virtual servers, but this is not recommended as OBIEE 10g isn’t really designed for this and you hit issues around files, configuration settings and so on being inadvertently shared amongst all environments. If you want to set up multiple BI Servers on the same box, use VMWare or another such virtualization product to create OS containers and work from there. I have heard that support for multiple BI Servers on the same OS environment is coming in the 11g release, but for now it’s not recommended.

So once you’ve got DEV and TEST environments set up, and you’ve developed your initial system in the DEV environment, how do you go about making use of the PROD environment? For us, you can generally divide up code promotion into two stages:

1. The initial deployment of the project from DEV to PROD
2. Subsequent updates of PROD from DEV, for example new releases of the project

This initial release typically involves copying the whole project, including the RPD, the web catalog, the supporting web, configuration and artifact files, from the DEV environment to the PROD one, and we don’t have to worry about merging, overwriting or preserving what’s currently in PROD (as there won’t be anything there yet). The second and subsequent migration require a bit more thought though as you’ll typically want to preserve what’s already in production.

Doing the initial DEV to PROD code promotion is therefore a fairly straightforward process, as there aren’t really any decision points, more a series of steps to remember, as shown in the flowchart below:

Starting from the beginning, the typical steps you’d want to perform in this initial migration are:

1. Create a temporary directory somewhere on the DEV server, into which we’ll put all the files to migrate. Shut down the BI Server if the RPD you want to migrate is running online,

and then copy the development RPD to this temporary folder.

2. Next, start the Catalog Manager utility and connect to the web catalog either online or offline. Select (click on) the Shared folder, and then from the application menu select File > Archive. Archive the shared web catalog folder to a file in the temporary directory you created earlier, and then repeat this for the Users directory if there are reports in there that you want to migrate. Don’t archive the whole web catalog, or the System folder, as the production web catalog will get corrupted if you try and copy the system folder from another catalog into it (you’ll not be able to connect to any dashboards or to Answers).

3. Now gather up the various configuration, artifact and other files that you need to copy from this environment to the production one. This list isn’t exhaustive, but you’d typically want to gather up the following files and copy them into the temporary directory on the development server.

$ORACLEBI/server/config/*.*
$ORACLEBIDATA/web/config/instanceconfig.xml
$ORACLEBIDATA/web/config/xdo.cfg
$ORACLEBI/web/javahost/config/config.xml
$ORACLEBI/xmlp/XMLP/Users
$ORACLEBI/xmlp/XMLP/Reports

plus any files that you’ve used to customize the dashboard UI, set up writeback and so on. Once you’ve done this, zip up the temporary directory ready for transferring to the production server.

4. Now copy this ZIP file from the DEV to the TEST server, unzip the file, and copy the files to the correct location on the production server, including the RPD that contains the BI Server repository. Watch out for files like NQSCONFIG.INI and instanceconfig.xml that reference machine names in the files, as you’ll need to update these to reflect the naming in the production environment.

5. It’s generally good practice to do any new project development into a fresh, blank RPD and web catalog, and so if the production server is still pointing towards the Sample Sales or Paint web catalog, you’ll want to create a new one into which you’ll copy the development webcat items in a moment. The safest way to create a new web catalog, with all permissions, folders and so on on created correctly, is to create a new folder in the web catalog folder ($ORACLEBIDATA/web/catalog), reference it in the instanceconfig.xml file and then stop and start the BI Presentation Server. When the Presentation Server starts and finds its pointing to a blank directory, it’ll automatically create all the folders and system items we need, ready for the migration will then do from the development environment.

6. Now it’s time to start the Catalog Manager tool and unarchive the Users and Shared webcat directories from the development environment. You can choose to keep timestamps and permissions, the latter becomes relevant if you’ve set up a security system in development that you want to copy across to production.

UPDATE: Discussing this web catalog migration technique with Venkat later, one suggestion that he has is to zip up the whole web catalog, transfer it to the production environment and then unzip it into the correct place, rather than use the Catalog Manager for this initial transfer. This is the approach that Oracle uses with the BI Applications web catalog, and it’s safe as long as you transfer the whole environment. It won’t work subsequently as you’ll have user reports to preserve, but this is the approach that Oracle uses for the initial setup of the web catalog and it may be easier to use compared to the approach above using the Catalog Manager.

7. The RPD that you copied across in step 5 will contain database passwords (and connection details) that may not be relevant in the production environment. If your DBA allows it, you can open up the RPD file and edit the connection pool settings to reflect the production settings.

There are various techniques around to do this in a scripted way, one of my colleagues has defined the database password as a variable and then updated this via an init block and a text file, another technique is this one by Venkat where he uses an undocumented command line interface to the BI Administration tool to set the database password.

8. Now that all of the files are in place, and you’ve copied the RPD over, set the database password(s) and migrated the web catalog entries, one step you’ll want to consider is to make the production RPD file read-only. This stops inadvertent changes to the RPD in production, though if this is inevitable (for quick fixes etc) you can always make it read-write and apply subsequent changes using the Merge feature recently introduced into the BI Administration tool. If you can though, make the production RPD read-only.

It’s worth taking a copy of the first production RPD at this stage, which we’ll call the “baseline RPD”, as we’ll need this later on if we apply subsequent RPD changes using the three-way merge feature in the BI Administration tool. Take this copy and place it somewhere safe, and we’ll use it at a later date when we start doing incremental updates to the production environment.

9. Now, in order to pick up all the changes you’ve introduced with the migrated files, stop and then restart the BI Server, BI Presentation Server, Javahost and BI Scheduler services, and the BI Cluster Controller if you’re using clustering.

10. You’re now ready to use your system in production. Users can now create new reports in production (within their own User folders, if you ware maintaining the Shared webcat folder in development and plan to overwrite it with each subsequent release), or developers can create reports in the development environment if they are dependent on updates they are making to the RPD, which will be put into production as part of a co-ordinated release.

So that’s how we promote the initial release of the project into production. What about subsequent releases, where we have updates to the RPD to promote and potentially some more shared reports and dashboards? Well, we’ll cover this in the next instalment of the series.

And my handwriting is crap, so I have decided to try tweeting all the stuff I need to remember (and yes got the idea from Angela at Zencos, thanks Angela)

You can follow my ramblings here:

http://twitter.com/optimalBI

(somebody already has sasInct, grrrrr)

When I worked briefly for Xero the office was full of very smart 20 ish olds who could tweet, icq, blog, drink coke, chat and code at the same time.

Lets see if I can do two things at once, i.e tweet and listen.  Of course being a man I can’t guarantee success.

Just a quick remind for anyone who’ll be attending the UKOUG Technology and EBS Conference at the Birmingham ICC next week. We’ll be exhibiting at Stand 50, and will also be delivering the following sessions:

• “Oracle BI EE and Essbase Integration Step-by-Step”, Monday 14:45 – 15:45, presented by myself and Venkat Janakiraman
• “Oracle Warehouse Builder 11gR2 New Features”, Monday 16:15 – 17:00, presented by myself
• “Under the covers of ODI and the Oracle BI Applications”, Tuesday 14.30 – 15.30, presented by myself
• “Oracle BI Enterprise Edition Masterclass”, Wednesday 13.50 – 15.50, presented by myself, Venkat, Adrian Ward and Borkur

This year at the Masterclass, as well as tackling the basics of modeling and reporting, we’ll be tackling four of the questions that most often come up from our customers:

• How do we model non-dimensional (OLTP) sources using the BI Administration tool;
• How do we join together fact tables that do not have the same dimensionality;
• How do we model single-table sources, and
• How do we (safely) migrate OBIEE projects from one environment to the other.

Although OBIEE 10g is a fairly mature technology now, these questions always come up and it seems that no-one is too sure about how to approach them. We’ll deliver a session on this (together with a general update on the product) and try and tackle them for you.

We’re also running a BI “Fringe Event” on the Tuesday night at the Pitcher and Piano from 6pm – 9pm, where we’ll put some money behind the bar, lay on some nibbles and try and get all the BI speakers, delegates and users together for an informal get together. We’ll be inviting all those that came to the BI Forum in May and the Training Days in October as a kind of “reunion”, but we’d be happy to have anyone along with something interesting to say on Oracle BI. If you want to come along, please send me an email or come along to Stand 50 so we can get an idea on numbers. Other than that, see you all in Birmingham in a few weeks time.

Everywhere I look at the moment I see my clients talking about needing to benchmark themselves against the market, to understand customer and prospect sentiment on social networking sites and to understand competitors in much more detail. It is not just me that has recognised this need. It also seems that new young startup companies have also seen this gap in the market. Over the last few days I have spent some time talking to Andrew Yates, CEO of Artesian and Christian Koestler, CEO of Lixto about their solutions in this area.

Artesian are are focused on monitoring media news, competitors intelligence and market intelligence that can be fed into front-office processes - in particular to sales force automation. Integration with SalesForce.com is provided as is delivery to mobile devices for mobile salespeople on the road. Their intention on media intelligence for example is to track coverage across all media channels contextually matched to commercial triggers or specific areas of interest.  What I like about Artesian is the fact that they have looked at how to drive revenue from intelligence derived from web content by plugging it into front-office processes. Also by adopting social software attached to front-office systems like SalesForce.com’s new Chatter offering it becomes possible to collaborate over this intelligence. I would like to see this solution integrate with Microsoft SharePoint and IBM Lotus Connections for more use in large enterprises. However, seeing the need to focus attention on content that has real value in the front office is a real strength of this young startup.  

Lixto has a integrated development environment that allows you to build analytic applications pulling data from web sites such as competitor price information, new competitor marketing campaign data and other information that can be loaded into their customisable analytic applications to monitor competitors for example. 

Extracting insight from external data is definately on the increase with YellowBrix and Mark Logic also in on the act. IBM jumped into the market back in October with their announcement of IBM Cognos Content Analytics. This market is heating up. It seems to me that the start-ups are out there with competitive offerings.

This pattern uses data virtualization to create one or more virtual data marts on top of a BI system thereby providing multiple summarised views of detailed historical data in a data warehouse. Different groups of users can then run ad hoc reports and analyses on these virtual data marts without interfering with each others’ analytical activity.

Pattern Diagram

Multiple ‘power user’ business analysts in the risk management department of a bank often need their own analytical environment to conduct specific in-depth analyses in order to create the best scoring and predictive models. This pattern facilitates the creation of multiple virtual data marts without the need to hold data in many different data stores

Reasons For Using It

Reduces the proliferation of data marts and also prevents inadvertent ‘personal’ ETL development by power users who have a tendency to want to extract their own data to create their own data marts. It is often the case that each power user wants a detailed subset of data from a data warehouse that overlaps with the data subsets required by other power users. This pattern avoids inadvertent inconsistent ETL processing on extracts of the same data by each and every power user. It also avoids the duplication of the same data in every data mart, improved power user business analyst productivity, reduces the time to create data marts and reduces the total cost of ownership.  

=”FONT-FAMILY: ‘Arial’, ’sans-serif’; COLOR: black”> 

Had a tipoff from a regular support.sas.com user that there is now a few SPM 5.1 guides and usage notes now on the site.

So is SPM 5.1 released?

I haven’t seen any announcements, but then again I might have missed it.

Here are the docs I have found so far:

• Installation Instructions–Release 5.1 of the SAS Performance Management Solutions
  Installation Instructions for Release 5.1 of the SAS® Performance Management Solutions Copyright Notice The correct bibliographic citation for this manual is as follows: SAS Institute Inc., Installation Instructions …

  http://support.sas.com/documentation/installcenter/en/ikfmofrii/63034/PDF/default/install.pdf,

  433KB

• 37794 – Post-migration steps required for SAS® Strategy Management
  The SAS Demo User account must be modified in the SASSDM in order for that user to be able to access SAS Strategy Management content.

  http://support.sas.com/kb/37/794.html,

  B

eHitDescription–>

Let me know if you know.

Here’s a cheeky move by Microstrategy: they’ve made the free version of their Reporting Suite work for Analysis Services. More details and a download link here:
http://www.microstrategy.com/freereportingsoftware/learnmore/microsoft-analysis-services-ssas.asp

I’ve not tried it so I don’t know whether it’s any good or not, but it’s free and you can have up to 100 users, so it will be worth checking out. Of course this is Microstrategy trying to hurt Microsoft and its partners, but, well, it’s free…

I’ll be on vacation for the next few days, so I leave you with a list of recent posts from other bloggers you might enjoy.

SAS user Jared Prins makes a very funny SAS Christmas wish list, including:

Oddball t-shirts like those at T-shirt Bordello, but with SAS related goofiness on them. For example:

• For the text miner in you
• count+1;
• SAS log

p://blogs.sas.com/sascom/exit.php?url=aHR0cDovL2Jsb2dzLnNhcy5jb20vamltZGF2aXMvaW5kZXgucGhwPy9hcmNoaXZlcy8yNy1FbWFpbC1hbm5veWFuY2VzLWFuZC1nZXR0aW5nLXRvLXRoZS1TdHJhdGVnaWMtSW5ib3guaHRtbA==&entry_id=616″ title=”http://blogs.sas.com/jimdavis/index.php?/archives/27-Email-annoyances-and-getting-to-the-Strategic-Inbox.html” onmouseover=”window.status=’http://blogs.sas.com/jimdavis/index.php?/archives/27-Email-annoyances-and-getting-to-the-Strategic-Inbox.html’;return true;” onmouseout=”window.status=”;return true;” >SAS CMO Jim Davis on dealing with email overload:

I hate nothing more than waiting until the fifth paragraph to find out what you want. Say it up front. If you need to sell me, that’s why God invented PowerPoint.

Why is data quality important? From the DataFlux Community of Experts blog:

Fundamentally, what I am asking is – why are you doing a data quality project? I believe it’s absolutely essential every member of the project team understands why you are working on improving the quality of your data. Regardless of their roles and responsibilities, everyone should be able answer this question – in two minutes or less.

SAS Chief Financial Architect Clark Abrahams on community development and better loan underwriting:

You may be wondering what information-led community development and loan underwriting have to do with one another. First, they both benefit from having greater information. In the case of community development, more information can be used to develop greater intelligence that can change the perspective of a community from being one of need to being one of opportunity. Loan underwriting can benefit from the sourcing and use of alternative data that can qualify consumers for a loan on their record of making timely cash payments for rent, utilities, phone, and so on.

The SAS Publishing blog introduces a new video series:

Armed only with a Kodak Zi8 and a wired clip-on mic, SAS Press Acquisitions Editor Stacey Hamilton filmed seven author interviews in Las Vegas during the M2009 and PBLS conferences. Once she returned to the office, I edited the videos using Apple’s iMovie software and then handed them off to our video department, who uploaded them to the SAS YouTube channel.

The Telecom Regulatory Authority of India (TRAI) put up a press release, Date: November 21, 2009 Press Release:Telecom subscribers growth for the month of October 2009. ,  that has information on the telecom subscription data in India for the month of October 2009. Apart from the quite amazing piece of news that India added 16.67 million (that is 16,670,000) new wireless subscribers, and that the

If you had attended our training days event or one of our Open World sessions, you will have noticed that we had covered the various aspects of integration between Essbase and relational sources using BI EE. The presentations are available here. One of the scenarios that we had not covered was the ability to display relational attributes along with Essbase data. It is very similar to the Horizontal Fragmentation technique we had covered in the training. But there are significant differences which i thought made sense to cover in a separate blog entry.

For example, lets assume that we have the entire SH schema loaded into Essbase. In many cases, not all the attributes of a dimension are loaded into Essbase for a couple of reasons.

1. Load & Retrieval Performance
2. Cube size

If you look at the Essbase outline below, for the product dimension we have just loaded the primary hierarchy.

As you see this outline does not have all the Product attributes like Product Price, Product Pack Size etc. In many cases, such attributes might be maintained in relational sources but may never be loaded inside an Essbase outline. But from a reporting standpoint, we might still need to display these attributes in some cases as shown below

If you notice, Quantity is an Essbase measure and PROD_LIST_PRICE is a relational attribute of a product. The question is how do we model this in the repository in such a way that there is least impact on the queries generated. There are couple of approaches that we can take to model this. I will cover one such approach today.

This modeling technique leverages the concept of conforming dimensions. We start first by including the relational product source table in the Essbase Product dimension as shown below

Then we create another custom logical table called Product Attributes. In this logical table we we can include all those attributes that do not exist in Essbase. This logical table will be modeled as a separate dimension.

Ensure that the source of this new logical table has the same relational table source as the source that we added in the Product dimension. Now create a dummy Fact logical table called Fact-Products which will enforce the conforming dimension join.

The source for this fact logical table will be the same Products relational source. Now, create complex joins as shown below.

For each fact measure in the Essbase source map it to the total level of the Product – Attributes dimension as shown below. This will ensure that BI Server will combine both the sources together in its own memory.

Now while creating a report we need to ensure that we bring in measures from Essbase as well as our dummy fact. The dummy fact enforces BI Server level join at the product level.

If you look at the SQL queries, you will notice that BI Server will fire 2 separate queries. But the joins across the sources will be through the conforming product dimension.

   T2273.PROD_CATEGORY as c2,
     T2273.PROD_SUBCATEGORY as c3,
     T2273.PROD_NAME as c4,
     T2273.PROD_LIST_PRICE as c5,
     sum(1) as c6
from
     PRODUCTS T2273
where  ( T2273.PROD_SUBCATEGORY = 'Accessories' )
group by T2273.PROD_CATEGORY, T2273.PROD_LIST_PRICE, T2273.PROD_NAME, T2273.PROD_SUBCATEGORY, T2273.PROD_TOTAL
order by c1, c2, c3, c4

With
  set [Product4]  as '{[Product].[Accessories]}'
  set [Product5]  as 'Generate({[Product4]}, Descendants([Product].currentmember, [Product].Generations(5),SELF), ALL)'
select
  { [Measures].[Quantity]
  } on columns,
  NON EMPTY {{[Product5]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [SH.SH]

This shows the capability of BI Server in modeling multiple data source scenarios. If you require other dimensions to be part of the report then the dummy fact will have to assigned to the Total level of all the other dimensions (effectively Total levels need to be created in each dimension). For example, the Promotion dimension is shown below