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.

 

7 thoughts on “Performance in Reports (Part I)

  1. André Carvalho

    Very good tip. I had already used but did not know it affected the querry. Thanks for the advice.

    Like

    Reply
  2. Pingback: Performance in Reports (Part II) | MicroStrategy - Vilela's

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s