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.
- If not already there, go to My Personal Objects | My Reports folder.
- Double-click on 03 FactInternetSales row Count to run the report.
- When the result appears select the View | SQL View menu.
- 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. - This is the list of queries that the MicroStrategy Engine runs on the database server. See the
Pass0statement (select count(1) WJXBFS1 from FactInternetSales a11). - You can copy this statement and run it into
sqlcmdand it will return the same dataset as the report. - Switch back to the grid with the menu View | Grid View and click on Save and Close.
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.
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.