Category Archives: Reports

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

Search string in SQL View

Hello there!

I don’t know if you had to search for a specific string in the SQL view and had to copy and paste in a text editor to search for it.

There is a shortcut that you can use to search in the SQL View.

Just go to the SQL View and hold Ctrl + i.

That’s it. Now you can search in SQL View.

 

God bless you!

Parallel Query Execution

Hello there!

There is a excellent vldb option to execute in parallel independent sql passes.So. if you have more than one pass in your SQL and those aren’t dependent, you can instruct Microstrategy to execute them separately. Microstrategy will open DB connections to execute those SQLs.

Example:

You have a query with some passes like:

Pass1

 Pass2

  Pass3

   Pass4

If Pass1 isn’t dependent of Pass2, Microstrategy will execute them separately.

To do that, you just have to:

1. Open your report;

2. Click on Data -> VLDB Properties

3. Select Query Optimization -> Parallel Query Execution

pic

Justchoose one of those 2 “Enable” options. Just read it and see which one is better for you.

Hope it helps!

God bless you!

Removing Auto Apply changes in Page-by

Hello there!

Today I’m going to show you how to remove the auto apply changes made in a Page-by in a report for Web.

When you put an attribute in a page-by section of your report, and changes to a different element, it will automatically apply that change.

To change that behavior inserting an apply button, you just have to:

1. Enter in you project in MicroStrategy Web;

2. Go to Preferences;

pic

3. Go to Project Defaults -> Grid Display -> Uncheck “Automatic page-by”.

pic

Now when you put an attribute in Page-by you will see a button like this:

pic

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.

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.