Drill down pivot table Structure Question

[#1]

Hi,

A quick question about speed & drill down tables.

I have a drill down pivot table.

Department -> Category -> Sub Category -> Product

There are set analysis calculations on each level. Due to the fact that I have a relational database background I have kept these tables separate in QlikView, simpler for me to understand is the only reason, since I am currently looking at 50M records and increasing every day would I get a speed improvement holding all this information on one table rather than 4 ?

Thanks,

Gavin.

[#2]

Sorry got my tables confused, it is a straight table with a drill down !!!

[tabletuner]

Hi Gavin,

Which tables do you have right now in your data model? What is the fact table in your case?

I think using multiple tables ina relational structure is good development practise. Maybe it is more important to edit your set analysis query. For example, you could do some calculations in the background. Maybe the forum can give you some advices if you share the query.

Good luck.

Tjeerd

[#4]

Thanks for your quick reply,

I have a table for each one, none of which is my fact table, the fact table consists of individual transactions. Would it actually be better to add this information to the fact table ?

[tabletuner]

Hi Gavin,

Honestly, i haven't tried this yet.

From a storage point of view it is better to keep the relationships because the fact table is generally the largest table. However, if you have enough disk space and enough preparation time for the jobs, you could try to join the dimension data on the fact table. I am very curious about the result from a performance point of view. Will you please share your result on this forum so we can all learn from it?

Good luck!