How to structure data coming from several similar data sources?

[seniorps]

I need to take ERP GL transactional data from 4 different companies. I need to merge it but I also need to be able to identify which company each row came from. There is nothing in the data itself to identify this. The data will be coming from different SQL database names but other than that it is identical in structure. I've done several visualistions from single databases and added data from other sources, but I've never combined 4 like this.

Any ideas how I should approach this? A simple company identifier against each line? If so, what is the best way to attach this. There are several empty columns in the data that could be utilised if necessary.

Thanks!

[Anonymous]

Yup, as you suggest, add a new field called say 'Company ID', populate it and concatenate your 4 tables together.

[seniorps]

Thanks Bill. How would you suggest I populate each line with the Company ID?

[Anonymous]

Assuming you hold the Company ID in the databases. load it into its own table with 1 row and 1 column, outer join it onto your fact table to give a cartesian product.  Then concatenate the resulting tables.

[seniorps]

No the company ID doesn't exist in the table. Ideally I would like to just populate one of the empty columns (there is one called Source!) with my own company ID. I there a transform function that could do this?

[Anonymous]

Could you use the database name, you should be able to extract that from the database whether variety of SQL you are using.