Dynamic Cubes vs TM1 for BI reporting

[kimmal]

Wondering if anyone else has compared Dynamic Cubes vs TM1 cubes as a source for BI reporting.  We're in the process of creating a new reporting data mart and were testing Dynamic cubes vs DMR (20M records) and found, as expected that Dynamic cubes were much quicker.  I then decided to load the data into TM1 and test the results and have found that TM1 is significantly faster at returning data in our Analysis Studio reports. 

Has anybody else done a similar comparison?

[MFGF]

Wondering if anyone else has compared Dynamic Cubes vs TM1 cubes as a source for BI reporting.  We're in the process of creating a new reporting data mart and were testing Dynamic cubes vs DMR (20M records) and found, as expected that Dynamic cubes were much quicker.  I then decided to load the data into TM1 and test the results and have found that TM1 is significantly faster at returning data in our Analysis Studio reports. 

Has anybody else done a similar comparison?

Hi,

Both TM1 and Dynamic Cubes utilise in-memory technology, but they are fundamentally different architectures.

TM1 will always load *all* the data in memory, and when you request data from it, it will calculate the intersections on-the-fly. This can be ok for small/medium/large volumes, but is also dependent on having a well-designed cube with appropriate skipcheck and feeder rules (to assist it in calculating only the required intersections and not everything). For very large data volumes, it can struggle, though. TM1 does scale well, but only up to a limit.

Dynamic Cubes work differently. They sit over a data warehouse, and retrieve data from the underlying tables. They can be made aggregate-aware, so that they can use aggregate tables/views in the data warehouse, plus they can utilise in-memory aggregates. There is a learning process for them (using workload logs within Aggregate Advisor) where the most appropriate in-database aggregate tables and in-memory aggregates are determined, based on the structure of the data and how people are using the cubes. Unless you add these, though, the cube is simply sending queries to the underlying database tables initially to retrieve data (so in your case hitting a 20M row fact table). Once data has been retrieved, it is cached, so subsequent access is usually quick, but I imagine you are seeing results from an "untuned" cube here, where there are no in-database and in-memory aggregates? A tuned Dynamic Cube would be just as fast as (if not faster than) TM1 generally. It would also scale to *much* larger data volumes.

Cheers!

MF.

[kimmal]

You are correct in you assumption that these are "untuned".  I will need to figure out how to tune it in order to get a better response.

Thanks

[MFGF]

You are correct in you assumption that these are "untuned".  I will need to figure out how to tune it in order to get a better response.

Thanks

Lots of good pointers in the redbook for this. You need to turn on workload logging, then use the cube in the way you would normally use it (to build up entries in the logs). Go into Dynamic Query Analyzer, and launch Aggregate Advisor and let it analyse the cube structure and the workload logs. It will then recommend some in-database aggregates and in-memory aggregates.

Cheers!

MF.

[cognostechie]

Hi,

Both TM1 and Dynamic Cubes utilise in-memory technology, but they are fundamentally different architectures.

TM1 will always load *all* the data in memory, and when you request data from it, it will calculate the intersections on-the-fly. This can be ok for small/medium/large volumes, but is also dependent on having a well-designed cube with appropriate skipcheck and feeder rules (to assist it in calculating only the required intersections and not everything). For very large data volumes, it can struggle, though. TM1 does scale well, but only up to a limit.

Dynamic Cubes work differently. They sit over a data warehouse, and retrieve data from the underlying tables. They can be made aggregate-aware, so that they can use aggregate tables/views in the data warehouse, plus they can utilise in-memory aggregates. There is a learning process for them (using workload logs within Aggregate Advisor) where the most appropriate in-database aggregate tables and in-memory aggregates are determined, based on the structure of the data and how people are using the cubes. Unless you add these, though, the cube is simply sending queries to the underlying database tables initially to retrieve data (so in your case hitting a 20M row fact table). Once data has been retrieved, it is cached, so subsequent access is usually quick, but I imagine you are seeing results from an "untuned" cube here, where there are no in-database and in-memory aggregates? A tuned Dynamic Cube would be just as fast as (if not faster than) TM1 generally. It would also scale to *much* larger data volumes.

Cheers!

MF.

Hi MFGF -

This is what I have been wondering about for some time. What happens when the data is retrieved first time, let's say when a user runs the report in the morning. At that time, can it take quite some time regardless of how well the cube is tuned because at that time it is retrieving the data from the DB, whether from an aggregated table or a detailed table? At least this has been my experience, both with Dynamic Cubes and a relational package using DQM.
I am wondering if this is how it is supposed to be or I am doing something wrong.

[bus_pass_man]

 The dynamic cube tries first to figure out whether the query can be routed to the data cache.  Then it tries to figure out if it can route the query to an aggregate, and if it can, does so.

What sorts of diagnostics have you performed to analyze what is happening? 

When you look at the workload logs what sorts of things do you notice?  Do you have holes where queries are being unnecessarily routed to the detail fact table rather than aggregate tables or in-memory aggregates?

What sorts of aggregates have you defined?

Have you run priming queries?   You can schedule them so that they have primed the data cache before anyone has run a report.

Have you looked at the query service log?

I would recommend consulting the redbook, especially the Optimization and performance tuning chapter, and the articles that are referenced by it

There's also the hardware sizing aspects.

Hope that helps.

[cognostechie]

I had turned on the database monitor which showed that the query went to the DB when I ran the report in the morning. I have set up one aggregate table but the point here is not the performance but the fact that the queries would go to the DB sometime to get the latest data and bring it for caching. I was thinking the same thing as you said (run a report before anybody else does) but had thought that it was only me thinking that way. Thanks for seconding that idea. The good thing about Dynamic Cube is that it can drill down to the line level which a Powercube may not be able to, not that it will ever be required for analytics because the audience would mostly be senior people but still does make a difference in terms of ability. Sad thing is that there is a dependency to figure out all scenarios which the user will use to drill down , slice and dice so that you can build aggregates. That does not need to be done in a Powercube or TM1. The ability to attach several attributes to every level is also nice.

[MFGF]

I had turned on the database monitor which showed that the query went to the DB when I ran the report in the morning. I have set up one aggregate table but the point here is not the performance but the fact that the queries would go to the DB sometime to get the latest data and bring it for caching. I was thinking the same thing as you said (run a report before anybody else does) but had thought that it was only me thinking that way. Thanks for seconding that idea. The good thing about Dynamic Cube is that it can drill down to the line level which a Powercube may not be able to, not that it will ever be required for analytics because the audience would mostly be senior people but still does make a difference in terms of ability. Sad thing is that there is a dependency to figure out all scenarios which the user will use to drill down , slice and dice so that you can build aggregates. That does not need to be done in a Powercube or TM1. The ability to attach several attributes to every level is also nice.

You don't necessarily need priming queries if you have all of the necessary in-database and in-memory aggregates defined. When you start a cube, it becomes available for use as soon as possible, and before the in-memory aggregates are complete. The process of building these is (of course) hitting the database, but could well be using aggregate tables rather than the underlying fact detail rows. If you try to access the cube before all in-memory aggregates are built, access will probably be slower than "normal".

WRT having to figure out all scenarios for users drilling down/slicing and dicing etc, Dynamic Cubes now support autonomous in-memory aggregates, so can self-tune. This mitigates the necessity to figure out up-front every single user usage scenario.

Cheers!

MF.

[kimmal]

Does the Aggregate Advisor have to be run on the server or can a user machine be used to run the Advisor, if they have DQA installed.  I have it on my local machine and was able to run through all the prompts and pick the cube etc, but it just seems to be spinning without returning anything. 

[MFGF]

Does the Aggregate Advisor have to be run on the server or can a user machine be used to run the Advisor, if they have DQA installed.  I have it on my local machine and was able to run through all the prompts and pick the cube etc, but it just seems to be spinning without returning anything.

I haven't ever tried running it on another machine - not sure. Try it on the server.

MF.

[kimmal]

Yeah, it should be run on the server.  Got it up and running.  It did make some recommendations which I applied and I did see a performance improvement as expected

thanks