Traditional web-based reporting with SAS BI tools

Registering Indexes on metadata repository

Reply
Contributor
Posts: 59

Registering Indexes on metadata repository

Hi,

I have a SAS dataset which is 800MB in size with 15-20 variables which would be source to a SAS report on the SAS Web report studio. I created an index on this dataset and the file gets created with an extension .sas7bndx.

The index was created on 4 attributes (PORTFOLIO PRODUCT YEAR MONTH). These are the exact same attributes on which any user can filter data before viewing the actual report.

I tried testing the index on Base SAS by filtering the data on one of the attributes and resuts are below.

Results from Index Dataset - - - > 27 seconds
Results from Non - Index Dataset - - -> 42 seconds.

However, the time it takes to render a report after clicking on "View Report" remains exactly the same on the WRS. Do we need to register an index on the metadata repositary seperately or does the index gets registered automatically once the SAS dataset is registered. I looked at the properties of the dataset on the SAS Management console and it does shows that the dataset is indexed on 4 attributes.

Any help will be greatly appreciated. Thanks

Tej
SAS Employee
Posts: 238

Re: Registering Indexes on metadata repository

Tej,
You might review the difference between running a test in Information Map Studio vs running the Web Report Studio report. If the web site is slower, than performance improvements should be considered on the web tier itself. There are post configuration tasks mentioned (such as 9.1.3: http://support.sas.com/documentation/configuration/biwaag.pdf) to increase java heap and stack size.

~ Angela
http://sas-bi.blogspot.com
SAS Super FREQ
Posts: 464

Re: Registering Indexes on metadata repository

Hello,

I also suggest that you review this SAS note for tips on generally improving performance.: http://support.sas.com/kb/39/065.html
Contributor
Posts: 59

Re: Registering Indexes on metadata repository

Thanks Anjela and BIC_USER_3 for posting your responses on potential solutions. Anjela, could you just a little bit explain your note "You might review the difference between running a test in Information Map Studio vs running the Web Report Studio report" I mean i can definately test by running it on WRS, how should I test on Information Map Studio. I am sorry if this sounds like a dumb qq, I am relatively new to BI tools.

Also, my question is that if 2 reports are sourcing 2 similiar data and the only difference between these 2 source datasets is that one is indexed and the other is not, shouldnt there be a visible difference in response time of these reports on WRS irrespective of how the web tier is setup?

Regards,
Tej
Super Contributor
Posts: 356

Re: Registering Indexes on metadata repository

You may also want to review the code generated by WRS, as it may be generated in a way that doesn't use the index, hence the same response times.
SAS Employee
Posts: 238

Re: Registering Indexes on metadata repository

Tej,
There is a button in the menu bar of Information Map Studio that looks like a document icon - it takes you to a test tool to run the query directly from there. This eliminates the web component as a factor in your comparison between the index/non-index source.

If you continue to see the same performance between the two source tables directly in Information Map Studio, this could be an instance where the index is not providing much value. Check on papers such as http://support.sas.com/resources/papers/proceedings09/024-2009.pdf to review index needs/uses.

~ Angela
http://sas-bi.blogspot.com
Contributor
Posts: 59

Re: Registering Indexes on metadata repository

Thanks so much Anjela.
I tried testing the queries on the Information Map and again didnt see any visible difference in response time. I was hoping to look at the queries and try and figure out if the query was using the index. The source is indexed on 4 attributes and Year is one of them.Also since I created a simple index, the index itself will not have a name. Below are the queries

Query from Index Data Source

Proc SQL; Create Table %DATA% as
SELECT
SUM(table0.ACCTS) AS DIR_30 LABEL='Accts',
table0.CUSTOM_CHANNEL AS DIR_31 LABEL='Custom Channel' FORMAT=$20.,
table0.CLIENT_ID AS DIR_37 LABEL='Client Id' FORMAT=$20.,
table0.PRODUCT_TYPE AS DIR_38 LABEL='Product Type' FORMAT=$4.
FROM
ppaldata.ACQ_TEST_SAMPLE2_TEST1 table0
WHERE
table0.YEAR = '2009'
GROUP BY
2,
3,
4
;
quit;

Query from Non Index Data Source

Proc SQL; Create Table %DATA% as
SELECT
SUM(table0.ACCTS) AS DIR_2 LABEL='Accts',
table0.CUSTOM_CHANNEL AS DIR_3 LABEL='Custom Channel' FORMAT=$20.,
table0.CLIENT_ID AS DIR_5 LABEL='Client Id' FORMAT=$20.,
table0.PRODUCT_TYPE AS DIR_6 LABEL='Product Type' FORMAT=$4.
FROM
ppaldata.ACQ_TEST_SAMPLE2 table0
WHERE
table0.YEAR = '2009'
GROUP BY
2,
3,
4
;
quit;

Is there a way I can be sure that the query used the index?Meanwhile I will also go through the paper.
Again, thanks for the all the help.

Tej
SAS Super FREQ
Posts: 464

Re: Registering Indexes on metadata repository

You might be interested in this paper http://www2.sas.com/proceedings/sugi30/008-30.pdf

Note that it says the following:
"The primary use of an index is to optimize a WHERE expression."
Ask a Question
Discussion stats
  • 7 replies
  • 326 views
  • 0 likes
  • 4 in conversation