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:


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?


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

| improve this answer | |
  • thank u but i create a measure that used Distinct count on Transaction no. . Sorry i don,t know where to put the Query that u suggest. I am Really new – suttichai Aug 28 '12 at 10:44
  • i can get a what i want now but with out Time dimension . when i add a relationship between Date dimension and Measure .after i deploy it show error: Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'dbo_TblTransactionMaster', Column: 'ServiceDate', Value: '2/4/2008 7:00:57 AM'. The attribute is 'Date'. – suttichai Aug 28 '12 at 10:51
  • @suttichai, the time diminsion is commonly used because it allows flexibility, however, it is not mandatory. I don't understand how what your query is like or what you want, but it is not common to use Distinct on a 'fact' table. I can't help with the error either because I can't see the query. Maybe you want to have a new question for that. – NoChance Aug 28 '12 at 14:36
  • ,can i have yours e -mail so that i can show u a screen shot – suttichai Aug 29 '12 at 2:04
  • Yes but I really can't provide more information on this. – NoChance Aug 29 '12 at 20:49

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.