Multiple use cases for Aggregate Aware / real time data

[former_member226964]

Hello,

This is partly a question but partly a description of how I have implemented Aggregate Aware in our UNX-universes to make use of aggregated tables but also a total "table switch" to enable more real-time data. Could be useful to others.

We have a big datawarehouse with large fact tables. We normally update our datawarehouse every night but our users are now requesting more online data. I have therefore built a smaller batch (SSIS+SQL Server) which is loading data continously (facts are updated every 5-10mins).

The problem I encountered is that our fact tables are very large and users can do complex queries running for up to an hour. This makes it impossible to load (insert/update/delete) every 5 mins since there would be a table lock in the database if a user where running a long query.

Our solution was to have duplicates of every fact-table where the duplicates only contains todays and yesterdays data. So the smaller tables get loaded continously during the day and the larger ones with full history gets loaded every night. Using such small tables makes the problem of table locks very unlikely since all the queries are fast to execute.

To make use of the smaller tables with (basically) online data I have connected them all in the Data Foundation Layer. Both the tables with full history and the smaller ones. Then I've created a dummy dimension object called "online" which is incompatible (through aggregation navigation) with the original large fact-tables. Then I use @Aggregation_Aware on all measures so that if the "online"-dimension is used then aggregation aware changes the query to select data from the smaller tables instead of the large ones.

By doing this we have both "online" data and many years of history in the same universe. Though they can't be combined in the same query.

You could make a separate universe for online data but I think this is a cleaner solution.

This became a very long description but hopefully someone

Have anyone done something like this?

Is there any flaws to using Aggregate Aware in this way?

Is there a better solution? 🙂 (other then using separate universes)

Thanks

Jonathan

[denis_konovalov]

I have fixed your tag to better align with the content you posted.
I also think that this should be a blog post and an invitation to a discussion. I'm afraid it will get lost as a question....

[former_member226964]

Thanks. Yes, my post is more of a discussion thread. I was looking for a discussion forum but only found the Q&A section.

Is a blog post the only alternative?

[wobi · Accepted solution]

Well for me the described idea looks pretty good and is a valid usage of the Aggregate Aware Feature.
I have no better solution for you!

- Wobi