topic Re: Creating facts and dimensions from multiple files in QlikView

Creating facts and dimensions from multiple files

amijit_hazarika — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have multiple Files with 20-25 fields (both key and dim) in each of the files.

I am working on building the data model now.

As of now, I am thinking to pull key field from all the files store them in different tables say Fact 1, Fact 2 etc and then concatenate to form 1 fact table.

Similarly I am planning to do the same for Dim Tables. Pull all the Dim fields from all the files, store them in different tables say Dim1 , Dim 2 etc and then concatenate and form a single Dim table.

I would like to know from the experts here that how this approach would be and if there is any other way to build the best data model then please advice.

Thanks in Advance.

andre.moreira‌ stalwar1‌

Re: Creating facts and dimensions from multiple files

jonathandienst — Fri, 04 May 2018 08:44:12 GMT

One other option to consider is to simply load and concatenate the files without any transformation. For most analysis, this will work properly, and it certainly simpler

Re: Creating facts and dimensions from multiple files

amijit_hazarika — Fri, 04 May 2018 08:47:49 GMT

Hi Jonathan,

Did you mean to concatenate all the files and form a single table and not 2 different dim and fact tables?

Re: Creating facts and dimensions from multiple files

jyothish8807 — Fri, 04 May 2018 08:53:01 GMT

Hi Amijit,

If your all files are in same format and have same fields, then you can simply concatenate then all together and make one master table.

Br,

Re: Creating facts and dimensions from multiple files

amijit_hazarika — Fri, 04 May 2018 09:27:59 GMT

Hi KC,

All the files are in same format but not fields are same.

Thanks

Re: Creating facts and dimensions from multiple files

vishalarote — Fri, 04 May 2018 10:02:16 GMT

Hope so it will help you.

E.g. Like

Create One fact Table Assign Some Flag to each table as Doctype n Concatenate that tables.

So it will show one fact table one Master Calender as Dimension Table.

and Make All Dates Field Alias as "Date".

Each Date Having Flag so Create Variables in Frontend

Suppose my Date is TRANSDATE so am alias to Date n Assign '1' as Flag or Doctype

Make Variable

set vTRANSDATE=Max({<Doctype={'1'}>}Date) in Frontend and use in expression like

e.g.

sum({<Date={"$(vTRANSDATE)"}>}Sales)

Re: Creating facts and dimensions from multiple files

jyothish8807 — Fri, 04 May 2018 11:03:38 GMT

Hi Aamijit,

In this case if you put some effort and identify the dimension and fact then with in the files , then you can go for a star schema.

Multiple dimension tables (like emp, product etc) mapped to a fact table (Sales)

Br,

Re: Creating facts and dimensions from multiple files

jonathandienst — Fri, 04 May 2018 11:12:07 GMT

>>Did you mean to concatenate all the files and form a single table and not 2 different dim and fact tables?

Yes. It may sound crude, but it should be functional - but it is dependent on the nature of the differences between the files. For example, if one file contains a department ID and name, and another has only the ID, then selecting department name will deselect all the rows from the second file.

Make sue you use explicit concatenation (with the Concatenate keyword) if the fields differ between the files. And load a derived type or source field so you can easily identify records using a set expression.