Data model for a few databases

[#1]

Hi,

We have a few SQL databases, each represents a different sub-company in our organization.

We would like to enable on the fly, a presentation of data (e.g., sales orders) of  2 companies together, or for each company by itself.

Another words, the user selects it.

Dimensions data such as customers, part, etc.. are also different, and  stored in each database.

Would you recommend to create one QVD for each dimension from all companies? 

I would also think that it might be a good idea to enable flexibility in adding more companies in the future. Another words, use a variable in the ODBC connection string.

Any recommendations about achieving it are highly appreciated.

[Gysbert_Wassenaar]

I think you should try to put all customers in one table, all parts in one table etc. For each table add a field to store the source of the data. That way you can do analysis over the entire company, between subcompanies or per individual subcompany using the source field to distinguish between subcompanies.

You'll may want to use a multi-tier qvd layer to generate the necessary tables. See here and here for more information.

[#3]

These are great tips! Thanks!

An issue which might come up, though, is when some dimensions have the same code. For example, items

or customers.

I guess I can create a key with the company and item ID, but I don't think it will resolve the issue.

Do you have any suggestion?

[Gysbert_Wassenaar · Accepted solution]

If the same item ID is used for different items by the subcompanies then you can't use item ID for company wide analysis anyway, so it shouldn't matter. Just don't forget that that's the case. But to prevent accidents it's not a bad idea to create a unique item ID. Same for customer codes and other fields.