Layout examples for dashboards

Hello there!

What if you don’t have an idea for layout to create a dashboard? Where do you look for inspiration?

I constantly look for dashboards pics in http://pinterest.com.  Just try to search for

dashboard

” there. You will see that there are a lot of good examples of there!

What about you? Where do you

look for inspiration?

Post in the comments below!

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 Mobile Manual Back Button

Hello there!

URL API can do a lot of cool things in Web and even in Mobile environment. One of that is the possibility to create a manual back button that goes to the last report/document that the user was executing.

That’s a very easy thing to do, just:

1. Create text, right click on it and choose “Edit Links…” option.

2. In Navigate to this URL, type:

mstr://?evt=3124

That’s it. Now you can go back to the previous report/document using this object.

You can use this feature in every object that is able to use Hyperlinks.

I 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.

 

Changing the default formatting for Graphs

Hi there!

Today let’s talk about changing the default formatting of your graphs!

Everybody knows that is almost impossible to use the default formatting of MicroStrategy Graph. You always have to change the colors, fonts, sizes, number format, and so on. So, if you use 5 times the horizontal bar graph, you have to format it 5 times… That is so boring…

What if you edit it once and everytime that you insert a new horizontal graph, for example, you get that specific formatting? Wouldn’t that be nice? In fact, it can be done and it is very easy to implement.

To do that, you have to:

1. Edit the formatting of your graph in Desktop/Developer and apply your format to it.

Here I’m going to change the Horizontal Bar graph formatting.

pic1

2. While editing, go to Graph -> Save Graph Style and name it as: HorizontalBar.

pic2

3. Go to the folder: C:\Program Files\Common Files\MicroStrategy\GraphStyles or C:\Program Files (x86)\Common Files\MicroStrategy\GraphStyles, make a backup of the HorizontalBar.3tf file that is inside that folder, and substitute it with the new file that you have just saved.

pic3

pic3

 

4. Thats it!!

The next time that you use the Horizontal Bar graph, you will get your new cool and well done formatting.

You can also create a folder with all edited graphs and just apply the styles to your graphs manually. You just have to edit your graph, go to Graph -> Apply Graph Style and look for your .3tf file that you have created.

pic3

I hope it helps.

God bless you.