topic Multiple dates in one facttable(for simplicity to begin with) in QlikView

Multiple dates in one facttable(for simplicity to begin with)

Anonymous — Sun, 11 May 2014 16:28:17 GMT

Hi,

I'm relatively new to Qlikview but I am experienced in creating Datawarehouses and OLAP solutions. So I guess that my mind is in someway stuck in that kind of datamodeling. I the more I play with Qlikview the more I see that datamodeling is done in a slightly different way then traditional BI solutions are modeled.

So I have one facttable in this demo that has 3 different dates. So I created three different timedimensions for each Timekey, based on this article http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date.

But now I want to create one TimeDimension that will connect to the facttable and remove the other connections, A link table I guess. But for the life of me I cannot get this done. So how would I create this linktable based on my data? I will also add new facttables to the model and that will create more timedimenions.

So any hints are appreciated. Here is a snapshot of the model as well as the loadscript.

Regards

Birkir, and thank you for a great communitiy.

P.S. The facttable is a daily snapshot table.

[ProductSubscribersFact]:

LOAD CustomerDimDW_Id,

ProductDimDW_Id,

age,

LoadDateDimDW_Id as LoadDateTimeDimDW_Id,

NetworkDimDW_Id,

GildirFraTimeDimDW_Id,

Fjoldi,

SubscriptionTypeDimDW_Id,

RetthafiCustomerDimDW_Id,

GildirTilTimeDimDW_Id

FROM

$(vcQvdPath)ProductSubscribersFact.qvd

(qvd);

[LoadTimeDim]:

LOAD //TimeDimPK as LoadTimeDimPK,

LoadDateTimeDimDW_Id,

TheDate as LoadTheDate,

YearName as LoadYearName,

MonthNameLong as LoadMonthNameLong

FROM

$(vcQvdPath)vw_TimeDim.qvd

(qvd);

[GildirFraTimeDim]:

LOAD //TimeDimPK as GildirFraTimeDimPK,

LoadDateTimeDimDW_Id as GildirFraTimeDimDW_Id,

TheDate as GildirFraTheDate,

YearName as GildirFraYearName,

MonthNameLong as GildirFraMonthNameLong

FROM

$(vcQvdPath)vw_TimeDim.qvd

(qvd);

[GildirTilTimeDim]:

LOAD //TimeDimPK as GildirFraTimeDimPK,

LoadDateTimeDimDW_Id as GildirTilTimeDimDW_Id,

TheDate as GildirTilTheDate,

YearName as GildirTilYearName,

MonthNameLong as GildirTilMonthNameLong

FROM

$(vcQvdPath)vw_TimeDim.qvd

(qvd);

Re: Multiple dates in one facttable(for simplicity to begin with)

swuehl — Sun, 11 May 2014 20:56:55 GMT

Birkir,

you essentially want to create the bridge table from Henric's blog post, right?

What have you tried so far?

Here is another thread with a very similar topic and a link to a tutorial created by Rob Wunderlich:

Tutorial - Using Common Date Dimensions and Shared Calendars

Re: Multiple dates in one facttable(for simplicity to begin with)

Anonymous — Sun, 11 May 2014 22:29:49 GMT

Hi Swuehl,

Thanks for your reply. And yes that is just what I am trying to do create a bridge table like Henric's explained. But somehow I am lost in this and I am struggling to figure out or understand what should be included in my bridge table.

I looked at the tutorial and I think it might have solved it. Thank you for your help.

Next is try to add a fact table and keep the data model correct.

Thanks again.

Re: Multiple dates in one facttable(for simplicity to begin with)

Anonymous — Mon, 12 May 2014 13:15:22 GMT

Just one more comment, I have created a bridge or a link table as the tutorial showed. But the fact is loaded once as a facttable and then 1x per datetype in the link, is that correct? Is it just my old background of datamodeling troubling me is this acceptable behaviour in the qlikview world?

This is my script

DateLink:
LOAD
ProductSubscription_Id
,LoadDateTimeDimDW_Id as TimeDimPK
,'LoadDate' as DateType
RESIDENT ProductSubscribersFact
;
LOAD
ProductSubscription_Id
,GildirFraTimeDimDW_Id as TimeDimPK
,'GildirFra' as DateType
RESIDENT ProductSubscribersFact
;
LOAD
ProductSubscription_Id
,GildirTilTimeDimDW_Id as TimeDimPK
,'GildirTil' as DateType
RESIDENT ProductSubscribersFact
;