Monthly Archives: July 2014

NEW AND IMPROVED … Better than ever!!!

Its brand new and shiny, our updated blog features a whole new layout and easier to navigate menu and blog posts. After doing a fair amount of review on our previous site in terms of traffic and other factors we identified our Blog had a few issues in terms in usability and layout / appearance. This new version aims to address those issues providing a more user friendly and intuitive navigation experience. We also have started a focus on more visually engaging blogs. Also check out our events section to find out where we will be over the coming months.

Our brand new blog theme and layout tries to bring back something we have lost along the way. A spirit of fun and excitement that shows our passion towards learning and sharing. We hope you love it coz we sure do!!!

As always we would love to hear what you think so please feel free to share.

A Social message

All that is necessary for the triumph of evil is that good men do nothing. – Edmund Burke

The darkest places in hell are reserved for those who maintain their neutrality in times of moral crisis. – Dante Alighieri

This blog is a call to action.

 

If the above two phrases has some resonance with you, take a moment to act. Things go wrong and bad things happen, we all know this and have come to accept certain things as a way of life. The fact however Life is what WE make it. All the inspirational messages that people forward you on emails and WhatsApp etc. don’t mean a thing if its treated as entertainment. Ask yourself when was the last time you did something for another other human being just because you can.

Then ask yourself how long does it take for you to change another’s life for the better. To be clear I am not asking you to donate tons of money to some charity or take to the streets. Small things can help just as much when everybody is doing it.

  • Donate blood.
  • When you see someone on the road after an accident stop and ask them if they need help instead of slowing down to see what the commotion is about.
  • If you see something dangerous on the road try and get rid of it or make sure the right people are informed.
  • When you know something is not right raise your voice against it. Remember social media can be used for SOCIAL things too.

A saw a girl last week who was filtering water from drainage with rain water overflow using a towel. We are coming up with a list of cost effective solutions we think are affordable to the poor. Such as Potassium Permangnate, Solar cookers to boil water, using activated carbon and UV lamps to treat water. Any other suggestions are more than welcome.

 

A video educating kids on Good and Bad Touch

https://www.youtube.com/watch?v=VkY0xqtw6W8

SSAS: – Dimension Hierarchies

Most of the time there will be a relationship between different attributes in the dimension. We saw an example of this kind when we created the date dimension in the previous post. In particular this kind of relationship was a parent child relationship or a hierarchy between Years to Month to Week to Day. Such relationships exist in many different scenarios in day to day business cases. Another example of such a relationship would be between the product category to sub category and then to product items or between a country to state to city to address.

In our cube let us now add a few additional tables and generate a product hierarchy which we can use to identity sales by product groups.

First we need to import the new tables that contain the product relationship into our cube. We see that there are mainly three tables we are interested in.

This is because our cube focuses mainly on sales data so we do not need a lot of product information that would be available in say for example a product inventory cube.

As we can see there are three different tables here so let us now de-normalize these tables by creating a named query.

select

pc.Name

as

CategoryName , psc.Name

as

SubCategoryName , p.Name

as

ProductName , p.color , p.ProductID

from

[Production].[ProductCategory]

pc

join

[Production].[ProductSubcategory]

psc

on

pc.ProductCategoryID = psc.ProductCategoryID

join

[Production].[Product]

p

on

psc.ProductSubcategoryID = p.ProductSubcategoryID

Notice that we have included only the productid as the key column since this is the column with which we can link to the measure group tale called sales order details.

In the below screen shot we have created the named query and now assign the productid as the logical primary key. Please refer the post on creating named queries to see how we reached till here.

Once the primary keep is setup we establish the relationship between the fact table and the product catalogue by selecting the productid from the fact table (salesOrderDetails) and dragging and dropping the column into productid column in the ProductCataloge table.

Save the data source view. Next we create the dimension using the new object that’s been added to the Datasource view.

Please refer the post on how to create the dimension in order to reach this step. When we explore the dimension we see that while there is a relationship between the category , subcategory and the product name there is non-defined in the Hierarchies pane in the middle of the screen.

Let us now define this hierarchy. We do this by dragging and dropping the different attributes into the middle pane , as you can see the highest grouping ( category in this case ) comes first and then we add the next sub groups etc. until we reach the primary key column. At this point we are presented with a warning.

Since there is no relationship defined we need to do them now to ensure we get the best performance. We can do this by navigating the to Attribute Relationships tab on the top half of the dimension editor window. The below screen shows us the different attributes are related to the key column instead of each other. Let us correct this now.

From the above screenshot we can see that the relationship between the ProductId to the Product Name is correct so now we need to map the product Name to the productsubcategory as shown below.

Select the mapping between the productid and the sub category name and right click and choose Edit Attribute relationship.

In the window that appears on the third column of the above table define the relationship between Productname and Product Subcategory by selecting the ProductName from the left hand side menu. Press OK , repeat the same steps for the mapping between productid and category only this time the relationship is between sub category and the category.

When you’re done the Attribute should look like the screen below.

At this point we see that the blue line from the hierarchy that was defined earlier is gone

We now see there is an additional blue line under the dimension attributes pane

Avoid visible attribute hierarchies for attributes used as levels in user defined hierarchies.

It says we should hide the attribute if it already belongs to a hierarchy. This is only a recommendation and therefore we need to evaluate if there is a requirement to make these changes. A typical example is when viewing the product catalogue do we need to be able to view data at the sub category level without its dependency on the parent (category) or the child (Productid). It’s better to look at this in terms of an example so let us finish this dimension and then come back to see examples of this.

The next step we need to do is to define the key column relationship for the attributes. Earlier we had a unique productid for each product Name, and we had unique Product subcategory for each for sub category and the same for the categories too. However with our current design and relationship defined we see that multiple products can map to the same subcategory so the value of the subcategory will be duplicated many times. We need to correct this so that when we drag and drop a subcategory while browsing the cube it understands that the cube will need to look up the underlying product IDs and then map to the fact table using these product IDs.

We do this by selecting the attribute and then navigating to its properties window.

In the properties window you will see the section for source and under source there is an option for Key columns. Click on the Ellipse next to header key columns. In the this example I am configuring the key column for the sub category .

Once the above screen as been setup. Press OK. We now need to define the Name column which in this case is the subcategory attribute so simply select the ellipse and select the subcategory column and press OK.

I have included the screenshots for the category below for comparisons.

Save the dimension Dim_productcataloge

Let us add the newly created dimension to the cube as done in previous posts.

At this point we now need to add a new measure; the measure will be used to identify unique line items in the order. We have already created a measure to count distinct order header id but this measure is not granular enough to identify the sales are the like item level.

When we query the sales order detail table we see that there is a column called call OrderQty which shows the number of times that order line items was included or purchased as part of the order. Therefore in the below example we see that Sales Order ID 43659 purchased three pieces of the productId 777.

So in our measure groups we are going to sum the values by OrderQty as shown below

If each item will always be billed individually we would have got the same result if we did a count of rows. But the best practise is to use Sum.

I renamed the measure group to make sure they names are distinct and meaningful.

Now let’s Map the new measure and the dimension to each other. As before the relationship is automatically identified as part of the primary key and foreign key relationship between the two tables.

Save the cube and deploy it and process it.

Browse the cube by connecting to SSMS and drag and drop the hierarchy from the dimension into the rows pane of the screen.

As you can see from the screenshot SSAS automatically understood the relationship and aggregated the data based on the relationship defined by us earlier.

Avoiding visible hierarchies, when we set the property AttributeHierarchyVisible property to False the attribute stops appearing in the dimension pane of the browser. This is because we now have the attribute as part of the hierarchy anyway.

BEFORE ( VISIBLE )

AFTER ( NOT VISIBLE )

References

http://technet.microsoft.com/en-us/library/bb630296%28v=sql.100%29.aspx