I have a report on an MDA table where I am looking to summarize data by quarter and pivot on that column
So these columns:
Product name
Client
Q1 2015 (date column pivoted and summarized by quarter to display Sum of Usage)
Q2 2015 (ditto)
Q3 2015 (ditto)
Q4 2015 (ditto)
Q1 2016 (ditto)
Q2 2016 (ditto)
The source table has millions of records in it as it stores daily usage records for each Product against each Client for each individual user
However when pivoted the table is incomplete because there is a limit on the number of records retrieved before it is summarised in the table.
This is then misleading as it shows that Client X's usage of Product Y stopped in Q3 2015 - not because there is no usage but because records after Q3 2015 were not retrieved in the first place
If I output to Excel/CSV I do get Usage for Q4-2015 onwards but would rather not have to train users to "always export"
I could potentially create new MDA objects where the base data is preaggregated to reduce the record count but there are maintenance complications around that.
I understand potential performance concerns but are there any plans to increase/remove the record retrieval limit ?
Thanks
Released
Reports 2.0 Record retrieval limits compromising pivots
+2Ian, I am kicking this over into an idea so product has visibility into this request.
We are working on various infrastructure improvements to improve performance to get to millions of records. The fact that you are requesting this on MDA data helpful. We continue to expand this number, but to it's unlikely we can extend limit to millions over the next couple of quarters. What is the current limit that you have set for MDA?
Hi Gaurav
Thanks for reaching out.
On our org we have 2 large MDA tables
Usage Data Details - giving total usage by user by day since Jan 1 2014 with 2.9mn records
System Usage by Search Type - breaking that user usage down by "System" or Product for the same time period which has 9.1 mn records in it
Its broken down like this to give the granular reporting where needed but also so we can easily aggregate and report by company, week, month, quarter etc
I could perform an archive of 2014 data, and/or create pre-aggregated tables to the summary reporting levels but aside from the maintenance overhead there would still be over whatever your current limits are I think of 100k or 200k for retrieval
Two other things to be aware of are:
Best regards
Ian
Thanks for reaching out.
On our org we have 2 large MDA tables
Usage Data Details - giving total usage by user by day since Jan 1 2014 with 2.9mn records
System Usage by Search Type - breaking that user usage down by "System" or Product for the same time period which has 9.1 mn records in it
Its broken down like this to give the granular reporting where needed but also so we can easily aggregate and report by company, week, month, quarter etc
I could perform an archive of 2014 data, and/or create pre-aggregated tables to the summary reporting levels but aside from the maintenance overhead there would still be over whatever your current limits are I think of 100k or 200k for retrieval
Two other things to be aware of are:
- We have look ups set up on these tables as well for normalisation purposes to other mda objects so when you look at the record count in Data Management for these 2 objects it actually reports 100 mn and 96 mn records respectively - I don't know if lookups has an adverse impact too ?
- In the future we want to push even more granular usage here too - ie user may have looked at system X but within that what did they look at, at say an article level
Best regards
Ian
Hey Ian, I do understand where your problem lies when it comes to huge amounts of data. We are surely working on the infrastructure and tech stack improvements which are non-trivial. However, I would definitely like to jump on a call with you to discuss you specific use-case in more detail so we do not miss it when we are levelling up to next level of data processing. Do let me know any time which works for you and I will send an email with the invite.
Hi Sudarshan
Coming back to this rather belatedly but do you have any availability next week Wed or Thurs 9-11 BST ? I'm in the UK
Ian
Coming back to this rather belatedly but do you have any availability next week Wed or Thurs 9-11 BST ? I'm in the UK
Ian
Hey Ian, Please set up the call on any of the days (assuming you meant 9-11 AM BST 🙂 ). I will also include a couple of guys from my side who can put light on this architecturally. Thanks!
+1Hi All, Pivot enhancement in Reports.
Previously, pivot on reports used to happen on the UI (on the already presented data). But, with this enhancement, Admins can now pivot reports on the entire dataset from the server-side.
Note: The maximum number of pivoted columns visible in a report are 53. In CSV exports, you can see the entire dataset without limits.
Please refer the release notes for more information.
Thanks for posting!
Previously, pivot on reports used to happen on the UI (on the already presented data). But, with this enhancement, Admins can now pivot reports on the entire dataset from the server-side.
Note: The maximum number of pivoted columns visible in a report are 53. In CSV exports, you can see the entire dataset without limits.
Please refer the release notes for more information.
Thanks for posting!
Reply
Sign up
If you ever had a profile with us, there's no need to create another one.
Don't worry if your email address has since changed, or you can't remember your login, just let us know at community@gainsight.com and we'll help you get started from where you left.
Else, please continue with the registration below.
Welcome to the Gainsight Community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.