Business Intelligence with MicroStrategy Cookbook
上QQ阅读APP看书，第一时间看更新
上一章目录下一章

Using SQL View to inspect SELECT statements

A developer's best friend: SQL View is a way to look at the statements that MicroStrategy sends to the RDBMS. This window is probably the most useful debugging tool that we have at our disposal.

During the development phase or for customer support, from this view it is easier to troubleshoot a report that takes forever, returns no rows, or just fails with no evident reason. But it is even more useful to understand how the SQL generator interprets the objects that we build day after day. I call it my personal peep show.

Getting ready

You need to have completed the previous recipes and exercises to continue.

How to do it...

  1. If not already there, go to My Personal Objects | My Reports folder.
  2. Double-click on 03 FactInternetSales row Count to run the report.
  3. When the result appears select the View | SQL View menu.
  4. Look at the window, there is a lot of useful information here (number of rows/columns returned, query execution time, and so on). And, near the end there is a section called SQL Statements.
  5. This is the list of queries that the MicroStrategy Engine runs on the database server. See the Pass0 statement (select count(1) WJXBFS1 from FactInternetSales a11).
  6. You can copy this statement and run it into sqlcmd and it will return the same dataset as the report.
  7. Switch back to the grid with the menu View | Grid View and click on Save and Close.

How it works...

Based on the information stored into the metadata the query is issued to the ODBC driver and executed in the data warehouse. The returning rows are then processed and displayed on a grid. You can see that the sentence is exactly the same as we did at the beginning of the chapter to count the rows in the fact table.

There's more...

You cannot change the query in the SQL view. Unfortunately, it is read-only. So, if the SQL is not what you expected to be, you need to modify the schema objects used to build this report.

The SQL view is not always this simple. Going on in the project you will find more and more content in here, until it becomes almost unreadable to human. Very complex reports have several passes of SQL and use temporary tables to store intermediate results before displaying it to the user. It will become harder to follow, yet this is the best place to look for errors when things go wrong.

Note

You can watch a screencast of this operation at:

Exercise 14

Create a new blank report. Drag to the Report View area the two metrics we created in previous recipes. Click on View | Grid View and check the results. Now go to SQL View: what do you expect to see in the SQL statement Pass0?

Save the report as 05 Two Metrics from FactInternetSales.

上一章目录下一章