It's been a while since I last posted about anything remotely related to OBIEE or Microstrategy. I've had almost a year of picking up the Microstrategy side of the technology and gone through some headaches to get more accustomed to its quirks, strengths, weaknesses and workarounds.

Most of this post will deal with the Microstrategy 8 platform, not the 9 release that has been out about a year now. There are some features of 9 that will apparently level the playing field but I'll go through the obvious things (to those who know one or the other or both platforms) first.

1) Multi-source: Apparently Microstrategy 8 only is able to leverage one database source to run OLAP queries from. In OBIEE (even before the Oracle acquisition when it was still Siebel Analytics), one of the main benefits it incorporated way back in 2003 was the ability to derive analytics through multi-sourcing. This meant that if you had a Data Warehouse built and you wanted to join some tables to a second (or third) source (such as an Excel spreadsheet with corresponding correct key joins), OBIEE would easily do this for you. All the work, however, occurs at the server level and this spells bottleneck if it needs to go against 2 sources of very large datasets. After all, OBIEE design necessitated the fact that you need to push as much of the analytics processing down to the database level. With Microstrategy 8, you would essentially need to go through the data migration process of getting everything into a consolidated Data Warehouse for this to happen. Microstrategy 9, introduced in 2009, has finally rectified the issue of multi-source and allows you to do this.

2) Temp Table shenanigans: Microstrategy 8 LOVES to create temporary database tables to distribute some of the processing of the data into smaller query chunks. The thought process is that if a bunch of smaller queries were pushed to the database level, they would be processed faster and datasets would be returned back to the iServer analytical engine to render the results back to the user. The problem is that the final query (or queries) is dependent on the temp tables being created in the first place. There is a sequential process that seems less effective in terms of processing fairly large queries. Of course, if you design your Data Warehouses with too many records in your fact table, you deserve to suffer long query times! (Remember to design for efficiency also!) OBIEE is a little more straightforward in terms of generating the appropriate queries (it will split up into multiple parallel queries for returning different levels of aggregated metrics if your report is displaying granular, sub-totals and grand totals). And you don't have to follow the temp table madness that can occur with Microstrategy queries, where I've seen up to 8 temp tables built to serve the purpose of a single report. Clean up of these temp tables can get ugly if there aren't taken care of. (I don't believe Microstrategy actually drops them once they have served a dashboard/report purpose.)

3) Visualizations: Since one of the strengths of Microstrategy is in the visualization of dashboards/reports, this means that depending on the deployment type of dashboard (you can have interactive dynamic HTML or Flash versions as well), the rendering of all permutations of data (meaning for all the prompted values on a dashboard, it renders all of the combinations and sends the XML data to the web client) can lead to some heavy wait times on the iServer engine side to complete before the results are displayed to the end-user. This is part of the pixel-perfect features that Microstrategy touts as a prime selling point. With this, however, I believe the flexibility adds much to the complexity of some dashboard/report designs that users and developers can go absolutely nuts developing complex dashboards with a huge amount of permutations of data that can be shown on the it. Since OBIEE is a lot more rigid in the way it renders the dashboards/reports (you only have the standard UI that OBIEE provides or you can hit the highway), and by using dashboard prompts, you are actually requerying the Data Warehouse to generate another visualization of the data that has been selected, it's actually more efficient in "breaking things up" from a rendering perspective.

I've got a few more points to make but I think I'll save that for another time before boring you folks with the details. Again, please post your comments and criticisms of my observations. I welcome them all.

Having had a little more exposure to the Microstrategy application stack, specifically Version 8, I find they are fairly similar platforms. The differences end up being how to create Microstrategy (MSTR) Projects, which are essentially Logical Models within the Business layer in OBIEE. Metrics & Facts take on slightly different meanings within the MSTR world as they are fairly flexible in nature. The organization of things in multiple folders within an MSTR Project is a little daunting and confusing at first but makes some sense if you just focus on everything relative to the Project itself. If you extend the concept of MSTR Projects to a single OBIEE Subject Area, then you can make the association.

The MSTR analytical engine is fairly similar in that it will try to determine the most optimal query to construct based on the definition of the MSTR Project objects. The architecture of the physical data model requires a little bit of a different approach.

One thing I started to investigate was the fact that Microstrategy likes to snowflake tables off dimensions. This is a little different than the nearly complete normalization of dimension tables within the Oracle Business Analytics Warehouse data model. I'm going under limited exposure on the reasons behind this but I believe there are cases where snowflaking, let's say, a type table off a dimension may lead to some design approaches to the fact that that make it easier later on to create aggregates that join to the snowflake table at a higher level of granularity than referencing the dimension table. The MSTR analytical engine chooses the approach table to join to the proper granular fact table through its own implementation of aggregate awareness.

OBIEE has had aggregate awareness for quite some time now (at least 5 years) within its main server engine. It also was a little more intuitive to define at which level of granularity the Logical Table Sources were defined for and referencing the appropriate hierarchy column within a dimension to determine which LTS to query against. If an appropriate column within the normalized dimension table exists to join to the aggregated fact table, this design decision allows OBIEE to implement aggregate awareness in a much more efficient manner.

I'll dig more into how the Microstrategy analytical engine handles this with the configuration in the Project.

Another difference is that the metadata resides within its own schema whereas the OBIEE metadata resides in a single file. There are pros and cons in both methods. With an older BI platform product I worked on over 10 years ago called Information Advantage DecisionSuite (RIP IA!), it was very similar to what Microstrategy's approach is in terms of centralizing the metadata storage in a database schema. Migrations with metadata stored in a file vs a database means that it is a little more cumbersome to conduct within Microstrategy. In OBIEE, it requires copying a single file into the target server. Within Microstrategy, the Object Manager needs to push the changes from one environment to another, meaning that it needs to do database reads and writes from one metadata schema to another, in another database as well (if you architect it according to best practices and separate the target server metadata in another schema or database instance altogether). This, however, ensures that proper measures and checks & balances are done via the platform to preserve the integrity of the metadata components.

I'll post some more thoughts as I come across them. Feel free to poke holes in my observations though and comment on the comparisons. More to come!

I recently took a new position at a company as a Full Time Employee (FTE as we're called now ), ditching the consultant life of going from one client to another, and trying to take what I've learned and experienced over the years with Business Intelligence and OBIEE and applying them to this company.

The platform they are using is Microstrategy, which as some of you (or most of you astute readers) will know to be one of the oldest ROLAP Business Intelligence tools in the market. It's on version 9 now so I'll have quite a bit of exposure to the Intelligence Server architecture, the platform itself, the Reporting engine and the Administration portion of it to compare with OBIEE's similar parallel offerings.

I've already noticed a few differences between the 2 platforms but am surprised at the amount of similarites. In the next several posts, I'll probably do some comparisons of the 2 platforms and invite comments about the pros and cons of each one of these comparisons.

All things being equal, OBIEE and Microstrategy aren't that far apart in terms of what they actually offer and provide businesses: the ability to visualize their data and gain value and insight into their organization in order to manage their business more effectively and efficiently. It's just in HOW they go about taking care of business that will be interesting to compare with.

Oh, and BTW, why aren't there more resources on Microstrategy (such as blogs and forums) than there are available for OBIEE?

So where does the OBIEE Server Cache give you the best bang for the buck? Do you have a day to listen to a number of differing opinions on this? Nothing is quite as controversial as determining when or when not to apply report caching at the OBIEE Server level.

Hopefully you have set up most, if not all of your dimensions on the RPD to be cacheable. This is probably the very basic thing you would want to do from the initial design of your RPD. These objects are the least likely to change frequently (and I'm generalizing because it really depends on your Data Warehouse implementation). Most likely, the facts may also be on the same refresh frequency as your dimensions so it'd also make sense to enable caching on them as well. (Note: I would not recommend doing this if you have a very rapidly (like every hour refresh) changing incremental update process within your DW.)

If you follow John Minkjan's scripts (or any other custom script) to manage and purge the cache appropriately, you'd be effectively and efficiently helping performance on a number of areas like:

• Dashboard Prompts on dimensional attributes
• Answers queries on dimensional attribute list of values for filtering on
• Commonly referenced base measures from fact tables
• Basic common dashboard reports

Once you get beyond these very basic areas, caching may or may NOT help at all. My colleague has a good write up on why certain things may not be cached in OBIEE here.

Most implementations of OBIEE center around a tight security model. With caching enabled, most dashboard reports are run through the OBIEE security model that has been designed in place. As of now, the OBIEE Server caches the results for a specific user ID but does not share the results of this cache to other user IDs. To do so would be a nice little breach in security, wouldn't it? In general, though, the best practice to get around this is to execute the Dashboard report that is commonly viewed by all users under a General user (most likely an RPD user) and cache the results for all other users. This is all well and good, except when you have data-level security on the dimension/fact tables implemented within the RPD that cause each of these common reports to generate a query unique to the user. Then, the caching works for each individual user but not for the rest of the OBIEE users.

The frustrating thing is that no amount of planning can allow developers to prepare for what queries get cached, for which user and what set of dashboard prompts that filter each report. For instance, allowing individual users to "Save Current Selections" on a Dashboard page would simply prevent any predictability on what to cache. Planning which users are running what reports and seeding the cache for these users is a maintenance nightmare, especially if your user base for OBIEE is quite large.

What do we do about this? I'm going to let you follow the path to Mark Rittman's excellent suggestions here.

Any OBIEE and Data Warehousing professional worth their weight in salt should always take the approach to push as much of the work down to the database level as possible, designing the most optimal data model possible and creating the necessary aggregations and performance tuning at the database FIRST before tackling performance optimizations (or tweaks!) at the OBIEE Server/Presentation level. To leverage all your knowledge into using the OBIEE Server cache is not really optimization at all but merely using it as a crutch to mask the base issues at large.

Within the OBIEE server, there is a feature on the server for caching report results so that other users can retrieve the results of the reports fairly quickly, without putting unnecessary load on the database server for the same sets of queries. This has, IMHO, caused some consultants and implementors to use it more than it was intended to be, a band-aid to what is the core of OBIEE, the relational database underlying it all.

Remember that OBIEE is NOT a MOLAP product with the ability to pre-generate multidimensional cubes into a highly optimized format for very quick retrieval of data. It is also not a static reporting tool, which is how I view the cache sometimes since the cached results are static until the cache is purged by some predetermined scenario(s). It sits on top of a relational database, where your Data Warehouse resides, and is meant for dynamic retrieval of this dataset.

Arguments about clients & customers having a fixed data model that cannot be altered which forces us to resort to using the OBIEE server cache to be seeded with report results are just excuses I believe.

I'm going to step on my soapbox here for a second. The first thing to do is level-set the expectations and try to guage whether their investment in OBIEE (and it is not a cheap investment!) is with the right intentions from a high-level perspective? Are we JUST providing reporting? OBIEE is not the tool for this. Analysis of the data, diving into it to get at root causes of exceptions and being able to take actions, reactive or preventative, as a result of what the data is telling you is the key to OBIEE's purpose in life. It's not a pig on a stick or a lipstick on a pig or whatever the new political allegory is nowadays. It serves a particular purpose within the enterprise and needs to be communicated that way from the get-go.

With that being said, caching does serve the purpose of helping to relieve frequent trips to the database layer by allowing frequently used reports to be served up to a broad base of users fairly quickly. It is not for speeding up the results of the OBIEE application so that it pretends to be quick and nimble on its feet. Let's not delude the users this way since they ARE smarter than some of us make be led to believe!

Performance tuning on the database level has ALWAYS been the key to ensuring that OBIEE performs at its optimum levels. This means having a solid and optimal data model in place for your Data Warehouse, given the requirements of what's being reported on, data volume growth patterns, and the metrics and attributes required for meeting reporting needs. It means having a solid DBA team to provide technological strategies such as DB configuration, both hardware and software, indexing strategies and partitioning strategies. It means having a solid OBIEE application administration team to constantly monitor the evolving nature of a Business Intelligence application, analyze the current usage and plan for future usage growth, as well as reporting scenarios the business is likely to need moving forward.

After all this is done, go ahead and start doing some caching strategies. That is why it is on the OBIEE application level and not on the backend layer.

There has been mention of security requirements coming into play that affect the requirements for OBIEE server caching. These also need to be handled carefully and properly in order to adhere to the security standards of your enterprise.

These are my personal thoughts on the matter but there may be other scenarios that I have not encountered which has valid merit in the extensive use of OBIEE server caching. I just have not encountered those scenarios yet.

Next time: Does the caching mechanism work? Aren't there limitations on how much can be cached? Which users are encapsulated within the scope of caching and which aren't?

There was a question raised by a potential OBIEE customer that came up in some conversations internally. They wanted to use this automation, batch processing and scripting application called "Control-M" to replicate and replace the functionality of the Data Warehouse Application Console (DAC).

I don't really understand this since ANY application can be essentially hacked and cobbled together via code snippets, batch/script files, run via Control-M and let it run the necessary dependancies on success vs failures within this "spaghetti" solution. When you've paid so much license fees for Oracle Business Intelligence Analytics Applications and the DAC that manages and loads the Data Warehouse the Oracle BI Applications support, the logical solution is to let the software do the job it was BUILT for to begin with. Re-inventing the wheel here isn't necessarily a small endeavor. DAC already circumvents the Informatica Workflow Manager in most ways and allows customers to create their own Execution Plans (these are chained workflows with multiple dependancies based on success/failure states), customize the Data Warehouse creation, build and maintenance processes (schema objects and indices!) and deploy this on multiple environments.

Deploying the DAC Client can easily be done on any Windows box (workstation-class) where some remote desktop capability has been enabled. Most organizations usually let Terminal Services do this job but VNC can also be used.

There are other aspects of DAC that I'm not at liberty to give a thesis on (for the sake of time) but the main point is that there is absolutely no value in replicating the functionality of DAC within a separate batch processing/scripting application. It requires way too much time and resources to even do so. Please use all the tools available in a vendor's application suite to their fullest potential. That's what they were specifically designed and developed to do in the first place.

To take a look at my professional experience, please click here to view my resume in Microsoft Word 2003 format.

PROFESSIONAL PROFILE:
David Kwan has been working as a Data Warehouse and Business Intelligence consultant since 1997. He has a very strong business analysis and technical background with excellent work ethics and devotion to complete customer satisfaction. Throughout the years, he has taken the roles of Project Manager, Team Leader, Business Analyst, Developer and Mentor/Trainer for Fortune 500 customers. He has broad and extensive experience implementing OBIEE/Siebel Analytics and Data Warehouses in different industry verticals & horizontals.

Qualifications include:
o Gathering, analysis & documentation of core business requirements
o Star schema design based on Ralph Kimball Methodology & DW best practices
o Design overall infrastructure & data storage architecture strategies
o Reverse engineering of source systems to develop a data migration (ETL/ELT) design
o Implementation of extraction, transformation and loading (ETL) data from source to target
o Best practices on development of the Reporting layer to the business users’ requirements
o Customizing out of the box ( OOTB ) packaged applications for unique source data requirements
o Testing and rollout of the system, including production support during the initial go-live period.

OBIEE is an acronym for Oracle Business Intelligence Enterprise Edition. It is a product that has evolved into something more than just a Business Intelligence application platform toolset. It is a combination of multiple components that helps a company leverage their transaction application data, dive into it in a more manageable fashion and intelligently make key decisions to optimize their business and enterprise as a result of KPIs produced by OBIEE reports.

I'd invite you to check out Oracle's website here to get the "official" literature on it.

This is my world which I have lived and breathed in my short career for the last 5 years. It is a journey that continues to teach me insight into industries I have not yet covered, exposing the intelligence and analysis required for a company to continue to operate profitably, grow and innovate in a highly competitive world market.

Hopefully, I may one day provide my experience and foundation as an OBIEE consultant to help you leverage the information that is currently residing in your enterprise systems and applications and turn that into actionable intelligence to make effective and sound decisions in running your business.