Transformer 7.0 "Updating the Powercube metadata" step is taking 3 hrs 1

[JeffECR]

Hello,
One of our cube updates is taking an extraordinarily long time in the "Updating the Powercube metadata" step. It's taking 3 hours for just that step! Prior to this weekend the entire cube update took about 2 3/4 hours, now it takes 5 1/2 hours. As far as I know, nothing changed in the cube model at that time. I tried saving the model as an .mdl and resaving it as a .pyi, which didn't help. We're using Transformer version 7.0.713.
Does anyone know exactly what is done in that step, why it would be taking so long, and what can be done to speed up that step? Thanks for any help you can give me.

 

[billnted]

Sounds like you've got w-a-a-a-a-y too many categories in that cube, espec. if you've got a category count measure too. Care to share what the category counts are on your model? I'm betting you're into the 100Ks.
Fall-back thought - got any Antivirus that's objecting to the disk-write and cacheing in metadata creation?

PS unless you do a clean house or manually strip saved data levels in your dimensions, cycling pyi > mdl > pyi won't remove the accumulated cruft.

 

[JeffECR]

There are 16 dimensions and 70 data sources in the model. The counts are 4818, 16, 14942, 27762, 31, 80, 54, 15858, 128, 2063, 2079, 4, 188, 12329, 29558, and 981. As you can seem none are in the 100ks.

 

[drlex]

As you say, none of the counts are 100K+ and while 16 dimensions is a bit high, 70 data sources is going it a bit! (Multiple cubes and combined data sources could be a way forward)
Prompts a few Qs:
-What's the split between structural sources and transactional data sources?
-What's the record count and are there any category count measures?
-How about calculated measures and categories?


PS You're not building any other cubes at the same time, are you? ObjectStore seems to grind to a halt if it's having to cope with 2 models.

soi la, soi carré

 

[JeffECR]

Only 3 of the data sources are structural; the rest are transactional. However, 9 of the 16 dimensions are one-level dimensions. Not sure what you mean by record count, the number of records in the data sources? No category count measures and only 2 calculated measures (out of 28). Cube size is 91,136kb. We don't update more than 1 cube at a time.

 

[drlex]

I was thinking of record count as the total number of records in the data sources, but given no category counts, that's probably of little impact.

I now recall that metadata update times dropped impressively when I went from 7.3 to 7.4 (thread401/1277038). I suspect that the improvement was due to changes in ObjectStore - perhaps a simple upgrade of this component alone would suffice? I'll see if I can find a copy of Transformer 7.0 to try.

[I appreciate that upgrading Transformer may be difficult due to licensing or the need to upgrade other Cognos applications (PP client, PPES) in the chain.]

soi la, soi carré

 

[drlex]

Further to my musings above, I had an opportunity to conduct a little comparison between Transformer 7.0 & 7.4MR1 and between ODI 6.0 & 6.2 (objectstore).

The model used has 310K data items, 243K categories, 5 dimensions (1 with drill-down) and 4 measures (2 standard, 1 calculated from the 2 standard and 1 category count on the lowest level of the drill-down dimension = 205K items)

Metadata Times in Minutes:Seconds from log file.
                   |   ODI 6.0     |     ODI 6.2
---------------------------------------------------
Transformer 7.0    |     7:13      |       5:17
---------------------------------------------------
Transformer 7.4MR1 |     0:52      |       0:51

[All builds on a PC with Pentium D 3GHz & 2GB RAM.]
2 runs, average figure shown.

So, whilst it disproves my initial thoughts about ODI upgrade when on 7.4, just installing the current version of ObjectStore could cut build times on 7.0.

happy friday,
lex




soi la, soi carré

 

[JeffECR]

Lex, thanks for the valuable information. I will see what I can do about installing the latest version of ObjectStore.

 

[drlex]

Jeff,
You're welcome - thanks for the star. I hope that in the long run you can upgrade Transformer - the speed gain alone is worth it.
lex

soi la, soi carré

 

[Orren]

Hi,
You haven't mentioned your hardware setup. By having the appropriate hardware you can reduce the build time further.

Which logical disks do you have and where do you have your temp files (Transformer preferences and data files).
Number of physical disk, speed 7.5/10 or 15K rpm.
Disk controller and cache?
Memory in MB.

Cheers Orren

 

[JeffECR]

Windows 2000 ver 5.0
Physical memory: 1,048,052k
System cache: 335,596k
Temp files assigned to a local disk with 6.83G total and 5.76G free space