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.

You may also like...

15 Responses

  1. LXLI Design says:

    Hi Bryan, I saw one of your code snippets on MicroStrategy forum about how to use command manager or C#.net to run a report and generate an excel file. In the code snippet, you used a “ReportViewer” as one of the parameters in execute report. What report viewer were you referring to? Does your example require the MicroStrategy SDK? Thanks in advance!

  2. Bryan says:

    ReportViewer is an internal Style Name that just returns the data in an easy to read and clean format. It applies to Web Services though, and not Command Manager. You may be interested in using TaskProc instead. No SDK license required other than to obtain further documentation.

  3. LXLI Design says:

    Thanks a lot for your extremely fast reply! I am very new to MicroStrategy and my client wants to display their MicroStrategy report on their asp.net application like how they did with SQL reporting. I am not sure where to start on this, can this be done at all? What do you recommend as a solution?

  4. Bryan says:

    TaskProc is the easiest to get started with, so if you’re just looking to feed data to .NET, go with that. Alternatively you could check out Web Services to get a little bit more control.

  5. LXLI Design says:

    Hi Bryan, this looks very useful for us since we cannot buy the SDK at this point. Have you used this to pass the data to MS SQL Server reporting services reports? or is there a Microstrategy report widget that can be used?

  6. Scorpio says:

    Very nice article Bryan! People always tell me not to or never use Logical View at all, but after reading this article I can really explain to them where and why we should use logical views.
    Please provide some information on report performance improvement as well. This is the trickiest job for a MSTR developer.

  7. zhaba1 says:

    This comment has been removed by the author.

  8. Hi Bryan,

    Please let me know from where to see LOGICAL VIEW in MSTR DESKTOP.


    • Bryan says:

      They’re in the same place as other tables. To create one, you just right click on a pre-existing table, and choose “Create Logical View”.

  9. Hi Bryan,

    Any comment on above question?


  10. Hi Bryan,

    I like to know if MicroStrategy SDK can be used to modify metadata.
    For example, rename a table.

    Thank you very much and greet

    • Bryan says:

      It can, but I think the Java SDK can’t, and only the COM SDK can. You may be better off using Command Manager, because it has a lot of modification options and will be easier to use.

  11. lucho says:

    Brian, i need some help. I have a Logical table with a query and subquery, and i dont know why it doesnt work. When I run the report it gives an error “Table or view not found”, i tried lot of ways but never work. All tables are in WCatalog. Sorry with my english.

  12. Nag says:

    Hi Brian,

    How can we use logical table in freeform sql. with direct reference or should we need to mention any schema for that.
    In case we have to mention any schema, will that be same as the schema of tables used in logical table or something different.
    Please let me know.


  13. Rohan says:

    Hi Bryan,
    i am trying to create attributes with a logical view as a lookup table using command manager scripts. But it gives me this error:The lookup table ‘test’ was not found either within the expression’s related tables or within the EXPSOURCETABLES explicitly defined.
    Please let me know.

Leave a Reply

Your email address will not be published. Required fields are marked *