Incorporating Budget Data into a Cube

[Nassy]

Hi ,

A bit of Background Information

I am currently trying to create a cube that will help our Finance departments generate Sales statistics based on Property Sales. My knowledge of cube design is now a little rusty so some advice is definitely needed...

Currently I have designed a fact table which lists transactions, the type of transaction eg property instruction, property viewing, property sale ,a list of 'facts' related to that transaction eg fee, revenue, net revenue and Finally.. the office branch the transaction relates to.

Fact Table
----------
PropertyID
OfficeID
TransactionTypeID
TimeID
Amount
Revenue
Net Revenue
Fee

I then have designed a Transaction Type, Office and finally Time dimension with Day, Week, Month, Quarter and Year elements.

The Problem

The Fact table will be populated using data from our Estate Agents IT system (VAuLT). Details of transactions are stored on a day by day basis in this system. The problem is that budget data is not entered in this system.

Budgets are entered for each office for each month in a spreadsheet and are stored already as summaries

eg
Sales# Sales Revenue
---------------------- ---------
July 2004
Bristol: 10 £780000k


My Question

I would like the users to be able to view monthly actual and budget figures side by side month but if the user selects to view figures by month(or just blank entries).

If anyone could offer advice as to how I should go about incorporating monthly total figures for budgets with daily actual figures that should be shown by any period it would be an enormous help as I am not quite sure how to structure my tables to combine these two sets of data!



Nassy

 

[jefers]

1.You can add budget date into fact table
Add Budget data into firstday of every month;
2.You can create two cube,one contains fact data,another contains budget data.
Cube(Fact)
Cube(Budget)
At Cube Fact,you can create a command to get budget data from Cube Budget using function <LookupCube>.
In Command Mdx,you should change from one date set to another,maybe this will deal with string operate.
My English is poor!