Tag Archives: MicroStrategy

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!

Dropdown with multiple selections

Hello there,

Today I’m going to show a hack of  how to allow multiple selections using a dropdown.

Felipe César sent me that tip. Thank you, Felipe. I really appreciate that.

To create this dropdown you will need to:

  1. Create a Dropdown selector as usual;
  2. Right click on the selector and select Properties and formatting;
  3. Go the Layout and select from the DHTML style “Listbox” and check “Allow multiple selections”
  4. Go to Theme and select Light Theme
  5. Go back to Layout and select Dropdown
  6. Go back to Theme and select None
  7. Click OK and you will have something like this:

Hope it helps.

God bless you!

Performance in Reports (Part III)

Hello there!

Today I’m going to talk about a simple technique to reduce the amount of cache used in your project.

As you may know, Microstrategy is able to cache the reports to run it much faster than normal. It’s stored in memory ram and disk. So, it reduces the amount of space of your Hard Disk and Memory RAM of your Intelligence Server machine.

The idea is pretty simple:

Create a single report to be used in more than one document.

For example, if you have to bring: category and subcategory in one report and region and city in another one, you can create 1 report that contains all of those attributes and use this report for a dashboard that contains region and city and another one that contains category and subcategory.

So, always think about creating 1 report to cover more dashboards instead of creating one report for each document.

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.

Cool URL API events for MicroStrategy Mobile

Hello there!

I’ve already spoke about how to create a manual back button in MicroStrategy Mobile, and now I am going to talk about more cool events that we can create using URL API for Mobile. To do all those examples below you just have to create a link in an image or textfield, for example, and insert the URL of the example in the “Navigate to URL” field.

1. Email a screenshot of document

With this event, you can create a screenshot of your panel and send it using the email from Mobile devices automatically.

Just use this url below:

mstr://?evt=3037&emailSubject=Look!

You can change the subject of the email just changing the &emailSubject parameter value.

2. Display an Information Window

You can use grid/graphs to open an information window to show more info about that specific element that you have tapped. But sometimes you want to display a help information window just tapping an image or text.

You can do it using this url below:

mstr://?evt=2048500&panelName=NameOfThePanel

You just have to change the &panelName parameter value to use your panel stack information window name. You can see the name of the panel with a right click on your panel stack -> Properties and formatting -> General -> And then copy the Name field value.

3. Re-prompt a document

Sometimes users don’t know how to re-prompt a document, that filter button isn’t that intuitive, and now, you can create your own re-prompt button!

Just use this URL below:

mstr://pr

That’s it for now.

Hope it helps.

God bless you.

Microstrategy suite… No more

Hello there!

This will be a quick post. It’s just an advice to you guys that work with Microstrategy suite.

Microstrategy is about to remove the suite from the website. They will not work with this kind of free software anymore. So, if you would like to get the lastest version (9.4.1) and use the free version, do it now!

I don’t know what is going to happen with the free version, but, as long as I know, there won’t be a free version anymore…

God bless you.

Performance in Reports (Part I)

Hello there!

Today I’m going to talk about a very good tip about increasing performance in reports. Ths is a very easy and simple to do. So, let’s go!

PS: This tip is only possible if you have more than 2 descriptions attribute forms in your attribute and you will not display all of it.

For this example, I’m going to:

1. Create a new report in Microstrategy Tutorial project and add: Customer and Cost to rows and columns respectively. I will only display “First Name” for Customer attribute.

pic1

pic1

Check the SQL. As you can see, Microstrategy brings the ID, First Name and Last Name from the DB.

create table ZZEA00 (
    CUSTOMER_ID    LONG, 
    WJXBFS1    DOUBLE)


insert into ZZEA00 
select    a11.[CUSTOMER_ID] AS CUSTOMER_ID,
    sum(a11.[TOT_COST]) AS WJXBFS1
from    [CUSTOMER_SLS]    a11
group by    a11.[CUSTOMER_ID]

select    pa11.[CUSTOMER_ID] AS CUSTOMER_ID,
    a12.[CUST_LAST_NAME] AS CUST_LAST_NAME,
    a12.[CUST_FIRST_NAME] AS CUST_FIRST_NAME,
    pa11.[WJXBFS1] AS WJXBFS1
from    [ZZEA00]    pa11, 
    [LU_CUSTOMER]    a12
where    pa11.[CUSTOMER_ID] = a12.[CUSTOMER_ID]


drop table ZZEA00


So, instead of removing it from your SQL, the Microstrategy Query Engine is bringing all data to the report execution, and can you choose which one you would like to display at runtime.

But, if you will never display “Last Name” column, for example, it’s not a clever thing to do.

So… Let’s really remove “Last Name” columns from your SQL.

2. Go to Data-> Attribute Display -> Select Customer from the dropdown list -> Check the second option “Use the following attribute forms” -> Remove Last Name from the Report Data Objects area.

pic2

pic2

3. Go to SQL View and now as you can see, there is no “Last Name” column!

create table ZZEA00 (
    CUSTOMER_ID    LONG, 
    WJXBFS1    DOUBLE)


insert into ZZEA00 
select    a11.[CUSTOMER_ID] AS CUSTOMER_ID,
    sum(a11.[TOT_COST]) AS WJXBFS1
from    [CUSTOMER_SLS]    a11
group by    a11.[CUSTOMER_ID]

select    pa11.[CUSTOMER_ID] AS CUSTOMER_ID,
    a12.[CUST_FIRST_NAME] AS CUST_FIRST_NAME,
    pa11.[WJXBFS1] AS WJXBFS1
from    [ZZEA00]    pa11, 
    [LU_CUSTOMER]    a12
where    pa11.[CUSTOMER_ID] = a12.[CUSTOMER_ID]


drop table ZZEA00

Doing that, your query will run faster and you will get the same result as would expect to. It’s always a good idea to remove the columns that you aren’t going to use in your report because it will decrease the amount of data brought from your Database and the amount of data transfered by the network.

If you would like to remove that column to never get that by default while using that attribute, you can:

1 Edit your attribute -> Go to Display tab and remove “Last Name” from the Display object forms section.

pic3

Hope it helps.

God bless you.