5th Case

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:

1. 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. 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.)