SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SSAS Tabular DirectQuery–No Hierarchies in Dimensions

I recently was testing DirectQuery to see the reporting differences in a Tabular Model. After Deploying to a development server and analyzing in Excel, the hierarchies for the Date and Product dimension were not available. SQL Server 2016 SP1 was used in this example.

image

You can go to this like from Microsoft about more limitations.

There was also difference in labeling when designing a Tabular Model in Visual Studio (SQL Server Data Tools – SSDT) and making a change to the Model from SQL Server Management Studio – SSMS.

In SSDT, The Property for the Model is DirectQuery and the values are On and Off.

image

In SSMS, The Property is Default Model and the values are DirectQuery and Import.

image

Not sure which I like better. But, what you are able to do is Deploy a model built with DirectQuery Off (with hierarchies), then change the property in SSMS on the database, and see the changes. Or vice versa. If you are previewing the DirectQuery Off Mode in Excel before making the change, when the data is refreshed, the hierarchies are removed.

The Smiling DBA

Thomas LeBlanc is a Business Intelligence Consultant/Data Warehouse Architect in Baton Rouge, Louisiana. He uses his 25+ years in IT to help develop OLTP systems with normalized databases for high-performing T-SQL and end-to-end dimensional data marts using SSIS, SSAS, PPS, and Excel. His SQL Server certifications include MCSA 12, MCITP 08 BI and DBA, MCITP 2005 DBA, and MCDBA 2000. As a PASS volunteer, he is current chair of the Excel BI virtual chapter, past chair of the Data Architecture virtual chapter, and past virtual chapter mentor. He has helped the Baton Rouge SQL Server User Group with SQLSaturdays and speaks at local IT meetings.

Comments

Leave a comment on the original post [thesmilingdba.blogspot.com, opens in a new window]

Loading comments...