Sql-server – How to design a Dimension and Fact Table from a relational database

cubesql-server-2008-r2ssas

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?

Best Answer

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.

enter image description here

Related Question