Cube Build Times 1

[Rolldice]

Hello all,

This is a best practice question. Is it better to have dimensions coming into transformer as single data sources i.e 3 dimensions - 3 datasources plus the fact table, which then combine during build time. Or is it better to have a single data source coming from a star schema, with the relationships between the dimensions and fact table modeled in Framework manager.

I thought the second way works better in terms of cube build time. But I have been told that best practice is to bring each dimension in individually for a quicker read and build time???

So what is the best way for cube build time and best practice?




----------------------------------------
Another throw closer to a win ... ???
----------------------------------------

 

[billnted]

At the risk of being labelled glib (and I'm taking that risk ), why not try it both ways?

Can depend on no. of levels in a dimension - if your unique id is down low, it can take a bit more horsepower to build the structure and relationships with a multi-sourced model.

 

[Rolldice]

Thanks - I'm going to test both : )

But I was just wondering what best practice is.. I'm stuck in a political debate. I'll let you know the results when i run both models with a large fact table. Going from experience I found that more data sources took longer and created a larger cube size.. Will keep you posted.

Cheers

----------------------------------------
Another throw closer to a win ... ???
----------------------------------------

 

[moonbaby870]

Hi Rolldice,

In my experience, separate data sources per dimension resulted in quicker builds but bigger cubes since orphans are created if data exists in one source but not the other. Also, maintaining "relationships" in Transformer is tricky - things can get out of synch between the FM model and the cube. And don't forget about drill though - I've found that simple drill throughs aren't that simple when you build structurally/transactionally...

HTH

 

[Rolldice]

Thanks Moonbaby and Billnted!

Billnted I took your advice and built two models one with multiple data sources and the other with one data source.

The build times were surprising.

- Multiple data sources took 1 hour 20 minutes to build
- Single data source model took 1 hour.

I expected the multi DS model to come in before the single (The Cognos documentation leads one to believe this is ‘best practice’ thus faster performance). I was quoted Cognos documentation on this.

I have been told that auto partitioning could be a factor?? Does anyone what else might course this?

The cube sizes are:
Multi DS: 112mb
Single DS: 67mb

Thanks again.


----------------------------------------
Another throw closer to a win ... ???
----------------------------------------

 

[drlex]

Rolldice,
What version are you using? I recently upgraded from 7.3MR2 to 7.4 and have enjoyed a phenominal improvement in build times - that is in cube processing (Metadata), not data read. One model went from 3 hours to 1 hour. I'm unaware of how much of this improvement is to do with a new version of ODI and how much is changes in transformer. The Network manager assures me that he has made no changes to AV scanning that might conceivably affect temp file access and hence build times.

Happy Friday,
lex

soi la, soi carré

 

[Rolldice]

Thanks drlex,

we're on 7.3 - I'll push for the upgrade and give you a star if we get those performance improvements!!

Cheers

----------------------------------------
Another throw closer to a win ... ???
----------------------------------------

 

[Rolldice]

Hello,

To close this thread I wanted to say thanks - We weren't able to change the versions of transformer for various reasons.

However one point of interest to anyone following this is that the use of multiple datasources allows a complete picture of the entire dimensions data (Even if it doesn't appear in the fact table).

When we used a single iqd over the star schema data from any dimension that wasn't in the fact table, didn't go into the cube.

This means that the single iqd was pulling a smaller data set of info and that the dimensions in the cube would therefore have holes.

Thanks to all.

----------------------------------------
Another throw closer to a win ... ???
----------------------------------------