Power BI enables us to connect to SQL Server Analysis Services in two ways:
But there are several limitations in working with live connection. In Power BI, when using Live connection, we cannot modify any field names, or data formats. Also we cannot add calculated fields. Calculated measures can be added though not all DAX formulas can be used (For example, time intelligence). Another major limitation is the inability to add any other data sources to the OLAP in Power Query.
Import option attempts to mitigate these issues. We can select the fields and measures that we want from the OLAP. By importing the data from the OLAP, we are basically creating a flattened data set in Power Query. Once flattened, it's like any other dataset- table, view, excel file etc. We will then have full Power Query functionality at our disposal for this dataset. We can add/modify fields, blend with other datasets and create DAX fields and measures. Basically, when we import selected columns from the OLAP, the measures we select will be aggregated at the grain of the dimensional attributes we choose. Say, for example, the Sales Order Id may be the lowest granular level of the data present in the OLAP. If our analysis doesn't need such a granular data, we can exclude Sales Order ID from the import list and include only those attributes by which we want to analyze by - Month, Product, Country etc.
This is all well and good. But here is the catch. The Import functionality is unreliable and has issues loading data into Power BI. For me, the experience is inconsistent. Sometimes it works. But many times, I'm presented with the below error after sometime.
Upon investigating, the real problem was traced back to the internal query that Power BI is sending to the SSAS server (traced via SQL Profiler). Please find below the sample query using Adventure Works data that is sent to the OLAP:
- Live Connection
- Import
But there are several limitations in working with live connection. In Power BI, when using Live connection, we cannot modify any field names, or data formats. Also we cannot add calculated fields. Calculated measures can be added though not all DAX formulas can be used (For example, time intelligence). Another major limitation is the inability to add any other data sources to the OLAP in Power Query.
Import option attempts to mitigate these issues. We can select the fields and measures that we want from the OLAP. By importing the data from the OLAP, we are basically creating a flattened data set in Power Query. Once flattened, it's like any other dataset- table, view, excel file etc. We will then have full Power Query functionality at our disposal for this dataset. We can add/modify fields, blend with other datasets and create DAX fields and measures. Basically, when we import selected columns from the OLAP, the measures we select will be aggregated at the grain of the dimensional attributes we choose. Say, for example, the Sales Order Id may be the lowest granular level of the data present in the OLAP. If our analysis doesn't need such a granular data, we can exclude Sales Order ID from the import list and include only those attributes by which we want to analyze by - Month, Product, Country etc.
This is all well and good. But here is the catch. The Import functionality is unreliable and has issues loading data into Power BI. For me, the experience is inconsistent. Sometimes it works. But many times, I'm presented with the below error after sometime.
WITH MEMBER [Measures].[Microsoft.Mashup.Engine.One] AS 1SELECT { [Measures].[Microsoft.Mashup.Engine.One] }ON 0, SUBSET( CROSSJOIN( CROSSJOIN( CROSSJOIN( CROSSJOIN( CROSSJOIN( CROSSJOIN( CROSSJOIN( CROSSJOIN( CROSSJOIN( CROSSJOIN( CROSSJOIN( CROSSJOIN( CROSSJOIN( CROSSJOIN( CROSSJOIN( CROSSJOIN( CROSSJOIN( CROSSJOIN( CROSSJOIN( CROSSJOIN( CROSSJOIN( CROSSJOIN( CROSSJOIN( CROSSJOIN( [DimCustomer].[EnglishOccupation].[EnglishOccupation].ALLMEMBERS, [DimCustomer].[FirstName].[FirstName].ALLMEMBERS ), [DimCustomer].[Gender].[Gender].ALLMEMBERS ), [DimCustomer].[LastName].[LastName].ALLMEMBERS ), [DimDate].[CalendarQuarter].[CalendarQuarter].ALLMEMBERS ), [DimDate].[CalendarSemester].[CalendarSemester].ALLMEMBERS ), [DimDate].[CalendarYear].[CalendarYear].ALLMEMBERS ), [DimDate].[EnglishMonthName].[EnglishMonthName].ALLMEMBERS ), [DimProduct].[Color].[Color].ALLMEMBERS ), [DimProduct].[EnglishDescription].[EnglishDescription].ALLMEMBERS ), [DimProduct].[ProductLine].[ProductLine].ALLMEMBERS ), [DimSalesTerritory].[SalesTerritoryCountry].[SalesTerritoryCountry].ALLMEMBERS ), [DimSalesTerritory].[SalesTerritoryGroup].[SalesTerritoryGroup].ALLMEMBERS ), [DimSalesTerritory].[SalesTerritoryRegion].[SalesTerritoryRegion].ALLMEMBERS ), [FactInternetSales].[ExtendedAmount].[ExtendedAmount].ALLMEMBERS ), [FactInternetSales].[Freight].[Freight].ALLMEMBERS ), [FactInternetSales].[OrderDate].[OrderDate].ALLMEMBERS ), [FactInternetSales].[OrderQuantity].[OrderQuantity].ALLMEMBERS ), [FactInternetSales].[ProductStandardCost].[ProductStandardCost].ALLMEMBERS ), [FactInternetSales].[SalesAmount].[SalesAmount].ALLMEMBERS ), [FactInternetSales].[SalesOrderLineNumber].[SalesOrderLineNumber].ALLMEMBERS ), [FactInternetSales].[SalesOrderNumber].[SalesOrderNumber].ALLMEMBERS ), [FactInternetSales].[TaxAmt].[TaxAmt].ALLMEMBERS ), [FactInternetSales].[TotalProductCost].[TotalProductCost].ALLMEMBERS ), [FactInternetSales].[UnitPrice].[UnitPrice].ALLMEMBERS ), 4096 ) PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON 1FROM [Model] CELL PROPERTIES VALUE
<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Catalog>AdventureWorks2012</Catalog> <DbpropMsmdSubqueries>2</DbpropMsmdSubqueries> <LocaleIdentifier>1033</LocaleIdentifier> <Format>Tabular</Format> <Content>SchemaData</Content> <DbpropMsmdActivityID>8028a980-888c-4296-98b5-408eaf85d343</DbpropMsmdActivityID> <DbpropMsmdRequestID>4fd357b0-cc55-45a7-85a3-2afa1d7e4d4d</DbpropMsmdRequestID> </PropertyList>
A couple of points to note:- I'm connecting to SSAS Tabular OLAP, but the query generated is MDX rather than DAX. This itself shouldn't be a problem because MDX works with Tabular.
- It's way too lengthy and unnecessarily complicated
- This query uses CROSSJOINS to read data from related tables, which is very expensive
EVALUATE
FILTER(
ADDCOLUMNS(
SUMMARIZE(
'FactInternetSales',
'FactInternetSales'[OrderDate],
'DimSalesTerritory'[SalesTerritoryCountry],
'DimSalesTerritory'[SalesTerritoryRegion],
'DimProduct'[Color],
'DimProduct'[EnglishDescription],
'DimProduct'[ProductLine],
'DimCustomer'[EnglishOccupation],
'DimCustomer'[FirstName],
'DimCustomer'[LastName]
),
"Freight", CALCULATE(SUM('FactInternetSales'[Freight])),
"Extended Amount", CALCULATE(SUM('FactInternetSales'[ExtendedAmount])),
"Order Quantity", CALCULATE(SUM('FactInternetSales'[OrderQuantity])),
"Sales Amount", CALCULATE(SUM('FactInternetSales'[SalesAmount])),
"Tax Amount", CALCULATE(SUM('FactInternetSales'[TaxAmt]))
),
'FactInternetSales'[OrderDate] >= DATE(2008,1,1)
)
Comments
Post a Comment