Category Archives: Tips & Tricks

NEW! MicroStrategy Tips & Tricks Quick Reference Page

Readers:

To make it easier to find all of the MicroStrategy Tips & Tricks I posted, I have added a new page that contains a quick reference to all of them (see screenshot below).

I will update this page after every five new tips & tricks have been added.

Best Regards,

Michael

Tips and Tricks Quick Reference

Tips & Tricks #13: Star Schema in MicroStrategy

Star Schema 1

A Star Schema is a design that contains only one lookup table for each hierarchy in the data model instead of having separate lookup tables for each attribute. With only a single lookup table for each hierarchy, the IDs and descriptions of all attributes in the hierarchy are stored in the same table. This type of structure involves a great degree of redundancy. As such, star schema are always completely denormalized. Let’s review the star schema above based on the MicroStrategy Tutorial data model.

The schema contains only two lookup tables, one for each hierarchy. LU_LOCATION stores the data for all of the attributes in the Location hierarchy, while LU_CUSTOMER stores the data for all of the attributes in the Customer hierarchy. As a result, star schemas contain very few lookup tables-one for each hierarchy present in the data model. Each lookup table contains the IDs and descriptions (if they exist) for all of the attribute levels in the hierarchy.

Even though you have fewer tables in a star schema than a snowflake, the tables can be much larger because each one stores all of the information for an entire hierarchy. When you need to query information from the fact table and join it to information in the lookup tables, only a single join is necessary in the SQL to achieve the desired result.

Joins in a Star Schema

As an example, if you run the same report to display customer state sales, only one join between the lookup and fact table is required to obtain the result set as illustrated below.

Star Schema 2

To join the Customer State description (Cust_State_Desc) to the Sales metric (calculated from Sales_Amt) requires only one join between tables since the Customer State ID and description are both stored in the LU_CUSTOMER table. As a result, the query has to access only one lookup table to obtain all of the necessary information for the report.

Even though achieving this result set requires only a single join, star schemas do not necessarily equate to better performance. Depending on the volume of data in any one hierarchy, you may be joining a very large lookup table to a very large fact table. In such cases, more joins between smaller tables can yield better performance.

Characteristics of a Star Schema

The following is a list of characteristics of a star schema.

  • Contains fewer tables (one per hierarchy)
  • Contains very large tables (much larger than some forms of snowflake schemas due to storing all attribute ID and description columns)
  • Store the IDs and descriptions of all the attributes in a hierarchy in a single table
  • Requires only a single join when querying fact data regardless of the attribute level at which you are querying data

—————————————————————————

Source: MicroStrategy University, MicroStrategy Advanced Data Warehousing, Course Guide, Version: ADVDW-931-Sep13-CG.

Tips & Tricks #12: How to Troubleshoot Cross Joins in SQL Reports for the SQL Generation Engine 9.x

MicroStrategy Community Banner

Readers:

In my last blog post, I blogged about the new MicroStrategy Community. Jaime Perez, VP of Worldwide Customer Services, and his crew have come up with a better way for us to engage with MicroStrategy as well as his team.

Speaking of Jaime, last June, he posted this great tip on the MicroStrategy Knowledgebase site as a TechNote. I am reblogging it since it is one of the most frequent questions I get asked and I find it an extremely useful Tip & Trick. Also, this will give you an idea of the great stuff being posted in the MicroStrategy Community.

Best Regards,

Michael

MicroStrategy and Cross Joins

In some scenarios, one may encounter cross joins in the SQL View of a standard, SQL Report in MicroStrategy.  Cross joins appear when two tables do not have any common key attributes between them in which they can inner join.  As a result, the two tables essentially combine together to create one table that has all the data from both tables, but this results in poorer performance with a common effect of increased execution times.  Sometimes these execution times, and performance hits, can be very severe.  Therefore, it is important to understand some simple steps that can be performed to resolve a cross join, as well as some steps to understand why it may be appearing in the SQL View of the report.

One common occurrence of a cross join is when a report contains at least two unrelated attributes in the grid, and no metrics are present in order to relate the unrelated attributes via a fact table.  Such a occurrence can be resolved in a few ways:

  1. Create a relationship filter, set the output level as the unrelated attributes (or the entire report level), and then relate these by a Logical Table object
  2. Create a relationship filter, set the output level as the unrelated attributes (or the entire report level), and then relate these by a Fact object
  3. Add a metric to the report that uses a fact from a table in which both attributes can inner join to

This provides a pathway from the fact table to the lookup tables in which the unrelated attributes are sourced from.  The result is an inner join between the fact table and the lookup tables, which resolves the cross join between the two unrelated lookup tables.

Options 1 and 2 provide a means in which the report template can remain as only attributes, whereas Option 3 would have a metric on the report.  Option 3 may not be desired if a metric does not want to be placed on the report.  Keep in mind that other techniques can also be employed to have the metric on the report, but formatted to be hidden from display.

More common scenarios include cross joins between a fact table and a lookup table, and are typically surprising to a developer.  These situations can be a bit more tricky to troubleshoot and resolve, but here are a few techniques that can be employed to try to resolve the issue:

  1. In SQL View look at where the cross join appears, and between which tables the cross join appears
  2. Open up those tables in the Table Editor by navigating to the Schema Objects\Tables folder, and double-clicking the tables
  3. Select the Logical View Tab of both tables to see all the logical objects mapped to the table
  4. Take note of which attributes have a key icon beside them
  5. These key attributes denote attributes at the lowest level of their hierarchy presently mapped to the table and/or attributes that are in their own hierarchy (meaning they have no parents or children)
  6. The SQL Engine will join 2 tables on common key attributes only, so if none of the key attributes on either table exist on both tables, then a cross join should appear

This means that just because a Region attribute exists on Table_A and a Region attribute exists on Table_B does not necessarily mean that the SQL Engine will join on Region.  If Region has its child attribute on the table, then that attribute should be the key as it is the lowest level attribute of its particular hierarchy mapped to the table.  If Region exists on both tables, and is also a key attribute on both tables, then an inner join should take place on Region.

This essentially means that one can find a cross join, investigate the tables in which it appears, and verify if at least 1 common key attribute exists between the tables.  If not, then that should be the first path to investigate because a cross join is correct in that scenario.

Video

You can find a detailed video on how this issue is reproduced and resolved here: Tech Note 71019 . Steps to reproduce and resolve

Note

MicroStrategy Technical Support can assist with resolving cross joins in a specific report, however caution should be taken when resolving such issues.  In some scenarios, the cross join is resolved through modifications to the schema objects, which can have a ripple effect to all other reports in an environment.  For example, if a relationship is changed in the Region attribute to resolve a cross join in one report, this change will be reflected in all other reports that use Region, and potentially the hierarchy in which Region belongs.  As a result, the SQL View of one report will have the cross join resolved, but the SQL may have changed in other reports using Region or its related attributes.  This may or may not be desired.  MicroStrategy Technical Support may not be able to fully understand the impact of such a schema change to the data model, so before a change is made to the data model the consequences of such a change should be fully understood by the developer, and any changes made to the schema should be recorded.

References

[1] Jaime Perez, TN47356: How to troubleshoot cross joins in SQL Reports for the SQL Generation Engine 9.x, MicroStrategy Community, 06/24/2014, http://community.microstrategy.com/t5/Architect/TN47356-How-to-troubleshoot-cross-joins-in-SQL-Reports-for-the/ta-p/196989.

[2] MicroStrrategy Knowledgebase, Tech Note 71019 . Steps to reproduce and resolve,

Tips & Tricks #11: The URL path is not visible in the report properties dialog box in MicroStrategy Web

The Issue

Each report and document has a URL path that could be used to directly execute the object when pasted in the browser address bar. The URL path can be found in the properties section when a user right-clicks on the object.

The expected view of that window is as follows:

The view observed by some users without the link feature:

The Cause

The required feature is not enabled in the MicroStrategy Web Admin page.

The Fix

  1. Navigate to the MicroStrategy Web Admin page.
  2. Select ‘Security’ on the left-hand side.
  3. Under ‘URL’, enable the feature – “Session information is included on the URL”.

 

Workaround 

If the session information setting should remain off for security reasons, use the Show Link feature as shown below.

 

Reference

MicroStrategy Knowledgebase Technical Note 39787, Updated July 22, 2013.

Tips & Tricks #10: How to Remove the Underlining from Hyperlinks Created on an Attribute or Metric

This is another one of those little tricks that can save you from pulling your hair out trying to figure it out.

By default, hyperlinks in a Report Services document are underlined. The underlining can not be removed by changing the format of the attribute or metric.

Tip 10-1

There is an Enhancement Request open with MicroStrategy on this, but MicroStrategy does currently provide a workaround.

Workaround to Remove the Underlining is to Clear the Default Link

Step 1: Right click the object that is linked and choose Edit Links.

Step 2: Click the Clear Default button while the link is highlighted.

Tip 10-2

 

Step 3: The underlining should now be removed as shown below.

Tip 10-3

CAVEAT: The only limitation to this workaround is that in order to now get to the link, the object has to be right clicked on. The user can not directly click on the object and be taken directly to the new page (see screenshot below).

Tip 10-4

Tips & Tricks #9: How Do Changes on the Source Report (Dataset) Get Reflected in MicroStrategy Report Services 9.x Documents

In MicroStrategy Report Services Documents, document datasets and their original source reports  (such as a grid report being used as a dataset) are not completely connected to each other. Depending on the changes made on the source report, it can be reflected differently on the document. Basically, the change can be divided into two types.

Type 1 – Formatting Changes

Formatting changes, for example, changing autostyles, thresholds, subtotals.

If a user chooses the option Add to Section without Formatting, the grid/graph showing on the document will not use the report’s stored formatting. Any formatting changes on the source report will not be reflected on the document.

Tip 9-1If a user chooses the option Add to Section with Formatting, the grid will be added with the current format of the report.  However, any formatting changes made to the source report AFTER the dataset has been included in the document will NOT be reflected on the document.

For example, a user disabled the subtotal (see screenshot below) for the original report after the report has been included as a dataset in a document, the document will still show the subtotals.

Tip 9-2

To force the document to recognize the report’s formatting changes, the user needs to delete the grid/graph from document section and add it again using the With Formatting option. By doing this, the latest formatting properties of the grid/graph on the source report are retrieved.

Type 2 – Adding/Removing Objects and Modifying Report Filters

Another type of changes made on the report involving adding/removing objects and modifying report filters.

Unlike the formatting change, this type of change does carry over from the source report to the document datasets.

For example, if users add/remove/modify report filters (see screenshot below), the change will be reflected on the data when running the document.

Tip 9-3

If an object is removed from the source report (see screenshot below), the user can see the change in the document’s Dataset Objects window. After the user runs the document, the object will be removed from grid/graph on the document.

Tip 9-4

If an object is added to the report (see screenshot below), the change will show in the document’s dataset objects window. However, the object will not be automatically added to the grid/graph. The user has to manually add the object to the grid/graph or add the dataset to the section again to make it show up.

Tip 9-5

NOTE:   As of MicroStrategy 9.0, a new feature was introduced where a user can add a dataset report to a Report Services document as a shortcut by selecting the Add to Section As a shortcut option, as shown below:

Tip 9-6

If the grid/graph is added to the document using this option, then the document would be updated automatically if ANY type of change is made on the source report.

Tips & Tricks #4: How to Create N-Level Deep Nested Prompts in MicroStrategy Developer (Desktop)

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

  1. 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“.
  2. Click “prompt on attribute element list” and then click “Next” through the rest of the screens to accept the default values.
  3. Do not set any additional conditions. Save the filter as “Year Filter“.

Trick4-1

Trick4-2

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

  1. Create a filter on attribute “Quarter” and click “Prompt“.
  2. Select “Use a filter to reduce the number of elements” and select the “Year Filter” created in the previous step.
  3. Save this filter as “Quarter Filter” .

Trick4-3

Create the Month Prompt

  1. Follow the same basic procedure as above to create a Month filter. Create a filter on attribute “Month“.
  2. Click “Prompt“.
  3. Select “Use a filter to reduce the number of elements” and select the “Quarter Filter” created in the previous step.
  4. Save this filter as “Month Filter” .

Trick4-4

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.

Trick4-5

Trick4-6

Trick4-7

Tips & Tricks #3: How to Define Custom Subtotal Displays in MicroStrategy Desktop

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.

Trick2-1

Trick2-2

To define a specific subtotal displays for a report like the one shown above, follow the steps below:

  1. Select Subtotals from the Data menu. The Subtotals dialog box opens. Clear the Totals check box to remove the standard subtotals.
  2. Click Advanced.
  3. Click New to create a custom subtotal.
  4. 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.

Trick2-3

 

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.

  1. Check the Total for the #P #0 subtotal (shown below).
  2. Click Advanced.
  3. Select Across level and then select the Region and the Employee as the levels.
  4. Click OK to save the new subtotal.
  5. Click OK to return to the Subtotals dialog box.
  6. Click OK.

Trick2-4

The report should now look like this.

Trick2-5

Tips & Tricks #2: In MicroStrategy Web, Report Execution Fails with Error ‘Results for this message cannot be retrieved from MicroStrategy Intelligence Server. This might be because the execution has failed. Please contact your administrator.’

I had this error the other day. Fortunately, I had just been reading about working set governor settings the other night to prepare for the CPA and MCE exams.

First, let’s discuss the issue.

Issue/Problem

In MicroStrategy Web, when I executed a report, I received the following error message (also, see screenshot below).

(Results for this message cannot be retrieved from MicroStrategy Intelligence Server. This might be because the execution has failed. Please contact your administrator.)

Results cannot be retrieved error message

Basically, what happened was my report request could not be processed. Looking through the MicroStrategy KnowledgeBase, it basically tells you to try to run the report again. If it still throws this error, contact your Administrator.

If you look at the Web Log on the Web Server, you will see an error similar to the following:

<record reset=`true`>
 <package>com.microstrategy.webapi</package>
 <level>SEVERE</level>
 <miliseconds>1157577081154</miliseconds>
 <timestamp>09/06/2006 14:11:21:154</timestamp>
 <thread>154</thread>
 <class>CDSSXMLReportServer</class>
 <method>GetExecutionResultsEx</method>
 <message>(Results for this message cannot be retrieved from 
MicroStrategy Intelligence Server. This might be because the
execution has failed. Please contact your administrator.) 
(com.microstrategy.webapi.MSTRWebAPIException)</message>
 <exception>com.microstrategy.webapi.MSTRWebAPIException: 
(Results for this message cannot be retrieved from 
MicroStrategy Intelligence Server. This might be because
the execution has failed. Please contact your 
administrator.)
 at com.microstrategy.webapi.
CDSSXMLReportServer.GetExecutionResultsCommon(Unknown Source)
 at com.microstrategy.webapi.
CDSSXMLReportServer.GetExecutionResultsEx(Unknown Source)
 at com.microstrategy.web.objects.
WebReportInstanceImpl.
getExecutionResults(Unknown Source)
 at com.microstrategy.web.objects.
WebReportInstanceImpl.pollForResults(Unknown Source)
 at com.microstrategy.web.objects.
WebReportInstanceImpl.populateUserReportCache(Unknown Source)
 at com.microstrategy.web.objects.
WebReportInstanceImpl.pollStatus(Unknown Source)
 at com.microstrategy.web.beans.
ReportInstanceProxy.pollStatus(Unknown Source)
 at com.microstrategy.web.beans.
ReportBeanImpl.localCollectData(Unknown Source)
 at com.microstrategy.web.beans.
ReportBeanImpl.doCollectData(Unknown Source)
 at com.microstrategy.web.beans.
AbstractWebBean.collectData(Unknown Source)
 at com.microstrategy.web.app.beans.
AbstractAppComponent.collectData(Unknown Source)
 at com.microstrategy.web.app.beans.
ReportFrameBeanImpl.collectData(Unknown Source)
 at com.microstrategy.web.app.beans.
AbstractAppComponent.collectData(Unknown Source)
 at com.microstrategy.web.app.beans.
PageComponentImpl.collectData(Unknown Source)
 at com.microstrategy.web.app.
MSTRWebController.processRequest(Unknown Source)
 </exception>
 <userName>Administrator</userName>
 <clientID>172.19.19.2</clientID>
 </record>

The DSSErrors.log file on the MicroStrategy Intelligence Server contains the following errors:

[Kernel][Error] MsiWorkingSet::PersistMsg(): 
failed to attach RI to msg, error 0x80003F79
 [Kernel][Error] CDSSServerMessage::put_OriginalRI: 
WSResultPool->AddRI for msg xxxx return error 0x80003F79
 [Kernel][Error] CDSSServerMessage::put_OriginalRI: 
WSResultPool->AddRI for msg xxxx return error 0x80003F79
 [Kernel][Error] CDSSServerMessage::GetReportInstance():
get_OriginalRI() return error 0x1

Now what?

Solution

The size of the report that was to be executed has 40MB report cache size while the ‘Maximum RAM for Working Set cache size’ is set as 102,400KB, as shown in the image below:

Governing Rules - Default - Working Set

The MicroStrategy Intelligence Server was unable to swap out the report instances of 40MB in the Working Set.  To resolve this issue, I needed to increase the size of Maximum RAM for Working Set cache to a higher value, for example 512,000KB.

What is the ‘Working Set’ Governor Setting?

When a user runs a report from MicroStrategy Web or Web Universal, the results from the report are added to what is called the working set for that user’s session and stored in memory on the Intelligence Server. The working set is a collection of messages that reference in-memory report instances. A message is added to the working set when a user executes a report or retrieves a message from his or her Inbox.

The purpose of the working set is to:

  1. Allow the efficient use of the web browser’s Back button.
  2. Improve web performance for manipulations.
  3. Allow users to manually add messages to the History List.

This setting is accessible via the MicroStrategy Intelligence Server Configuration as shown below:

Governing Rules - Default - Working Set

Working Set Governors

The ‘Working Set file directory’ is the location in the filesystem where the Report Instances may be persisted on disk. A report instance will be persisted on disk in binary format if its size exceeds the limit set by the ‘Maximum RAM for Working Set cache’ governor or none of the report instances in memory can be swapped to make room for the new report instance. The persisted report instance will be persisted as the <filename(GUID)>.po and may be reused if the report is invoked again.

The ‘Maximum RAM for Working Set cache’ is the governor that modulates the size of the WorkingSet Result Pool. The Maximum value is: 2147483647 MB, the Minimum value depends on version and is 200 MB in 9.3.1, and the Default value is: 200 MB. Note that if the value specified is greater then the machines memory it uses the default of 200 MB. The default value is usually sufficient, but if memory issues arise, as noted above, the setting can be increased. Increasing this setting means that the MicroStrategy Intelligence Server may be operating with a higher average memory footprint during its lifecycle, so proper tuning may be needed if memory usage becomes an issue.

Important Notes

  • There is no Working Set (WS) for a session created by the MicroStrategy Desktop client.
  • This is a MicroStrategy Intelligence Server configuration level setting, so it applies to all the projects and all the users and is not specific to a project. If these settings are changed, MicroStrategy Intelligence Server may need to be restarted.
  • The MicroStrategy Working Set is not the same as the Microsoft Windows Operating System Working Set.

Tips & Tricks #1: How to filter more than one substring in the Find search box when selecting a long list of elements from a dynamic prompt list

There are cases in which a dynamic prompt is used to qualify attributes selected to be part of a report, but the attribute list is very long.

Attributes like ‘Street Address’ may contain keywords like ‘St’, ‘Ave’, ‘Dr’ that can be used to reduce the list of selected elements.

It is possible to filter the list using the find box and to include one or more substrings that can be logically ‘ORed’ (Will be included if any of the substrings is found) or logically ‘ANDed’ (Will be included if all substrings).

Pattern delimiters are ‘ ‘ (space), ‘%’ and ‘_ ‘

Space or blank  = will be used for logical ‘OR’

‘%’  =  Will be used for logical ‘AND’  usually in pairs (begin-end of string)

‘_’   =  Will be used as wildcard in lieu of space (blank)

 CASE 1: Filter all attributes that contains ‘z’ or ‘x’ strings:

Case1

In this example, all listed addresses contains string ‘z’ OR string ‘x’ in any position of the description.

CASE 2: Filter all attributes that contains first the string ‘Old’ AND then ‘Hwy':

Case2

Observe that substring ‘Old’ and ‘Hwy’ can be part of other string and can start in any position.  However, using %Hwy%%Old% will produce a different result.

In this example, this filter will produce an empty list as there is no address that have substring ‘Hwy’ before ‘Old’.

CASE 3: Filter all attributes that contains the substring ‘Old Hwy':

Case3

Special character  ‘_’ should be used to represent a single space. Cannot be duplicated for representing multiple space.

In this example, if the filter were ‘_Old_Hwy’, the last two elements will not be shown as they do not begin with spaces.

NOTE: Examples shown in MicroStrategy Desktop, but it apply also to the Web interface when using the find box in dynamic prompts.

Follow

Get every new post delivered to your Inbox.

Join 118 other followers