This post is referring to the “Combine DW and ERP in SSAS cube” post, where I described the 4 possible cases.
I recently encountered another case of combinging ERP data with DW data in the cube: A dim built from DW data is supplemented with a referenced dim built from ERP. There 2 variants of this case:
- 2 NQs on DSV (1 points to ERP, 1 points to DW), 2 DB dims, and 2 cube dims. On the dim relationship, dim1 has regular relationship to MD but dim2 is a referenced dim via dim1.
- 2 NQs on DSV like above, but only 1 DB dim. When creating the DB dim we use both NQs, linked on the NK.
Apologies for the abbreviations, here they are:
- DW: Data Warehouse
- ERP: Enterprise Resource Planning such as SAP
- NQ: Named Query
- DSV: Data Source View
- DB Dim: Database dimension*
- Cube Dim: Cube dimension*
- NK: Natural Key
*Chapter 6 of Gorbach, Melomed & Berger is an excellent resource of understanding SSAS objects, especially the not-so-well-known-but-very-useful concepts such as Dimension Cube. (Hmmm makes me want to blog it: Dimension Cube. Watch this space.)