Infocube Query or ODS Jump Query

[Former Member]

We have the following scenario I'd like your input on:

- ODS with about 5 key fields and about 20 data fields

- The ODS contains daily level data. The users expect to report on weekly level data as well as daily data.

<u>Option 1</u>: Roll the data to the infocube at the weekly level. Create a jump query to ODS if users want to "drill down' to daily data

<u>Option 2:</u> Roll the same daily data to the infocube at the daily level, containing reference to week. Therefore all queries built on the cube will provide weekly and/or daily data.

Concerns:

1. If we load daily data to the cube, data volume is obviously an issue

2. What kind of performance issues (if any) might we experience with Jump Queries off an ODS?

Thanks for your input.

CB

[Former Member · Accepted solution]

Assuming you are loading deltas to the ODS, and the ODS passes the deltas to the cube, and presumably your cube would also have fewer characteristics than your ODS, so I would be surprised if data volume with the cube would be an issue. How many transactions get loaded to the ODS daily, and how many rows are currenly in the ODS?

Using the summarized cube data to then jump to the ODS is a good practice. I think size of the ODS table would influence whether I would do daily reporting from the cube or jump to the ODS for it. If you decide to jump to the ODS for daily data, then you should create a secondary index on the ODS starting with the date, and other query selection criteria, which should keep the ODS query performance acceptable.

[Former Member · Accepted solution]

Thanks for the input on jump queries.

We load about 1.5 million records daily, or about 30 million per month. Current ODS has about 220 million records, so we're dealing with large volume. That is why I'm not sure if querying off the ODS is a good idea.

However, we need to query same data as needed, that is why I was considering pushing all the daily data to infocube, at least to take advantage of the partitioning and star schema dimensions.

So with these data volumes, does that impact your recommendation on using queries on the ODS?

Thanks

CB

[Former Member · Accepted solution]

There is nothing inherently bad about querying a large ODS. What is bad is if those queries result in full table scans rather than using an index to read just the small number of rows that are necessary.

Cubes with the star shema and all the bitmap indices certainly could help - you just have to balance the benefit of this vs the complexity of adding another InfoProvider and related components to your environement. If there were other summarized queries that would be able to utilize aggregates built on this new cube, then that would also factor into the decision.

Also, an option could be to partition the ODS.

I would make the decision based off of whether all the queries that would run against the ODS would be able to use an index or not (assuming you do not have to go and add more than a few secondary indices). That is - is the amount of data that must be read from the ODS sufficiently small that an index would be used.