flat table structur to star schema

[#1]

Hello,

I wanna know what the best practices is to make a star schema in qlikview when your start point is one big flat table?

Eg I have this table loaded into qlikview:

ENTRYID,CreateDate,ShopID,ShopName,CustomerID,CustomerName,Amount,Taxes

When is the best practices to split this up in Dimensions and facts?

Lets say i want to create a ShopDimension And a CustomerDimension and a DateDimension

Would i then resident load from the big table and just make a surrogate key? Or what do you mean is the best?

[#2]

Master calendars are great ideas and there is  alot of guidance on the community.

Also check out the autonumber() functions when you are creating keys for this as this is a compact way to create a key in QV

[#3]

Yes - The calendar part i got covered

Ill sure have to look into how i do lookups and how i get the key in my "bigfacttable" instead of the real value.

maybe a load of the big table

distinct some values into a dimension

make lookup from dimension and big table and create a new "big table" and then drop the first 1 so that the new big table only consists of maybe values and keys.

[Peter_Cammaert]

Wow...

To make sure that your end-users still say hello to you in the morning, consider choosing from the following:

• Aggregate whatever can be aggregated, and drop the historical records that nobody needs, or
• Take an opportunistic view to the data model, and use whatever gives you acceptable performance. For instance, do a comparison by testing a single table-model against a multiple table model with say 10 mio rows.

Indeed, a standard Master Calendar can be built and coupled to the transaction table by loading Floor([CreateDate]) AS CreateDate. If the design doesn't need a day level calendar, reduce granularity to YearMonth or YearQuarter.

Peter

[#5]

Haha - We have discussed level of aggregation and sadly we need every thing on transactionlevel.

But good idea to try and split the data into large segments to see whether it would perform better. Maybe even split it out to serverel qvw documents? and then combine it into a qvd load. However i only need to do full load once, the rest can be incremental load.