SSAS Cube with MDX
SSIS package a few months back to retrieve data from a SSAS cube with MDX queries. The data flow was lost somewhere and the package could never finish the data loading. This is what it will look like in the Data Flow task. The OLE DB Source will stay yellow forever.
SSIS package was configured to make the data flow work. I had to find my old SSIS package and open it to refresh my memory. After choosing the OLE DB Provider for Analysis Services 10.0 as the provider for the connection manager, we need to go to the All tab to set Format=Tabular in the Data Link Extended Properties.
Need to set Format=Tabular in the Data Link Extended Properties.
Green color is what you will see once you set Format=Tabular in the Data Link Extended Properties.
SSIS that data type DT_WSTR (nvarchar) will be used instead
Data Conversion transformation to convert the DT_WSTR to DT_STR or DT_NUMERIC accordingly.
SQL – Structured Query Language. Here is the Transact-SQL Reference (Database Engine).
- MDX – Multidimensional Expressions. Here is a short introduction from Wikipedia. This is the TechNet site for Querying Multidimensional Data with MDX, and Multidimensional Expressions (MDX) Reference.
- DAX – Data Analysis Expressions. Here is an even shorter introduction on Wikipedia. This is the TechNet site for Understanding DAX in Tabular Models (SSAS Tabular), and Data Analysis Expressions (DAX) Reference. DAX reference is also available on for Power Pivot.