How do I design a Dimension and Fact Table from a relational database?

Question

I'm new to using SQL Server 2008 R2 SSAS. I have the following tables in my database, that I wish to model using SQL Server Analysis Services:

Table1

ServiceTypeID    | ServiceTypeName
---------------------------------------
   001           |    FLM                                       
   002           |    PM    
   004           |    SLM   
   005           |    CL    
   999           |    OTH   

Table 2

BankCode   | BankName
------------------------------------------
BAY        | Bank Of Ayudhya    
Brinks     | Brinks rama3       
ESB        | Easy Buy   
ISB        | Islamic Bank   
SCB        | Siam Commercial Bank   
SCBT       | Standard Chartered Bank    
TCRB       | Thai Credit Retail Bank    

Table3 (main table)

TransactionNo            | ServicedDate            | BankCode | ServicedTypeId
------------------------------------------------------------------------------
ATMBK00000000000104212   | 2011-06-09 00:00:00.000 | BAY      |  001
ATMBK00000000000104648   | 2011-06-14 00:00:00.000 | BAY      |  001
ATMBK00000000000105504   | 2011-06-24 00:00:00.000 | BAY      |  001
ATMBK00000000000107397   | 2011-07-14 00:00:00.000 | BAY      |  001
ATMBK00000000000111500   | 2011-08-23 00:00:00.000 | BAY      |  001
ATMBK00000000000115016   | 2011-09-23 00:00:00.000 | BAY      |  001
ATMBK00000000000123117   | 2011-12-14 00:00:00.000 | BAY      |  001

I need a to design a fact table and dimensions to get a report of BankName as a column and ServiceTypeName as row, categorized by month. I've used the DISTINCT COUNT function on TransactionNo to get the total number of different services provided to each bank. I also don't have a time dimension, which I need.

Can anyone point me in the right direction?

Answer 1

First, I suggest you read about star schema first. Not understanding the concepts could lead to wrong results.

You may use the transaction as your fact table as in the diagram below.

Don't use DISTINCT, instead you want to use:

SELECT ... FROM fact GROUP BY dimension data (time, transaction and bank) WHERE condition to restrict dimension data and join with fact.

Answer 2

The suggested answer already suggests a suitable star schema for your data. The error message you are receiving is telling you that the dimension value does not exist in the dimension table.

In your sample data the ServicedDate field is date only but in your error message it is both a date and time. If you require the time portion of the ServicedDate field then it is advised to split this field into separate date and time fields and have separate date and time dimension tables. Alternatively trim the time portion of the field so you only have a date for the value.

Ensure your dimension tables are loaded with corresponding records that exist in your fact table.

I would highly recommend The Data Warehouse Toolkit 2nd Edition to get familiar with dimensional modelling and design practices.