December 2, 2004 at 12:51 am
Dear all,
I need a fact table to store an actual data and year-to-date data. For example, on March I stored the actual production for this month as an actual production and I stored the cummulative from January to March as year-to-date data. Can I use one fact table for both data? FYI, curently I stored the actual and year-to-date data on a different fact table, thus on a different cube.
Thx
December 2, 2004 at 6:49 am
If this Year to date data is just so you can load it in a cube you don't need it, Analysis Services has a Year to Date function that will calculate it for you.
December 2, 2004 at 11:30 pm
I Found YTD function on Analysis Services which return a set of time to current time so I can get a set of time period that I want (ex: Jan 2004 - March 2004). I want to create a calculated member to calculate unit sales measure for the series of time. Which function should I use?
December 7, 2004 at 6:23 pm
A basic entry point is to use the YTD in conjunction with a SUM() function, but this may not produce correct results where your measures aren't summed (e.g. distinct counts).
If you google YTD +MDX you'll get a lot of listings returned for help with this. TO get you started, this is a link to an article from 2000 (still relevant thought) where the first code sample will get you started (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbpj00/html/complex.asp) .
Steve.
February 11, 2005 at 6:42 am
In RE your original question: "...a fact table to store an actual data and year-to-date data."
From a dimensional modeling perspective this is possible but not recommended as you are mixing the fact table grains. You really need two different types of fact tables or, as has been previously advocated, you summarize the transaction actual data information within the cube.
If your goal is to perform point in time analysis such as the balance of X account was Y at Z then you do need the multiple fact table design strategy as the cube will be constantly updated with new activity rendering point in time analysis of a transaction type fact table impossible.
The multiple fact table type approach:
The first, a table to store actual data, is a transaction fact table in which the fact table granularity is one row for the lowest level of detail captured by a transaction. A record is present only if a transaction event actually occurs.
The second, a table to store year-to-date data, is either a Periodic Snapshot Fact Table or it is a Accumulating Snapshot Fact Table. The Periodic Sanpshot Fact Table represents business performance at the end of each regular, predictable time period. A separate record is placed in the fact table each period regardless of whether any activity has taken place. An Accumulating Snapshot Fact Table has multiple dates representing the major milestones of a relatively short-lived process or pipeline. This table is updated as activity occurs and a record is placed in the table just once when the item that it represents is first created.
Best of Luck!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply