Hello. I have been struggling trying to come up with the best solution for a problem that I have encountered.
I have 2 fact tables that represent Sales data from two separate systems. For explanation purposes, lets say i have Table A and Table B, where Table A has 100Million records and Table B has 50 Million records. Each table has about 100 columns representing various things such as customer, plant, date, invoice number...etc. The goal is to pull them into qlikview and build reports around the data.
The issue I am struggling with is that they represent the same "type" of data (ie Sales), but do not contain any fields in common by name. Although, they DO contain the same type of information, like customer, invoice date, dollar amount...etc (just with different column name)
My idea is to rename the fields that align (ex: each table has customer, but they are named different so I would just name that field to Customer in each of the tables) and then concatenate the two fact tables, creating a gigantic 100+Million row fact table with all columns from each of the tables. I think this is ok, but the other issue that arose with doing this is that each of the two tables A and B have their own dimension tables (for example, customer info table, plant info table, description tables...etc). My data model will look like 1 fact table with tons of dimension tables linked to it, which I thought was a bad practice.
Am I anywhere close to the right path?
Any help and reccomendations would be GREATLY appreciated. Thanks!
.png "Luminary Alumni"){kind=icon}