Tag Archives: report

Link to different reports in a grid/graph

Hello there!

Today I’m going to talk about how to link to different reports/documents using the same grid/graph without any intervention from the user.

As you may know, when you create a link to a report/document in a grid/graph you can specify more than one hyperlink. But, when the user clicks on a specific item, it shows all hyperlinks and he/she has to choose which hyperlink to click on.

What if you must go to a specific report/document for item A, to another for item B, etc, and if the user choose a wrong one, the result is going to be wrong…

So… Let’s do that!

I’m going to use SQL SERVER as my Data warehouse in this example. The syntax may vary if you use different Database.

1. I’m going to create 2 reports that’s going to be used as the link.

2. Get the ID of them. To do that, you can use MicroStrategy Developer, right click on the report 1 -> go to Properties -> Copy the content of the ID field. In my case, for report 1 the ID is 6360D76C4D340D8B79D988A9A759485B and for report 2 is 8F43AC804E18E30709B73DAB83413D00.

3. Now, I’m going to create a FreeForm SQL report using this SQL:

SELECT a11.[CATEGORY_DESC] as CATEGORY_DESC,
CASE
a11.[CATEGORY_DESC]
WHEN 'Book' THEN ('6360D76C4D340D8B79D988A9A759485B')  
WHEN 'Electronics' THEN ('8F43AC804E18E30709B73DAB83413D00')  END 
FROM [LU_CATEGORY] a11

In my CASE statement, I’m specifying to Microstrategy which report has to be used for each specific category.

Now, create 2 managed atributes for that FreeForm.

ID_REPORT     attribute form: ID

ID_REPORT_DESC     attribute form: DESC

And then save it as baseReport.

4. Create a new dashboard and insert a Vertical Bar Graph using the baseReport as the source.

5. Create a Panel Stack and set it as an Information Window.

6. Now, I will use Microstrategy Web to see the links to my reports. To do that, just browse the folders until you see your saved reports -> Right click on Report 1 -> Go to Share -> Show Link -> Copy the content of it.

7. Copy the link an replace the reportID with {ID_REPORT_DESC}:

http://localhost/MicroStrategy/asp/Main.aspx?evt=4001
&src=Main.aspx.4001&
reportID=6360D76C4D340D8B79D988A9A759485B
&visMode=0&reportViewMode=1
&server=SERVER195&Project=EXAMPLE01&port=0&share=1

Just change the reportID content to {ID_REPORT_DESC}.

http://localhost/MicroStrategy/asp/Main.aspx?evt=4001
&src=Main.aspx.4001&
reportID={ID_REPORT_DESC}
&visMode=0&reportViewMode=1
&server=SERVER195&Project=EXAMPLE01&port=0&share=1

Insert an HTML Container object inside the Information Window Panel Stack and add the <a> HTML tag to create a link. Something like this:

 <a href="http://localhost/MicroStrategy/asp/Main.aspx?evt=4001
&src=Main.aspx.4001&reportID={ID_REPORT}&visMode=0
&reportViewMode=1&server=SERVER195
&Project=EXAMPLE01&port=0&share=1" target="_blank">Link</a>

I just added target=”_blank” to open the link in another window. If you don’t want that, just remove it.

“Link” is the static text that will appears after you click on the bar of your graph, you can change to another

Right click on the attribute of the graph -> Click on “use as selector” and target the panel stack that was used as the Information Window.

 

Hope it helps.

God bless you!

Report Performance Tips

Hello there,

I was searching for report performance and I found a nice post in this url:

https://community.microstrategy.com/t5/Reporting-Dashboards-and/Report-Performance-Tips-and-Tricks/m-p/278300#M11431

The user Omar V wrote some tips to help improving the report performance that you should try and see if those VLDB settings works for you.

Hope it helps!

God bless you

Using Bulk Export Reports

Hello there!

Today let’s talk about how to increase performance when exporting a report in MicroStrategy.

Sometimes we have a large report that contains a lot of rows and it can be a problem for Intelligence Server to generate and export that report.

There is a way to optimize the exportation, so your environment will consume less memory and export quickly: Bulk Export.

By default, MicroStrategy loads the entire report in memory to make the manipulation faster. The amount of memory that can be used for this purpose can be set in the Working Set parameter in MicroStrategy Developer/Desktop.

Once you change your report to Bulk Export you can’t change back to a regular report. So, if you want to have a visualization report, you must duplicate your report and convert just a copy to bulk export.

To create your bulk export report you just have to:

1. Open Developer/Desktop;

2. Create a regular report;

3. Click on Data -> Configure Bulk Export

pic2

4. You need to select the database instance that MicroStrategy will use to allocate the temporary tables needed to export your report. In my case, I’m going to use the Tutorial Data, you must use your own DW database instance.

pic2

5. It will export your report as a .csv file, so you need to configure your text file with a delimiter. You can use the default there is Comma, if you want.

6. Click Ok and Save your Bulk Export Report. You will see that the icon of your report has changed.

pic2

7. Open MicroStrategy Web and click on the bulk export report created. It will open a page with for creating a new subscription.In Bulk Export Report, you can only send reports to a file location.

pic2

Just insert the sub-folder name, if needed and MicroStrategy will generate your text file using that report data.

pic2

Hope it helps.

God bless you!

Performance in Reports (Part II)

Hello there!

Today I’m going to talk about the second part of the series “Performance in Reports”. If you would like to read the first part just click on this link “Report Performance (Part I)“.

I believe that nowadays I always see the query that Microstrategy creates when creating a new report. It recommend you to do so.

Sometimes you see that the query delays so much, and you don’t know what is going on.

Let’s believe that you have created all indexes needed to perform that query and still you want to improve the performance. Have you already heard about aggregated fact table?

Let’s analyse this simple database below:

pic1

As you can see, we have 2 hierarchies and 1 fact table here. Inside hierarchy Employee we have two dimension tables: employee and language. Two tables in geography hierarchy: country and continent. The fact Salary was created using the lowest level of the hierarchies: employee_id and country_id. That facts are commonly called Base Facts, just because it uses the base (lowest level) of your hierarchies.

So, if you want to display language_DESC and continent_DESC in a report? Microstrategy needs a few more steps to accomplish that, as you can see below:

select language_DESC, continent_DESC from Salary_fact a1
inner join employee a2 on (a1.employee_ID = a2.employee_ID)
inner join language a3 on (a1.language_ID = a3.language_ID)
inner join country a4 on (country_ID = a1.country_ID)
inner join continent a5 on (a5.continent_ID = a1.continent_ID)

It needs to go to Employee and Country tables because Salary doesn’t have a bridge to language and continent, only to employee and country.

Now, look at this datagram below:

pic2

I have added another fact called Salary_Fact_Agg that contains language_ID and continent_ID. Now, Microstrategy reduce the steps to get those columns.

select language_DESC, continent_DESC from Salary_fact_Agg a1
inner join language a2 on (a1.language_ID = a2.language_ID)
inner join continent a3 on (a1.continent_ID = a3.continent_ID)

That will make your query run faster than the first one.If you reduce the amount of joins that is required to get the data that you desired, you will increase performance of your report. So, if you change the granularity of your facts aggregating them in a higher level, you create an aggregated fact table.

 PROS

  1. Performance – The amount of joins is reduced and your query is executed faster.

CONS

  1. Database size – Creating more facts will increase the size of your database;
  2. ETL complexity – ETL needs to maintain another fact;

An advice:

Only create aggregated fact table when you notice that you have a lot of queries that are using a higher level dimension than the base facts that you have and the performance isn’t that good. Don’t agg facts at the beginning of your data warehouse creation. You have to test the base facts first and see if it’s really required an aggregate fact table to resolve performance problems.

 

Hope it helps.

God bless you.

Hide permanently the “Metrics” column for MicroStrategy Web

Hello there !

Today let’s talk about removing the “Metric” column forever in MicroStrategy Web !!!

I always have to remove the extra column (web only) for every grid that I create in MicroStrategy, because I just don’t like seeing that there. But sometimes is very boring doing that everytime that I create a new report or grid in a document. So, if you are like me that simply don’t like that extra column, here is a very good tip to remove it forever from your Microstrategy Web visualization.

To do that you just have to:

1. Make sure the language that you are using in your projects in MicroStrategy web. To see which language your project is setup, just go enter in a project, go to Web Preferences -> Project Defaults -> Language.

pic1 pic2

2. Go to the folder where your MicroStrategy Web is installer. If you use IIS, go to: C:\Program Files (x86)\MicroStrategy\Web ASPx\WEB-INF\classes\resources or C:\Program Files\MicroStrategy\Web ASPx\WEB-INF\classes\resources. If you use Tomcat, C:\Program Files\Apache Software Foundation\Tomcat X\webapps\MicroStrategy\WEB-INF\classes\resources (where X is the version of your installed Tomcat ).

3. Edit the MessagesBundle specific to the language that you use in your MicroStrategy Web. In my case I use English, so I’m going to edit the MessagesBundle_en_US.properties file. If I was using Spanish, for example, I just have to edit the MessagesBundle_es.properties file, and so on.

pic3

4. Open the file in notepad (or other text edit of your choice) and find the word “Metrics”.

pic4

5. In version 9.4.1 Hotfix 3, just delete the word “metrics” from the “mstrWeb.962” content, andadd blank a space.

pic5

6. After that, restart MicroStrategy Web. If you use IIS, click in: Start, click Run type IISReset, and then click OK. If Tomcat, go to Tomcat Monitor and restart your web server.

7. Now, create a new report and you will see, when you execute it, that the “Metrics” column has just gone forever!!

pic6

Enjoy it! Hope it helps!

God bless you.