September 30, 2014 at 12:28 pm
I’m reading the Microsoft SQL Server 2008R2 Analysis Services Operations Guide and have a question about chapter 7 Diagnosing and Optimizing.
On page 63 (7.1.1.2 Relational Indexing for Dimension Processing) it’s advised to create a schema bound indexed view for the low cardinality attributes City and State of the Customer dimension. But what to do next with the view? What would be best practice towards the dimensional model? I’m missing the link between advised changes in the relational engine and consequences in the dimensional model / SSAS model.
Is a snow flake schema the logical consequence? Or a separate City/State dimension, directly connected to the fact table?
October 9, 2014 at 12:26 pm
Perhaps I should explain a bit more why I’m confused about the example.
In the abovementioned SSAS Operations Guide, the chapter deals with dimension processing and optimizing the relational engine by proper indexing.
The indexing examples are based on a customer dimension and my assumption is that this should be one dimension table in the relational data warehouse.
In the last indexing example, City and State in the customer dimension are considered low cardinality attributes. Here the advice is to create an indexed view, including the City and State columns and a unique clustered index on both columns.
And that’s the end of the example. It’s not explained how to make use of the newly created indexed view. Should it become an outrigger, thus creating a snowflake model? Or should the indexed view become some kind of geography dimension, directly linked to the fact table? Or perhaps something else I don’t know of? I can’t figure out where the indexed view example should lead to.
If someone here can, I’d be much obliged.
October 9, 2014 at 3:03 pm
The example refers to large dimensions and how to optimize the load from the database server into the SSAS file structure. The customer dimension is used as an example of a large dimension and the white paper alludes to hierarchy of zip-city-state-country. At the beginning of the referenced section, the white paper shows the queries generated for processing, one of these queries is:
SELECT DISTINCT City, State FROM Dim.Customer
The white paper explains how you could optimize that particular statement from a database perspective with the unique index. The City State index will allow the database server to return City-States combinations by reading the unique index instead of a potential table scan of a large dimension.
The referenced section is NOT about SSAS, but about optimizing your database data structure for loading data into SSAS.
Did that help?
October 9, 2014 at 9:21 pm
What I don’t understand is how the SSAS processing engine could get knowledge of the presence of the indexed view. Well, I do, but only by changing the dimensional model. Either in the data warehouse or in the data source view on the SSAS side.
And what should be changed in the dimensional model is not explained, that’s why I’m confused. Am I missing some basic concept in SSAS dimension processing or is the example incomplete?
October 11, 2014 at 6:51 am
It seems that I’ve missed a basic concept concerning SQL Server query optimization. The query optimizer can choose to use the indexed view, even when the indexed view isn’t referenced in the original select statement. I didn’t know that and was quite surprised when I read about it this morning.
But now that I know, the example makes perfect sense.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply