April 28, 2010 at 10:57 am
I am trying to use a dimension attribute to calculate the average length of time a job has been in a particular status. The key items:
I am using SQL 2005 STANDARD edition.
The fact table has a field "LastStatusUpdate" that joins to the Calendar Table, which contains the date and an integer field called DaysPassed. The DaysPassed field is updated daily. Using a SQL query, I can calculate the average easily with AVG(DaysPassed). I am not able to do this in SSAS, however, even though I've made DaysPassed a dimension property. There doesn't seem to be anyway to get SSAS to recognize the property as an integer that can be divided by the record count. Several of the possible values, such as AvgOfChildren, are unavailable to me because I'm not using the Enterprise version.
For the moment I've added a field to the fact table that updates with the value in DaysPassed, but I'd like to be able to solve this properly if it can be solved with the Standard version.
Does anyone have a solution? If more information is needed, please let me know.
May 3, 2010 at 2:29 am
Hi Ron,
I'm not sure that I can help you out but I've something in my head which I think is worth a try. At least my idea is not dependent on the semiadditive aggregrates in Enterprise Edition.
Just to get a clear picture, how do you in SQL calculate the AVG(DaysPassed). Can you post the SQL query ?.
Regarding the cube: What does the fact table contain, or in Kimall words: What is the grain ?
1 record for each job (?) for each calendarday (?) for each ..... or something else ?
Kind regards,
Cees
May 6, 2010 at 10:54 am
Here is a much reduced version of the problem with the SQL solution.
CREATE TABLE dtblCalendar (
intDateID int NOT NULL PRIMARY KEY,
dtmDate smalldatetime NOT NULL,
intDaysPassed smallint NULL
);
GO
CREATE TABLE ftblJob (
intJobNo int NOT NULL PRIMARY KEY,
intLastUpdated int NOT NULL,
CONSTRAINT FK_ftblJob_dtblCalendar FOREIGN KEY (intLastUpdated)
REFERENCES dtblCalendar(intDateID)
);
GO
INSERT dtblCalendar
VALUES (1501, '5/1/10', 5)
GO
INSERT dtblCalendar
VALUES (1502, '5/2/10', 4)
GO
INSERT dtblCalendar
VALUES (1503, '5/3/10', 5)
GO
INSERT dtblCalendar
VALUES (1504, '5/4/10', 2)
GO
INSERT dtblCalendar
VALUES (1505, '5/5/10', 1)
GO
INSERT dtblCalendar
VALUES (1506, '5/6/10', 0)
GO
INSERT ftblJob
VALUES (1001, 1501)
GO
INSERT ftblJob
VALUES (1002, 1503)
GO
INSERT ftblJob
VALUES (1003, 1505)
GO
INSERT ftblJob
VALUES (1004, 1505)
GO
INSERT ftblJob
VALUES (1005, 1504)
GO
INSERT ftblJob
VALUES (1006, 1502)
GO
INSERT ftblJob
VALUES (1007, 1506)
GO
INSERT ftblJob
VALUES (1008, 1501)
GO
INSERT ftblJob
VALUES (1009, 1505)
GO
INSERT ftblJob
VALUES (1010, 1501)
GO
SELECT AVG(CAST(intDaysPassed AS decimal))
FROM ftblJob j
JOIN dtblCalendar c
ON j.intLastUpdated=c.intDateID
GO --2.9
May 6, 2010 at 2:13 pm
Hi Ron,
Thank for the sql scripts, i just executed them. Maybe you consider dtblCalendar to be the fact-table, but as I see it the facts should be based on [intJobNo]. The intLastUpdate is just a reference which can be used to determine the intDaysPassed for each job's, right ?
In this simple scenario, as I see it, you have 1 fact and 2 dimension-tables and no need for any complexity in the cube.
Fact-table: Fact_Jobs with columns intJobId and (measure) intDaysPassed. Th fact tables granularity : 1 record per Job.
Dim-table: DimDate with columns intDateID and (descriptive) dtmDate
Dim-table: DimJob with intJobNo and possibly other Job-related attribute
For the cube you simply relate the fact-table to the dim-tables. The Avg(DaysPassed) can then be defined as sum(intDaysPassed)/ Count of records in Fact_Jobs. This can be definitely be done in standard ed.
Kinf regards,
Cees
May 6, 2010 at 4:24 pm
dtbl is a dimension table. So the problem is that I have a value in the dimension that I want to use in a fact table calculation. The SQL statement is easy because at that level they are just both tables. MDX is another matter, however, because in SSAS calendar is a dimension and jobs is a cube. I should be able to make this work using some techniques in my MDX book, but in practice nothing I've tried works. I'm hoping you or someone else has already solved this.
May 6, 2010 at 8:28 pm
If you've already got a process updating the dayspassed, is there any reason why you can't change your fact to a view, and simply bring the dayspassed into the fact that way? If you can't basically the same approach is to bring it in (to the fact) via the DSV. Assuming (and it may be a bad assumption based on a simple example) that each job exists only once in the fact, then using the AVG rollup for that measure should get you the result you're looking for.
Steve.
May 7, 2010 at 5:51 am
Steve,
Each job appears only once in the fact, as that is that cube's level of granularity, so it's not a bad assumption.
I have never based a cube on a view before. If I were designing the cube from scratch, I would try that, as it seems as if it should work. Unfortunately my development cube is also the production cube, and I don't want to risk making things worse by changing the data source since I have a work around for it.
I really like this idea, however, and in the future, I may experiment with creating a cube based on a view, even if it's just a table in the beginning, to gain that flexibility in the future.
May 7, 2010 at 7:17 am
If you can't/won't use a view, why not use the DSV approach? A little known feature (well, little known *to me* 🙂 ) is the ability to reference a field from a secondary table, e.g your fact table in the DSV can have a calculated field added to it that returns the value from the dimension table.
If your prod is your dev, why not create a quick copy and do your tests against that? Could be as easy as backing up the SSAS DB and restoring with a new name, or scripting it out and running the create with a new name etc.
Steve.
May 7, 2010 at 7:48 am
I'm not against using a view, just not yet. I think it's a very interesting idea. I'm not a big fan of views (prefering stored procedures), but in this circumstance they might just be the trick.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply