Cognos Analytics

 View Only
  • 1.  Optimising data to achieve faster loading on Dashboards

    Posted Mon June 28, 2021 11:52 AM
      |   view attached

    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:

    1. Short background of our business and processes
    2. Building test cases
    3. 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

    Attachment(s)



  • 2.  RE: Optimising data to achieve faster loading on Dashboards

    IBM Champion
    Posted Tue June 29, 2021 06:17 AM

    Dear Csaba, thank you for your evaluation of the performance of dashboards.
    Do you plan to check out R11.2 as well? We have experienced a significant speed-up with the latest release as the dashboard view does not load all scripts from the server during the first opening step, but when they will be needed. This improved the performance a lot and has nothing to do with the underlying database.

    I'm curious to hear from you if the performance with you  fact table and the aggregate tables is much better with R11.2 also.
    Br



    ------------------------------
    Robert Dostal
    Team Leader BI
    GEMÜ
    Ingelfingen
    ------------------------------



  • 3.  RE: Optimising data to achieve faster loading on Dashboards

    Posted Wed June 30, 2021 07:09 AM
    Hello Robert,

    Yes, I'm actually testing a new model in 11.2 . I've created a new star schema model and doing some testing with a 35 million row data set. I want to get to the edge of Cognos capabilities.

    So far my experiences are that the count distinct functions have a hard time to run in summary visuals. Interestingly I've received an error showing: Duplicate request ID 'da3f6b20-c2c6-4499-af83-c67190bb27ae' within QueryContext ID 'd372c9f3-2940-43e8-bf6a-7abdcbdeaa25'.

    Unfortunately there are no error descriptions that I could found on the internet, so I've contacted the Cognos support for more details.

    So far my findings are related to DB issues, as the request takes too long and results in time out error.

    However, on one of our new client with 28m of data, which using the original fact table structure (all data in one fact table) loads pretty fast in 11.2. I see no correlation between the two examples as the client data with 28m of data the invoice id column has the same characteristic as the one with 35m rows (data type is varchar and length of the column is 120). With the client data the same calculation loads without error, and fast and both of the tables are in the same DB organised by column, so the columns are indexed. 

    I'll open a new post about my findings once it is done.

    Regards,
    Csaba


    ------------------------------
    Csaba Mihaly Lorant
    ------------------------------



  • 4.  RE: Optimising data to achieve faster loading on Dashboards

    Posted Tue June 29, 2021 06:28 AM
    Hi, Using data modules alone to aggregate data won't help much because the result set isn't saved. Make sure that the aggregated data is stored in the database directly or by using data sets. Both can be processed/enriched in data modules afterwards. This kind of persistant aggregation can speed up performance of dashboards a lot.

    ------------------------------
    Philipp Hornung
    ------------------------------