This week we continue to look at SSAS on a topic related to last week’s look at Named Calculations. Named calculations are great when you want to use existing fields within a table to create a new field. You can even define an expression that uses fields from other related tables in the data view to create a new field. However, sometimes a named calculation just is not enough.
A good example of when named calculations may not be enough is when you want to flatten a hierarchy of dimension tables into a single level. In the Contoso database we are working with, the Product dimension links to the product subcategory dimension to get the product subcategory name. Then the subcategory dimension further links to the product category dimension to the product category name. While you could model the sales cube using this hierarchy of dimensions from product to product category, a better choice in terms of performance is to flatten the model and place the product subcategory name and the product category name in the same table as the product. The way we will do that this week is with a named query.
I’m going to assume that because you have read some of my previous blog posts, you can build a data connection and a data view against the Contoso Retail database that I’ve been using the last several weeks. With the diagram for the data view displayed in the central panel of BIDS, right click on any of the background area of the diagram. A menu like the one shown below appears. You can click New Named Query to begin the definition of a new named query.
Defining a named query is very similar to creating a view within SQL Server Management Studio. When I click the option to create a new named query in the above menu, I am presented with the following dialog to help define the query. Note that I really must define a unique name for the named query that does not currently exist in the view. I must also identify the data source that I will use, but the default automatically points to the same data source as the current data source view. Optionally, I can include a description for the named query.
I then have the option of building the query using the visual tools to add the tables from the current data source that I want to use. Note that these tables do not have to be already included in the data source view. If relations already exist between the selected tables, the diagram automatically includes those relationships. Otherwise, I must define relations between the tables manually.
I can then select the fields from each table that I want to include in my named query by clicking the check box before the name of each field. Note that as you select fields, they appear in the second panel of the Query Definition and display the other properties of the selected fields. Also while I am selecting tables, defining relationships, and picking the fields I want to use, the dialog automatically writes the SELECT statement needed to gather the data in the third panel of the Query Definition.
Yes, I could directly enter the SELECT statement if I so desire, but to avoid possible errors including spelling errors and syntax errors in the expression, I find it easier to use the graphical design interface.
Once I have built the SELECT statement for my query, I click OK. I now see my new table on the design interface of the data source view. However, it is not by default connected to any of the other tables. Therefore, the next thing I need to do is to define a relationship between one of the other tables in the view with the new named query. I can do this by identifying a field in another table that corresponds to a field in the named view, clicking on that field and dragging down to the corresponding field in the named query. In my example, I would want to connect the ProductKey field in the FactSales table with the ProductKey field in the named query. When I do this, I see the following message because the named query does not have any indexes defined that can serve as the primary key in this relationship.
By clicking Yes in this dialog, I let SQL create a logical primary key for the named query so that it can define the relationship. At this point, my view dialog looks like the following having both a DimProduct table and a ProductWCategories named query (virtual table). I really don’t want or need both.
One way to get rid of the old DimProduct table is to right click on its header area to display the menu shown below and select Delete Table from DSV.
While this works, there is another way that I could have both build the new named query and replaced the old DimProduct table as a single operation. Let’s assume for a moment that I had not yet created the named query. I could have instead started this process by right clicking on the header area of DimProduct and selected Replace Table from the menu that appears. Notice that this menu option has an arrow pointing to the right along the right side of the menu. This indicates that this menu option has a fly-out of addition options. When I hover over Replace Table, the fly-out appears with two options, the first of which is to replace the existing table with a named query.
If I had selected this option, I would again be taken to the dialog that allows me to define the named query that we saw previously so I will not repeat it here. However, when I completed the named query, rather than returning to the view dialog and seeing both DimProducts and the new named query, we would see only the new named query saving me a step.
Note in the above image that the second option of the fly-out also allows me to replace a table with another table rather than a query.
Regardless of which way I defined the named query, I must then deploy my solution before I can open it in the built-in browser. The figure below shows a simple pivot table created from my cube definition that builds a manual hierarchy by dragging each of the individual fields to the row labels beginning with Product Category Name, then Product Subcategory Name, and finally Product Name (through the Product Key).
Next time, I will look at building the hierarchy for products directly within the dimension so you only have to drag one element onto the pivot table to create the entire hierarchy automatically.
C’ya next time.
SharePointMike's Blog 