Alias attribute / view original attribute name via MicroStratey Web

This is one is an easy one but i thought it would be good to keep it as a reference.

How to alias an attribute used in a document using MSTR web:

• Open the document in edit mode
• Right Click on the attribute we want to create an alias for
• Select "Rename / Edit..."

 The Rename/Edit editor by default has the attribute we right click as the default in the "Object" drop down, but we can select other attributes in the grid by changing the selection.

• Type the attribute alias in the "Name:" text box
• Click "Apply" and "Ok"

How to view the original attribute name after we have created an alias for an attribute:

• Right Click on the attribute we want to check the original attribute name for
• Select "Rename / Edit..."

The original attribute name the alias is mapped to appears in the "Definition" Box

Trying to open DB Query Tool generates Java heap space error

When trying to open DB Query Tool from MSTR i kept getting the following error message:

"Not enough memory to create the JVM"

Yes i know, my laptop currently only has 4GB of memory and running MSTR desktop and other tools my memory is quickly allocated and thus not available to start the JVM process.

One way to restrict the amount of memory Java can use from the available memory is by passing the -Xmx option in the command line when initializing the command.

In Windows go to Start->Search for DB query tool or navigate to "All Programs" -> "MicroStrategy Tools" and right click in the "DB Query Tool"and select properties.

Under the Shortcuts tab at the end of the Target command add -Xmx512M where 512 can be any value  multiple of 1024 greater than 2MB.

"C:\Program Files (x86)\Common Files\MicroStrategy\MADBQueryTool.exe" -Xmx512M

More on -Xmx

I set mine to -Xmx512M and the application started correctly.

Metric to perform count based on result of another metric

Working on a project i needed to count how many times a metric was returning either 0 or more than 200%. Looking for ideas on how to build such metric i came accross this tech note in the MSTR KBase TN: 39189

The technical note game the bases to build the metric I needed.

This example shows how to build a metric that will perform a count based on the result of another metric.

I will use the tutorial project to create a simple example.

Let's say we have the following grid displaying Call Center and Discount %

We can create a metric that counts how many call centers had a "Discount %" greater than 3%

• Create a new embedded metric (Right click in Report Objects Window select Insert->New metric)
• Add the following formula to the metric: IF(([Discount %] > 0.03), 1, 0)

The formula of the metric has a simple IF statement that outputs a 1 if the condition, in this case "Discount %" metric value is higher than 0.03.

The output of the above metric can be seen here:

If we need a count of the total the metric can be modified to show the total instead of the individual values that meet the condition:

• Edit the metric formula with Sum(IF(([Discount %] > 0.03), 1, 0))

The if condition can also include AND and OR modifiers to increase the logic inside the IF statement.

Let's say we wanted to count "Discount %" under 2.9% and above 3.2% we could rewrite our metric with this formula: IF((([Discount %] < 0.029) Or ([Discount %] > 0.032)), 1, 0)

The metric can be used in a document with a higher level attribute and it will aggregate the values showing a total.

Creating a copy of a document based of imported data as Intelligent Cubes

At work we had to create a quick Document (PDF mode) from a bunch of data provided as spreadsheets for a particular client. This was a quick proof of concept and it was decided to import the data as cubes. We ended up with around 12 cubes from which we created reports, some in grid mode some in graph. From those reports we created the final document.

The problem came when a few weeks later we were asked to create another such document this time not for a particular client but generic using the same data structures but with different values.

Instead of republishing the already existing cubes and modifying the document, which would've been a perfectly fine solution, we decided to create a copy of the original (just in case we were asked to run the original document again for that particular client with new data).

The problem with creating a copy in MSTR is the object dependencies.

The Document has the reports as components and the reports at the same time have the cubes as components. Creating a copy of the document, reports and cubes does not associate the new document with the new objects but it retains the references to the original reports.

To repoint reports to the new cubes and document to the new reports we had to follow this high level steps:

• Republish Cube with new data
• Edit report and point it to the new cube
• Replace Document dependency to the new report

Republish Cube with new data

Using MSTR web we republish each of the cubes with the new generic data. This was fairly straight forward and we did not encounter any problems.

Before we settle with the copy and republish approach we tested creating the cubes from scratch first. Creating new cubes did not go well when repointing the reports to the new cubes. Even when the data structures were the same MSTR complaint that columns that existed int he previous cube were now missing from the new cubes.

Because of a little quirk with step three "Replace Document dependency to the new report" where the tool only replaces reports in grid mode in order to work around that issue the cubes have to be republished... (more on this when we get to step three)

Creating a copy of the original and republishing the data worked as expected.

Edit report and point it to the new cube

Using MSTR Desktop, edit the report, select Data -> Intelligent Cube Options -> Point Grid to Intelligent Cube.

Save the report.

Note: If the report contains derived metrics, MSTR will alert you that those metrics are not present in the cube. The message can be ignore, the report will work correctly with the new data.

Replace Document dependency to the new report

To replace the report dependency in the document from the original reports to the newly created ones i used the "Find and Replace" functionality in MSTR desktop.

I first came upon this new functionality thanks to Bryan's blog  (great resource for all things MSTR)

In MSTR desktop select Tools -> Find and Replace

1. "Select" - In the "Find and Replace" dialog select "Object Dependencies" as the property to modify. For dependee object we will select the original report we want to modify the reference for (From above we would select RA)
2. "Find" - Select here the new Document you want to replace the dependencies for. (From the screenshot above we select "Copy of DA"). Click "Update Summary"
3. "Replace with" - Select the new report dependent. (From above we select "Copy of RA")
4. Click Replace

Repeat the above steps with all other report dependencies you need to replace.

Note: If you have derived metrics in your report instances in the document, those metrics will have to be added again. After replacing the report dependency the derived metrics disappear from the report instance in the document. This is true for reports added to the document as a copy i have not tested what happens when reports are added as a shortcut.

After completing the above steps you will have a fully independent copy of all your objects

The Quirk

MSTR "Find and Replace" tool only allow to find and replace reports in grid view mode, to replace those reports in graph view mode we had to:

• Edit the report
• Switch to grid view mode 
• Save the report
• Find and replace dependency
• Edit the report again
• Switch back to graph
• Save the report

The above steps had to be done for both the original and copy reports. With the copy reports, if the copy reports still point to the original data when switching from graph to grid view mode MSTR tries to run the report but complains that the cube has not been published. To workaround this, republish he cubes before switching the copy reports from graph to grid.