Now that the dimension has been defined we need to add a measure to our cube in order to be able to answer the question “What is distribution of sales by card type so that we can partner with the best provider to give our customers discounts”
We already have a dimension which will help use analyse the credit card types , but we now need to have a way of identifying the count of orders , in order to do this we need to revisit our original design. We started out by creating a data source view that contained 2 different tables with order information the first one being the order header and the second one being the order detail. The nature of the order header defines that payments are done against the order and not the individual line items. Imagine paying for each item one at a time at the checkout counter. All the items together belong to the same order so it makes sense for us to group data from the different line items up to order level and then simply have an order level grand total.
However, we have replaced the order header and order line items tables with a named query which joins the order header to the order line by ordered in order to have a single fact table. This means that while we had one row per order header in the previous scenario we not have the order header repeated as many times as there are line items within the order. This is not an ideal cube design and was implemented only to show that use of named queries when de-normalizing. Let’s leave it as it is for now. Now that we are aware of the quirk on our data we need to account for it when creating the measure.
Open the democube project and select the Cube name from the measures pane on the top left window of the cube designer. Right click and select New Measure.
The below windows will appear , it will default to the table that appears first alphabetically so make sure to select the SalesOrderDetails named query as the Source table in the second drop down list. Here you will see that not all columns are visible initially, this is because a measure is usually something that can be aggregated i.e. summed, counted, averaged etc. Text values do not fit into this description and so SSAS assumes these columns are not measures. However as mentioned earlier in our case we need to be able to count the distinct number of Orderid in our fact table SalesOrderdetails in order to know how many orders were place using a particular credit card. So we select the check box at the bottom of the screen called Show all Columns, at this point all columns within the named query Sales Order details are listed and we can now select the Sales OrderID. . A key thing to remember now is that we do not want to count the Sales OrderID as it is, because for an order with 10 line items we will have the order id repeated 10 times. So in this case we need to count the number of distinct occurrence of the Sales order id so in the Usage drop down list we select the option for distinct count.
Press OK once you’re done and we will see that the measure group (a broad classifier for similar measures which usually originate from the same table) with the single measure called Sales OrderId Distinct Count. At this point we have created our measure. It is a bit long so I am going to rename it to Cnt_SalesOrderId.
Simply select the measure and Press F2 on the keyboard to rename the measure.
Please Consider Subscribing