Creating facts and dimensions from multiple files

[amijit_hazarika]

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‌

[jonathandienst]

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

[amijit_hazarika]

Hi Jonathan,

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

[jyothish8807]

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,

KC

[amijit_hazarika]

Hi KC,

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

Thanks

[vishalarote]

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)

[jyothish8807]

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,

KC

[jonathandienst]

>>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.