Data Modelling

[deepakqlikview_123]

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

[stabben23]

Hi deepak,

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

[deepakqlikview_123]

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

[stabben23]

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.

[#5]

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

[jagan]

Hi,

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

Regards,

Jagan.

[#7]

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

[deepakqlikview_123]

Dear All,

Thanks for response.

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

Please suggest.

Thanks

[#9]

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

[stabben23 · Accepted solution]

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);