PDA

View Full Version : New to OLAP



thammoud
11-29-2003, 06:49 AM
Hello,
I am very new to OLAP. A facts table contains the following:
- Security ID
- date
- profit
- trader ID
- Strategy ID
How can I specify the following measures:
- Weekly profit (Profit on 'reference date' - Profit on 'reference date - beginning of week('reference date')
- Yearly profit (Profit on 'reference date' - Profit on 'reference date - beginning of year('reference date')
- Daily profit (Profit on 'reference date' - Profit on 'reference date - Day before('reference date')
Any help will be greatly appreciated. This looks like a great project. Keep up the good work.
Thanks,
Tarek Hammoud

sgwood
11-29-2003, 07:52 AM
So, profit in the fact table is a cumulative profit figure.
I would suggest that for good slicing and dicing, your should change the profit to be "profit made on the given date for the trader, strategy and security".
Add a "time" dimension based on the date that defines the week and year that a given date is in. See
https://sourceforge.net/forum/message.php?msg_id=2206528.
Note that while the thread above talks about creating a view, I found (in Postgres) that is was better to have a separate DateHierarchy table that I could index to get good performance.
Then you can do MDX like:
select {[Measures].[Profit]} on columns,
{([Time].[All Periods].[2003].[January].[Week 1],
[Trader].[All Traders].[Joe Bloggs])} on rows
from TradingProfit
Enjoy!
Sherman

thammoud
11-29-2003, 09:59 AM
Hi Sherman,
Thanks for the quick response.
"I would suggest that for good slicing and dicing, your should change the profit to be
"profit made on the given date for the trader, strategy and security"."
What I really want is a set of values that represents daily, weekly and yearly profits for the trader.
For example,
Given a reference date, say 11/15/2003 , the result is to look something like this:
Trader Strategy Daily P&L Weekly P&L Yearly P&L
------ -------- -------- ---------- ----------
John Converts 100 300 -1500
To calculate Daily P&L ( you would take the two records on 11/15/2003 and 11/14/2003) and diff the profit column.
To calculate Month P&L ( you would take the two records on 11/15/2003 and 11/1/2003) and diff the profit column.
To calculate Yearly P&L ( you would take the two records on 11/15/2003 and 12/31/2002) and diff the profit column.
I am being a bit thick here, but I can't see how your proposed solution provides what I am looking for.
Again thank you very much for your quick response.
Tarek Hammoud

sgwood
11-30-2003, 07:03 AM
I suppose we are getting to the question of "What is OLAP?"
An OLAP cube has a set of central fact or facts (measures) that are categorized in a variety of ways (dimensions). In your case, the fact is profit and the dimensions are time (date), trader, strategy and security. Dimensions are hierarchical, ie. Time/Date can be broken down by Year, month, week and day; trader by some management structure ie. location, desk.
The whole point of OLAP is that you want to be able to summarize (in some way - count, sum, average) the facts according to some selection of the hierarchical dimensions. That was why I was suggesting to have the profit figure in your fact table be profit for the day for date, trader, strategy and security. In your Mondrian schema, define the profit measure as:
<Measure name="Profit" column="profit"
aggregator="sum" formatString="#,###.00"/>
To get daily profit, you would run the following MDX:
select {[Measures].[Profit]} on columns,
{([Time].[All Periods].[2003].[November].[Week 3].[15],
[Trader].[All Traders].[John],
[Strategy].[All Strategies].[Converts])} on rows
from TradingProfit;
To get week to date (for current week) or historical weekly profit:
select {[Measures].[Profit]} on columns,
{([Time].[All Periods].[2003].[November].[Week 3],
[Trader].[All Traders].[John],
[Strategy].[All Strategies].[Converts])} on rows
from TradingProfit;
For month to date (for current month) or historical monthly profit:
select {[Measures].[Profit]} on columns,
{([Time].[All Periods].[2003].[November],
[Trader].[All Traders].[John],
[Strategy].[All Strategies].[Converts])} on rows
from TradingProfit;
For year to date (for current year) or historical yearly profit:
select {[Measures].[Profit]} on columns,
{([Time].[All Periods].[2003],
[Trader].[All Traders].[John],
[Strategy].[All Strategies].[Converts])} on rows
from TradingProfit;
I don't know in what context you want to get the daily/monthly/yearly values, but I hope this helps.
Sherman