- Business Intelligence with MicroStrategy Cookbook
- Davide Moraschi
- 488字
- 2021-07-23 15:32:05
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...
- 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.
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.
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.
- 審計全流程技術操作實務指南
- 金融科技（FinTech）發展的國際經驗和中國政策取向（中國金融四十人論壇書系）
- Microsoft Dynamics NAV Financial Management
- Learning Informatica PowerCenter 9.x
- 中國政府統計問題研究
- 審計全流程實操從入門到精通
- AO2011實用手冊
- Microsoft Dynamics NAV
- 內審兵法
- Implementing VMware Horizon 7.7
- 內部控制審計功能與質量
- 計量經濟學理論與應用：基于Eviews的應用分析
- Oracle E-Business Suite Financials R12：A Functionality Guide
- 統計學視角下的金融高頻數據挖掘理論與方法研究
- INSTANT IBM Lotus Notes 8.5.3 How-to