Hello all,
In one of our Projects, we are working on quick-upgrade of Cubes from 8.4 to 10.2.2.It came as a mandate from the administration team, to replace the existing IQD sources with FM Packages and we went onto do it. The cube validation along with scope for all the queries has gone very fine after the change.
when we started refreshing the cube, there was a huge fall in performance. Same cube which was taking around 4 hours to build in 8.4 is taking round about 16 hours in C10.2.2. below are more details on the same.
> its a time based partitioned cube on year level
> Fact data set is about 90 million records
> it nearly takes the entire 15 hours to opening the source fact.
> database remains the same in case of both environments and same IQD query is being used in FM to create a query subject.
> below is the log from the recent build
Mon 17 Oct 2016 6:18:52 PM 4 00002B09 Start processing data source 'XXXXXX'.
Mon 17 Oct 2016 6:18:52 PM 4 00002B09 Reading source data.
Mon 17 Oct 2016 6:18:52 PM 4 00002B09 Timing, OPEN DATA SOURCE,00:00:00
Tue 18 Oct 2016 9:13:08 AM 4 00002B09 End processing 88232348 records from data source 'XXXXXX'.
Tue 18 Oct 2016 9:13:08 AM 4 00002B09 Timing, READ DATA SOURCE,14:54:16
I just am trying to find the cause and probably fix it as this is not an acceptable time to refresh. It will be a great help if you can direct me on where to look for for what in this case. Let me know if I missing any necessary information.
Thanks in advance. And looking forward to hear from you guys.
You have not mentioned which database, and what are the specs of the computer you running Transformer on. Also, you can up the reporting level in the log and see timing on each dimension with individual counts. If it is the fact table itself, do you have much temporary local storage where Transformer is running? Is it fast storage? Have you tried using the FM package in Report Studio to see if it is perhaps a modeling issue? Does the DBA notice anything if monitoring the query while it is running?