cube with monthly data chunks not working

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

cube with monthly data chunks not working

Author

Topic

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2008-04-03 : 17:25:41

I have a table based around requisitions, and each requisition has a number of positions. That number can change over time through updates to pertinent rows rather than through transaction-like records that record an entire history, and I'm only able to get a monthly snapshot of the table. What I decided to do is still use one table for OLAP (fact_requisitions) but add a column called period_key that refers to the month the data comes from. So if I have two months of data then the table has each requisition twice, possibly with differing position counts, and new requisitions from the second month are only present once. Then I tried to filter the MDX query like so:

SELECT {
([Dim TimeRequestClosed].[Year - MonthNumber].[Year_Text].&[2008].&[1],[Dim Requisitions].[Period].[Period Key].&[200801])
}
ON COLUMNS,
NON EMPTY
{
([Dim Location].[Region Name].MEMBERS, [Dim Location].[Period Key].&[200801])
}
ON ROWS
FROM
[Requisitions]
WHERE
[Measures].[Request Closed Date Count]

This query doesn't work even though the data is there, it just returns nulls. Am I going about this all wrong? If not, what might I be doing wrong, and how would I get the query to return more than one period (e.g. tell Dim Requisition to match up with Dim Location on the period key)?

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2008-04-03 : 17:51:03

I figured out that I get the correct results if I take out the filter: [Dim Requisitions].[Period].[Period Key].&[200801]
I have no idea why that is, and I still don't know how to combine January and February (Feb = 200802).

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2008-04-04 : 17:07:10

I got it working, but I still don't know if I chose the best solution. I added a dimension called Dim Period, with period_key as its only column, then I linked all the other dimensions to it in the DSV. The queries ended up looking like this:

SELECT {
([Dim TimeRequestClosed].[Year - MonthNumber].[Year_Text].&[2008].&[1],[Dim Period].[Period Key].&[200801]),
([Dim TimeRequestClosed].[Year - MonthNumber].[Year_Text].&[2008].&[2],[Dim Period].[Period Key].&[200802])
}
ON COLUMNS,
NON EMPTY
{
[Dim Location].[Region Name].MEMBERS
}
ON ROWS
FROM
[Requisitions]
WHERE
[Measures].[Request Closed Date Count]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources