Tabular Hierarchies Across Multiple Tables

Recently I was looking at the Adventure Works database and I noticed how Product, ProductCategory, and ProductSubCategory are their own separate tables. It got me to thinking about how someone might want to take attributes from each of those tables and create one hierarchy for reporting when you are looking at it. In DAX, this can be accomplished by using the RELATED() function. By using the related function in our table with our lowest level attributes, we can bring in the higher levels and create our hierarchy. This is really useful, especially if our data source isn’t necessarily a data warehouse and may be something more transactional with many tables split all over the place.

 

The Related function excepts one argument, which is the name of the table/column that we want to pull back. Let’s start with a simple diagram of our 3 tables:

 

image

 

Since our Product dimension is our lowest level of granularity of these attributes, what we want to do is bring the Product SubCategory and Product Category fields into the product dimension. Change your view to the data view in the product dimension and add a calculated column to pull in the subcategory:

=RELATED(DimProductSubcategory[EnglishProductSubcategoryName])

You can see our RELATED function at work. What we’re telling the function to do is to find the relationship that exists between product and product subcategory, and bring back the product sub category name. Once you click enter, rename the column to sub category name. Next we want to bring in the product category. Add a new calculated column, only this time our RELATED function is going to point 1 column farther, into the Product Category table. The RELATED function is really useful for this kind of analysis, especially since it can traverse multiple tables. For any old school excel pros out there, take that vlookup! Below is our new column with both Product Sub Category and Product Category added:

image

 

Hide your key column attribute, and then go to diagram view and hide your subcategory and category tables. To hide the entire table, right click on the table itself and select hide from client tools. Create a new hierarchy in your dimension where you added your related columns to, and added the category and subcategory to them. The final design looks like the below:

 

image

 

And that’s it! Now we have all of our related attributes in one table that we can create hierarchies off of. This is very useful for multiple snowflakes in a warehouse or transactional databases.

Advertisements

Installing SQL 2012

Recently I needed to upgrade my CTP edition of SQL 2012 to a full actual install. I thought since I had to uninstall the CTP to install the developer edition, now would be a good time to blog about the steps necessary to install it. Below are the steps that I took:

 

First, locate the installation media for SQL 2012. When you find the SETUP.exe file, run it. After it loads you will see a screen that looks like the below:

 

image

 

Click on the Installation tab on the left, and then select “New SQL Server Stand-Alone Installation or Add Features to an Existing Installation”

 

image

 

After you click on it a new window will appear, where some rule checks will take place. Once they finish successfully click OK and you’ll proceed to the next screen:

image

A few more minutes will pass and then you will get a new screen where you enter your product key. Enter your key or specify a free edition to install if you don’t have a key. Click Next, Accept the license terms and select if you want to send usage data to Microsoft. Let it run the update wizard and select to install the updates. The install setup screen will run:

 

image

Once it runs and extracts (this could take a few minutes depending on your updates), Once it finishes it will proceed to the Setup Support Rules wizard, where they will run and inform you of any issues. Assuming everything passes fine, Click Next.

image

 

Once you click Next, you’ll be on the Setup Role screen. Select the feature you want. In this instance, we want the SQL Server Feature Installation.

image

The next screen is the fun one. Select the features you want to install. In my case, this is for my regular development I do from my laptop and for quick demos when I need them. I’ve selected the features I want, and I’m ready to click next. One really nice feature in 2012 is that Master Data Services can now be included with the regular install, in 2008 R2 it was it’s own install file that you had to find in the setup files.

image

Once you click next, the Installation Rules wizard will run. Let it run and then click Next. The next page is the Instance Configuration page. Select if you want the default instance or if you want a named instance running on your machine. On my machine the default instance is already being used by 2008 R2, so I’m installing a named instance. Also please note the Instance Root Directory. This is the default installation path for SQL Server. It is a Microsoft best practice to install it on a volume OTHER than your C: drive. And yes, I know I’m breaking that very rule here due to the options on my machine.

 

image

 

Click Next, and the Disk Space Requirements wizard will run. Click Next. Configure your service accounts and click Next. It is considered a Microsoft best practice to use separate, least-privileged domain accounts to run each of the services needed. This prevents having one “super-account” that has permissions to do everything on your server. After you’re done click Next, and the Database Engine Configuration page will appear. Specify if you want just windows authentication or mixed mode, and then add a user to the administrator section. If you select mixed mode you’ll need to specify a password for the SA account. On the data directories tab create your installation paths (keeping in mind they should NOT be on the C drive).

 

Once you click Next the Analysis Services configuration screen will appear. Select if you want to install Multidimensional or Tabular mode, and the admin for the instance. On the data directories tab, change the path to something other than the C drive. If possible, this should be on a drive separate from your database engine and tempDB as well. Analysis Services works best on its own drive. Click Next.

 

image

 

On the Reporting Services Configuration page, select Install and Configure if you want it to be configured for you while its installing or if you just want to do the install and configure it yourself later via the Reporting Services Configuration tool. Click Next, and check if you’d like to send Error Reporting to Microsoft. Click Next again and allow the Installation Configuration Rules to run.

image

Click Next, check the Summary to ensure everything you want installed is there, and click Install. Depending on the features you selected, it may take a while. With the features I selected above, it took about 1/2 an hour to get everything up and installed. If all goes well, your final screen should look like:

image