topic Re: Data Modelling in QlikView

Data Modelling

deepakqlikview_123 — Thu, 26 Feb 2015 06:23:18 GMT

Hello All,

Please find attached data model from etl side.

"I have four fact tables

1 Row_indicator_by_country_F

2 Row_indicator_by_Location_F

3 Row_indicator_by_Region_F

4 Row_indicator_by_Sector_F

Can you please suggest how shall i go ahead for performing data modelling in qlikview.

Thanks

Re: Data Modelling

stabben23 — Thu, 26 Feb 2015 06:29:00 GMT

Hi deepak,

You should concatenate your 4 fact into 1, and keep your dimension tables.

Re: Data Modelling

deepakqlikview_123 — Thu, 26 Feb 2015 06:38:46 GMT

Thanks staffman,

shall i use link table as

1 Row_indicator_by_country_F

2 Row_indicator_by_Location_F

3 Row_indicator_by_Region_F

4 Row_indicator_by_Sector_F

contains some of comman fileds.

if so can u suggest what would be steps.

Thanks

Re: Data Modelling

stabben23 — Thu, 26 Feb 2015 06:47:28 GMT

Your 4 fact has many common field and keys "out" to the dimensions, ex Sector_id. Just Merge (concatenate) all fact into 1 big fact table and use the normal keys to assosiate to the dimensions. I also think that your date table has the same keys in all fact and will be assosiated when you load this into your qlikview application.

Re: Data Modelling

Thu, 26 Feb 2015 06:51:22 GMT

Hi Deepak,

How big is your data? If small, then you can use link table. I like to use Link table because its easier for me to check/maintenance. But if your data is big (more than 5 GB), then I suggest you to use Concatenate method.

Please refer to this link for further link table explanation :

Link Table in QlikView | Learn QlikView

Regards,
Iwan

Re: Data Modelling

jagan — Thu, 26 Feb 2015 06:54:32 GMT

Hi,

I think Link table would be best option in this scenario.

Regards,

Jagan.

Re: Data Modelling

Thu, 26 Feb 2015 07:02:27 GMT

Something like below:

OneFact:

LOAD *,

'ByCountry' as Flag

Resident

Row_indicator_by_country_F';

Concatenate

LOAD *,

'ByLocation' as Flag

Resident

Row_indicator_by_Location_F';

Concatenate

LOAD *,

'ByRegion' as Flag

Resident

Row_indicator_by_Region_F';

Concatenate

LOAD *,

'BySector' as Flag

Resident

Row_indicator_by_Sector_F';

CalendarLink:

LOAD DISTINCT

As_of_date_id as MasterDateID

Resident OneFact

where Flag = 'ByCountry';

Concatenate

LOAD DISTINCT

As_of_date_id as MasterDateID

Resident OneFact

where Flag = 'ByLocation';

Concatenate

LOAD DISTINCT

As_of_date_id as MasterDateID

Resident OneFact

where Flag = 'ByRegion';

Concatenate

LOAD DISTINCT

As_of_date_id as MasterDateID

Resident OneFact

where Flag = 'BySector';

And then create master calendar based on MasterDateID.

Hope this answered or was helpful.

Thanks,

Singh

Re: Data Modelling

deepakqlikview_123 — Thu, 26 Feb 2015 07:35:47 GMT

Dear All,

Thanks for response.

I have cretated the link table as shown in attached qvw file

Please suggest.

Thanks

Re: Data Modelling

Thu, 26 Feb 2015 07:46:32 GMT

You are still having number of tables, you must concatenate them to one fact table.

Re: Data Modelling

stabben23 — Thu, 26 Feb 2015 08:15:14 GMT

My oppinion here is that Link table is not needed. It just complicate the solution. Just concatenate the fact in your main sheet in script.

Fact:
LOAD
[Indicator_Name_Varchar(50)]&'|'&[Indicator_Value_Dacimal(7,2)]&'|'&[As_Of_Date_ID(Int)]&'|'& [As_Of_Date_Varchar(45)] as [%Key_Field],

[Indicator_Name_Varchar(50)],
     [Indicator_Value_Dacimal(7,2)],
     [Country_id(Int)] as [Country_ID(INT)],
     [As_Of_Date_ID(Int)],
     [As_Of_Date_Varchar(45)],
     [Country_Varchar(45)]
FROM

(ooxml, embedded labels, table is Row_Indicator_By_Country_F);

Concatenate (Fact)

LOAD

[Indicator_Name_Varchar(50)]&'|'&[Indicator_Value_Dacimal(7,2)]&'|'&[As_Of_Date_ID(Int)]&'|'& [As_Of_Date_Varchar(45)] as [%Key_Field],
[Indicator_Name_Varchar(50)],
     [Indicator_Value_Dacimal(7,2)],
     [As_Of_Date_ID(Int)],
     [As_Of_Date_Varchar(45)],
     [Location_Varchar(45)],
     [Location_id(INT)] as [Location_ID(INT)]
FROM

(ooxml, embedded labels, table is Row_Indicator_By_Location_F);

Concatenate (Fact)
LOAD
[Indicator_Name_Varchar(50)]&'|'&[Indicator_Value_Dacimal(7,2)]&'|'&[As_Of_Date_ID(Int)]&'|'& [As_Of_Date_Varchar(45)] as [%Key_Field],
[Indicator_Name_Varchar(50)],
     [Indicator_Value_Dacimal(7,2)],
     [As_Of_Date_ID(Int)],
     [As_Of_Date_Varchar(45)],
     [Region_Varchar(45)],
     [Region_id(INT)] as [Region_ID(INT)]
FROM

(ooxml, embedded labels, table is Row_Indicator_By_Region_F);

Concatenate (Fact)
LOAD
[Indicator_Name_Varchar(50)]&'|'&[Indicator_Value_Dacimal(7,2)]&'|'&[As_Of_Date_ID(Int)]&'|'& [As_Of_Date_Varchar(45)] as [%Key_Field],
[Indicator_Name_Varchar(50)],
     [Indicator_Value_Dacimal(7,2)],
     [As_Of_Date_ID(Int)],
     [As_Of_Date_Varchar(45)],
     [Sector_Varchar(45)],
     [Sector_id(INT)] as [Sector_ID(INT)]
FROM

(ooxml, embedded labels, table is Row_Indicator_By_Sector_F);

Re: Data Modelling

deepakqlikview_123 — Wed, 29 Apr 2015 12:50:43 GMT

Hello All,

Is it need to keep calendar separate if so can you please suggest in which scenario's we need to keep calendar separate and in which scenario there is no need to do so.

Thanks

Re: Data Modelling

jagan — Wed, 29 Apr 2015 14:20:56 GMT

Hi Deepak,

It is not mandatory to keep the Calendar as a separate table. Suppose for example if your data is not having transactions for some dates assume that Mar month do not have transactions, but if you want to show the March month in the Month field then you need to go for Master Calendar as a separate table. In Master Calendar you have all the dates from Start to End date even if you have transactions or not.

Hope this helps you.

Regards,

Jagan.

Re: Data Modelling

deepakqlikview_123 — Wed, 29 Apr 2015 16:04:04 GMT

thanks jagan for valuable input.