Setting up the environment
As explained in the introduction, the proper way of implementing metadata-driven calculations in the cube would be to store the metadata information in the MDM system. For explaining the concept, we will simplify things and use a common Excel file, which should be available to everyone. The data in that Excel file needs to be loaded into SQL Server first, it can't go straight to the cube. Therefore, we need to set up the environment that enables the data manipulation and loading process.
Getting ready
Data manipulation will be done in Excel, so make sure you have it installed on your dev machine, where you're learning and testing all this and make sure it is a 64-bit version or you might have problems with the driver later. SQL Server, Excel, and the driver must be of the same version, 64-bit.
For the data loading process, we will set up a linked server connection to the Excel file using a special free driver, which needs to be downloaded and installed first. Once...
