Logical Views

While we usually focus on MicroStrategy and it’s role vs the Data Warehouse and it’s role, the truth is the lines are pretty blurred.  Nothing frustrates me more than when people tell me the “right way” to do something.  The truth is, only you could possibly know the right way when it comes to a solution, because you have all of the information.  You know how flexible your data model is, how quickly your ETL Team can turn around an enhancement or bug fix or how many times a piece of data will be used by users.

Whenever someone asks me how to do something, I like to give options and perhaps my preference for how to do it.  The truth is there are lots of possible solutions, and I hope to present various options for you in this Blog and you can choose the best tool for the job.  One of the most critical (and often controversial) tools at your disposal is the Logical View.  While some see it as a circumvention of traditional Data Modeling Best Practices, I see it as just another possible way to solve a problem.  Today I’ll discuss how they work, their role in MicroStrategy’s schema and weigh different scenarios of when you may want to consider using them.

Logical Views are a very powerful feature in MicroStrategy schema design and offer a great deal of flexibility.  When you import a physical table from your Database into MicroStraetgy either through the Warehouse Catalog or via the Architect Tool, you’re creating an object in MicroStrategy known as a Logical Table.  As it sounds, this is not a physical representation of the table, but a logical one.  The difference is that it is a copy of the definition of the table, meaning if the underlying table changes, you have to be sure to update your structure for your copy of that table.  A little known fact is that you can rename those Logical Tables if you wish and place them anywhere you want in your project, because it’s just another object (though it probably makes things a lot easier to work with if you keep them as the same name as your physical tables, I just wanted to point out that it is an option).

A Logical View will act exactly like a Logical Table in the way it handles Attributes and Facts and even contains the same Logical Table Size parameter to let you tweak your queries.  The difference is that instead of being linked to a physical table in the warehouse, it’s contents are free form SQL that you provide.  You can map your existing Attributes and Facts to your Logical View, so  it will fit nicely in your existing schema.

The Logical View will resolve itself in SQL as a subselect, so you may need to consult your DBA for any performance concerns.  Most recent database platforms are optimized to pass external Where Clauses into the sub-SQL, so don’t be worried to see Report SQL with a filterless select in the middle of it.

Ultimately, Logical Views are a convenience to a MicroStrategy Developer where you’re trying to strike a balance between assisting MicroStrategy’s SQL Engine in performing a complex join, such as a Many-to-Many-to-Many, theta join or breaking out complex hierarchies and asking ETL to provide new table structures.  For me, if the only issue is getting MicroStrategy to generate the correct SQL (such as from one of the previous scenarios), I’ll turn to the Logical View first since the “perfect” query would effectively do the same thing I’ll be accomplishing with my Logical View.  If the performance suffers, then I’ll consult my DBA/ETL Team to see what options we have including making a Database View or populating a new physical table.

Some consider the use of Logical Views taboo and to be avoided at all costs, but realistically, they can provide a schema designer with flexibility and the capability of delivering a quick solution in a pinch.  They are even handy to throw together Proof of Concept projects or features to make sure a particular schema or change will work prior to having the ETL Team perform the real changes.