MDX and Transformer

[ianhawksey]

Hi Guys

I am using 8.3 Framework Mgr, Transformer, Analysis Studio, Report Studio, over Oracle.
I have a database with c10 million rows, essentially date based. I need to fill in the gaps between records, by essentially adding 'dummy' records.
For example, I have records for Week 1 and Week 4. I want to be able to 'add' records for weeks 2 & 3 and calculate/display a cumulative qty from week 1 thru to week 4.
I don't want to do this directly in the database as it adds an overhead of 5 times more records.

My current thinking is to add a calculated measure in Transformer using some kind of MDX functionality.

Is this possible?
Am I thinking along the right lines?
Any suggestions?

Thanks

Ian

 

[blom0344]

We had the same problem about a year ago with 'gaps' in data that prevented us from displaying cumulative figures from a cube. We had substantially less records (about 300.000) for our facts.

Eventually we create a series of database views (including cross joins and set operators ) to get a continuous set and used this in IQD's to feed the cube.

The benefit is, that by creating views no additional database storage is required. Building the cube took some extra time and the cube was evidently larger.
However, only the facts grew, bot the dimensional categories, which meant the growth was not exponential.

I cannot give you specifics here, but I think it is a better approach than fiddling with MDX. Pretty hard to generate data that is not there..

Ties Blom

 

[ianhawksey]

Hi Ties. Thanks for the reply.

If I understand you correctly, when you were building the cube you were dynamically filling in the missing records with a database view.

The problem with my scenario could be the time to do that re the huge number of records and the resulting disk space used while building the cube. But something I can talk with the DBA about.

Cheers

Ian