Facts & Dimensions

This morning I went for a merchandise store to have some products of my daily use. Usually my mom gives me a list of products to get from the store and today also I had a list of products. I gave the list to the man and asked to deliver those items. But this time I was interested in seeing his calculation and the system of his sales maintenance.
I saw him entering my details such as my name, address, street, phone and other details related to me in one table and my sales information (all purchase related calculations e.g. OrderQuantity, UnitPrice, DiscountAmount, TaxAmount etc) corresponding to my details in another table.

This is the case of Datawarehouse design; all the information about the customer and its transaction is divided in 2 different tables. One contains the detailed information of the customer and the other related to its sales (additive values).The first table you can say as Dimension table which contains details of the customer.Second table, which contains all the additive figures and the corresponding foreign keys of all the dimensions, is called Fact table.
Example from AdventureworksDW database shows the Fact and Dimension tables below.

Fig1.Customer Dimension

Fig2. Fact Table

So we have a record in both the tables for a customer. Similarly for a Product and various dimension tables.

Hope this clears the idea of Fact and dimension tables in the warehouse.

Dimension: Dimension table contains all the detailed textual information of the dimensions.

Fact: A fact table typically has two types of columns: those that contain facts and those that are foreign keys to dimension tables. The primary key of a fact table is usually a composite key that is made up of all of its foreign keys.
In the next post we will discuss about the various schemas of datawarehouse projects.

'Item: code' and 'Unknowns' in Proclarity

Today my user said she is facing an issue in Proclarity. She is using proclarity which take data from a Cube built on Analysis services. She wants to know what is “Item: code” (e.g.; Item: 0, Item: 1, Item: 2 ….) shown in Proclarity and why it displays different values with “Item” prefix?Now let me explain various aspects of this issue. Why this Item appears?As you know that the cube consists of schemas (star or snow-flake). These schemas are developed with various facts and dimension tables. When we make some selection in the Proclarity, it fetches data (measures) from the fact table and members from the dimension tables. When we produce a grid view in Proclarity, we need to access both dimension as well as corresponding fact rows to that dimension.Eg. If we need to know the Gross Sales Value for sales to a particular customer with customercode (primary key) say 100, we need to fetch one row from dimension and corresponding all the rows from the fact table.This is done with various join architecture where joins on fact and dimensions are based on composite keys.Now if the join is valid (a row is available in the fact row with customerkey 100) but its other attributes (columns other than composite key) dimension table is blank or null for that customer, then Proclarity shows Item in place of the description.
Whereas if there is no corresponding row (value) available in the dimension table for any row in the fact table, Proclarity displays Unknown or you can set it to display Unknown in the report.Now this makes clear why Item or Unknown appears in the Proclarity report. You can say this is because of the dirty data.
Why different numbers after the Item:?The numbers after Item: are the index of the dataset that ProClarity retrieves from the cube. I mean to say this is the sequence in which the member appears in the proclarity report view or you can say the sequence of their appearance in the setup panel. Let me make it clear it with the help of an example:Suppose you have selected some members from the Product hierarchy/members. Now if the Zeroth item has null/blank in the dimension table, the first row will appear with Item:0. Similarly First item shows Item:1 in case of null/blank value in the dimension table and so on.
The figure here shows the Item:5 and Item:6 instead of the customername. This is in correspondence to INDEX 5 and 6 as shown below. Similarly it takes the index number after Item and displays the same.
In the screenshot here you can see that the tooltip displays name in case of
PERFUMERIES but is blank in the other case.
This is the case when the description is blank\null in the dimension table for a valid join.

Who am I ?

Hello Everyone,
I was searching for the topic for a post to start with, then one of my friend suggested me to introduce myself to all my readers. Even i liked the suggestion and now am doing the same.
I am a software (IT) engineer engaged with a MNC and have Microsoft BI as my primary skill. I work in a support project supporting a solution which provides my client with complete Business solution. We face a new issue now and then raised by the users of our solution and with the flow of data in solution and sometimes we are stuck with the problem with no help from anywhere. This make our job difficult.
So, one day suddenly it strike in my mind to help others with the problems faced by us (our team). This will make your job easier whenever you face such issues. So you can expect solution for your problems related to Microsoft BI in my posts here. Will try to extend my knowledge to other BI solutions existing in the market as well.
So, just be with me and suggest me with your views @ mrvijaykr@gmail.com .

Thanks for being with me till now.

- Veejay