Share dimension tables between data warehouses (marketplaces)

Some people often ask a question: do fact tables and dimension tables must be in a database? Why is there such a problem? As we mentioned, if the data warehouse is large enough, it may be split into so-called data mart. Generally, the division is based on the so-called different business modules, such as personnel management, sales management, and financial management.

Some people often ask a question: do fact tables and dimension tables must be in a database? Why is there such a problem? As we mentioned, if the data warehouse is large enough, it may be split into so-called data mart. Generally, the division is based on the so-called different business modules, such as personnel management, sales management, and financial management.

Some people often ask a question: do fact tables and dimension tables must be in a database?

Why is there such a problem? As we mentioned, if the data warehouse is large enough, it may be split into so-called data mart. Generally, it is divided according to the so-called business modules, such as personnel management, sales management, and financial management.

It looks good to do this, but there is another problem: the information between these databases is actually shared, such as employee information. Is it necessary to save one copy of each data mart separately?

I think in most cases, it is not a good design if each data mart is saved separately. As a result, the data is duplicated, and the US server also brings about the problem of simultaneous updates.

How can we design the relationship between fact tables and dimension tables across databases? In fact, as long as you start to do it (not just think about it), you will find it is not that difficult

1. The following figure shows two different data types. For demonstration purpose, I created a dimension table DimEmployees In the HR database, and a fact table FactSales was created in the Sales database.

Obviously, we cannot design relationships for them within the database. But what's the problem? No. A data warehouse (or data mart) only stores data. There are many other ways to ensure data integrity (such as SSIS.

2. When designing the Analysis Service project, we need to add two data sources

3. Create a data source view accordingly.

Many of my friends are surprised to say that in this dialog box, we can only select one data source. Yes, you are not mistaken. In this wizard, you only need to select any data source. What we need to do is create the data source view for the Hong Kong virtual host.

Then, right-click the blank area of the data source view and select "Add/delete table ..."

You can select different data sources.

4. Create a relationship between the two tables. The final result is as follows:

5. Create Cube, Design Dimension, deploy, process, and browse

The final result is as follows: