Today, I am adding the second post in my MicroStrategy Report Optimization series. This will be a multi-part series (I will leave it open-ended so I can continue to add to it).
Today, we will look at the components that comprise performance.
Source: MicroStrategy University, Deploying MicroStrategy High Performance BI, V9.3.1, MicroStrategy, Inc. September, 2013.
Components of Performance
There are five key layers or components that a typical BI query must go through. They are:
- Caching Options
- Data Transfer
- System Architecture and Configuration
- Client rendering or Data Presentation
- Data Warehouse Access
The components above are not listed in any specific order of access during the execution of a query. The image below illustrates the five components.
Caching and Intelligent Cubes
MicroStrategy’s memory technology is engineered to meet the increased demand for higher BI performance, which is driven by the rapid expansion of both data volumes and the number of BI users in organizations across industries. MicroStrategy accelerates performance by pre-calculating computations and placing the results into its memory acceleration engine to dramatically improve real-time query performance.
Data transfer over one or more networks are a very important component of a BI implementation. A slow or poorly tuned network performance in any of those transfers will translate into poor performance from a report or a dashboard execution perspective.
System Architecture and Configuration
Successful BI applications accelerate user adoption and enhance productivity, resulting in demand for more users, data, and reports. MicroStrategy provides the ability to adapt quickly to constant changes and evolve along with business requirements. MicroStrategy Intelligence Server hs been proven in real-world scenarios to deliver the highest performance at scale with the fewest servers and minimum IT overhead.
Dashboards provide graphical, executive views into KPIs, enabling quick business insights. MicroStrategy enables higher performing dashboards, averaging 30-45% faster execution and interactivity. Using new compression methods, MicroStrategy dashboards have a smaller footprint than ever before – up to to 55% smaller – resulting in faster delivery using less network bandwidth. Dashboards deliver ever more analysis and data for end-users.
Data Warehouse Access
High performance BI starts with optimizing SQL queries to retrieve results from the database as quickly as possible. BI performance is dependent largely on the time that the queries take to execute in the database. An average reporting request usually takes 40 seconds to complete, out of which 34 seconds, or 85% of the query time, is spent executing in the database.
Therefore, it is critical to optimize report queries to reduce database execution time.
This is something that I actually had the opportunity to use this week. It really came in handy and helped satisfy my client’s requirements.
Just a reminder as we discuss this tip, I am using MicroStrategy v9.4.1 and MicroStrategy Developer. Developer is the new name for MicroStrategy Desktop as of v9.4.1
This example demonstrates how to create a 3-level deep nested prompt that will prompt the user to select a year, then a quarter within that year, then a month within that quarter.
Prompt-in-prompt is a feature in which the answer to one prompt is used to define another prompt. This feature is only implemented for element list prompts. The following steps describes how to achieve this.
Create the Year Prompt
- Create the highest level filter first. This is a filter which contains a prompt on an attribute element list. Create a filter on the attribute “Year” and click “Prompt“.
- Click “prompt on attribute element list” and then click “Next” through the rest of the screens to accept the default values.
- Do not set any additional conditions. Save the filter as “Year Filter“.
From this level on, each filter will contain both a prompt on an element list and will have its element list filtered by the filter created one level above it. There is no limit to the number of such filters that can be layered.
Create the Quarter Prompt
- Create a filter on attribute “Quarter” and click “Prompt“.
- Select “Use a filter to reduce the number of elements” and select the “Year Filter” created in the previous step.
- Save this filter as “Quarter Filter” .
Create the Month Prompt
- Follow the same basic procedure as above to create a Month filter. Create a filter on attribute “Month“.
- Click “Prompt“.
- Select “Use a filter to reduce the number of elements” and select the “Quarter Filter” created in the previous step.
- Save this filter as “Month Filter” .
Try it Out
Place only the lowest level filter in the filter section of the report. In this case, the “Month Filter” will be placed in the filter section.
When the report is executed, it will prompt for Year, then for Quarters in the selected Year, then for Months in the selected Quarter, as shown in the following sequence of screen shots.
By default, when users apply subtotals in a report, the name of the subtotal is displayed in the subtotal line items that appear in the report. Users can use custom subtotals to give more control over the characteristics of a subtotal. Custom subtotals allow users to define custom subtotal line items that appear on the reports.
Users can make the subtotal name dynamic by typing special characters in the subtotal name field as listed in the following table.
To define a specific subtotal displays for a report like the one shown above, follow the steps below:
- Select Subtotals from the Data menu. The Subtotals dialog box opens. Clear the Totals check box to remove the standard subtotals.
- Click Advanced.
- Click New to create a custom subtotal.
- Type the following for the name: “Total for the #P #0”. Remember that P displays the parent attribute and 0 (the number zero, not the letter o) displays all the forms of the parent attribute. In this case, only one form exists for each, as shown below.
All the metrics on the report are listed. Users can select the subtotal function to use for each. Total is correct for all of the metrics.
- Check the Total for the #P #0 subtotal (shown below).
- Click Advanced.
- Select Across level and then select the Region and the Employee as the levels.
- Click OK to save the new subtotal.
- Click OK to return to the Subtotals dialog box.
- Click OK.
The report should now look like this.