Hello,
As the subject says I would like to share some of my experiences and would love to hear back from you if you are also interested in optimising data for Cognos dashboards or if you had similar issues how did you solved them.
In this post I'll talk about the following:
- Short background of our business and processes
- Building test cases
- Evaluation the tests
1) Intro
In our team we are delivering analytics for external clients supporting their procurement decisions by creating interactive dashboards using Cognos dashboards. For our processes and delivering the analytics, we are using a Cognos analytics cloud hosted environment where we are building the dashboards and validating the applied business calculations through the dashboards. For dashboards we created several general use case dashboard templates that applies for all clients and helps to find opportunities and additional benefits by analyzing the client data. By this we are able to quickly rebuild the dashboards to a new client. The additional calculations, data manipulation and transformation is done in the DB and we simply extending the main fact table with the calculated columns (fact table lowest level is the invoice number).
When we validated everything on the dashboards we are simply moving the dashboards and the associated data module to a Multi Tenant environment which is purchased by the client, so they can do whatever they want there, allowing them to create their own dashboards.
Our base goal is to keep the dashboards and data module as simple as possible as we also giving the opportunity for our clients to create their own use case dashboards not just delivering our own analytics package with the standard use cases. By this we created 1 fact table containing all available data. However, a few years passed and our client base and client data has grown significantly. We had to start optimizing our data modules as the dashboard load speed has dropped significantly.
2) Building the test cases
To begin the optimization, we've started to build a test case documentation to cover the following:
- what we want to test,
- how we measure
- and what outcome do we expect.
Dashboards
We've collected a few of our most used standard use case dashboards which are called admin tool, spto and spend.
Data Model types
Each of the dashboards are going to use different type of data to test which one is the best.
For data wer've selected several type of aggregation based on the use case lowest level of information and also a base line table which is our current original fact table.
The following table represents the size of the data for each data module that we tested.
We were using mostly data modules, but we also tested Cognos Data Sets as well.
Report_fact = the original size of the table containing all the rows and columns that we currently using
Fact = the sample fact table, we've extracted 1million rows for testing purposes
Aggreg_admin = aggregation based on the use case loaded into a data module
Aggreg_spend = aggregation based on the use case loaded into a data module
Aggreg_spto = aggregation based on the use case loaded into a data module
Ds_auto_spend = automatically aggregated data using the data set summarize data feature and using the spend analytics dashboards columns
Ds_auto_admin = same as above, just using the corresponding admin dashboard columns
Ds_auto_spto = same as above, just using the SPTO use case dashboard columns
Detailed information about the tables:
INFO |
REPORT_FACT |
FACT |
AGGREG_ADMIN |
AGGREG_SPEND |
AGGREG_SPTO |
DS_AUTO_SPEND |
DS_AUTO_ADMIN |
DS_AUTO_SPTO |
row number |
7180043 |
1000000 |
894957 |
672660 |
655824 |
672660 |
831103 |
655815 |
spend |
45388856371.3000 |
6604274599.64 |
6604274599.64 |
6604274599.64 |
6604274599.64 |
6604274599.64 |
6604274599.64 |
6604274599.64 |
columns |
364 |
364 |
63 |
22 |
35 |
22 |
63 |
35 |
Test cases:
- Dashboard overall load speed. From opening the dashboard and waiting for the last visual to load
- Click path. Using the most common click path on our use cases that a user might do.
- Opening a report from the dashboard using the drillthrough function.
Measuring the load time:
- Using firefox built in developer tool for measurement
- Classic wrist watch measurement
- *Later on I found out that there is another option to measure dashboard load speed, which is a built in function. If you press in edit mode the cntrl+. on your keyboard, then you are able to see the load time of each visuals. Sadly we realized it after we completed the tests.
Due to lack of resources, we couldn't test a lot as the process was time consuming, and automation wasn't an option as it would require some additional development.
- Overall, we did 2 rounds of tests for each dashboards using different type of data modules.
- The tests were done in the early mornings before regular work hours, to isolate the db performance from regular usage (peak time).
- The tests were done on Cognos 11.7 dashboards
You can find the detailed test report in the attached excel file.
3) Evaluating the tests
The test results were surprising. As we were testing the original table which contained more than 7 million of unaggregated data performed the best.
In the attached file you can see the average load speed for the original admin, spto and spend dashboards that they performed quite well compared to the aggregated versions from the sample data! This is key, as the original contains 7m of data. The rest of the tests and aggregated dashboards were using the 1m row of data as a sample and base line, and the aggregation was done on top of the sample. The aggregation compared to the 1m data resulted in 12-35% row reduction.
Honestly, my expectation was to have a 5-7 times faster load speed compared to the original dashboards as the aggregated tables were using less than 1m of data.
I'm wondering if you need a much bigger amount of data to see significant changes by using aggregated tables?
Does anyone has done similar data module optimisation, if yes how did it go, what was the size of data? What were the outcomes?
------------------------------
Csaba Mihaly Lorant
------------------------------
#CognosAnalyticswithWatson