I have a table with a list of contract line items in the form of
CREATE TABLE contracts_lines (
contract_id integer,
customer_id integer,
product_id integer,
contract_line_start datetime,
contract_line_end datetime,
amount float
)
What I would like to produce is a CUBE (that allows me to determine how much revenue I can expect each month - we have a simple rule that each line is recognized evenly over the term of the line (i.e. daily revenue = amount / (contract_line_end - contract_line_start) )
Just newbie to Cube concepts, so far created
month_dim (from 2005 till 2015)
Customer_dim (Cust_id, cust_number, cust_name, cust_add, cust_city, cust_state, cust_country)
product_dim (prod_id, prod_code, prod_name, prod_desc, prod_category)
product_manfct_dim (prod_manfct_id,prod_manfct_name, prod_manfct_desc)
Location_dim (loc_id, loc_state,Loc_country)
Would like
montly_revenue forecast for 12 months=
location_country
month
product
amount
should there be another dim for contract
please advice, how I can now project this for cube or is it missing more dimensions
I have spoken with my colleague and he confirmed building a table with monthly (or daily) data is the way to do it in data warehouse. We also discussed using MDX and I suggested my approach which I couldn't resolve and he pointed me to very useful MDX function and the results look promising just need to work out final bits.
MDX might be easier to implement but might be more complicated then building a table with data that OLAP handles very well.
Here is the split table
CREATE TABLE contracts_lines_split (
contract_id integer,
customer_id integer,
product_id integer,
contract_line_start_year INT,
contract_line_start_month INT,
contract_line_end_year INT,
contract_line_edn_month int
daily_amount decimal(9,3)
)
-- Just a small comment. I would avoid using float. It tends to round figures. If you don't need precision higher than 38 use decimal. In the above example I set it up as precision 9 (<=9 is 5 bytes) and scale of 3 I doubt dialy amount is > 999999 and 3 decimal places should be fine. You can adjust to your needs.
If your average contract length is 24 months then this table will be 24 times bigger than contract lines but it should avoid having to build complex MDX calculations. I wouldn't worry too much about performance OLAP is designed to handle large valumes of data. If you need to increase processing than you can use partitioning which is very common for tables larger than 20 millions rows. once the data is loaded MDX query approach should handle it very well.
Not sure if I answered preciselly your question :) but I hope that helps.
Regards
Emil