I am a general, non-SSAS, non-DBA software architect and complete newbie to SSAS who is having to fill in for an SSAS DBA who has just left the company.  Having watched the videos for dimensions and attribute hiearchies, I am still having problems applying the concepts to a particular application I'm building.

This application provides trending information for intrusion threats based on warning messages from firewalls, intrustion detection systems, monitors, etc.  It will be explored manually and will also be accessed via MDX queries from a rules engine that runs every 5 minutes to evaluate the most recent threats against recent trends.  There are hundreds to thousands of such messages per second.

My requirements include being able to look at event counts on two unrelated time bases.   At the lowest levels, these are all based on 60, 15, and 5 minute intervals.   But I also need to look at them on day of the week and on 'WorkDayType'- ie: weekday, weekend, (and possibly later holiday, etc.).  So DayNumberOfWeek will not necessarily roll up into WorkDayType.

The rules engine needs to access these time periods and do things like "Send an alert if a particular event is happening more frequently for this particular period than the rolling average of the most recent 30 such periods".

To accomodate these, I built a DimTime dimension into the DataWarehouse relational tables as:

CREATE TABLE [dbo].[DimTime] (
    [DayNumberOfWeek] [tinyint] NOT NULL,
    [TimeKey] int NOT NULL,
    [FullTimeAlternateKey] [time](3) NULL,
    [WorkDayPlusTimeKey] [int] NOT NULL,
    [DayOfWeekPlusTime] [varchar] (19) NOT NULL,
    [WorkDayPlusTime] [varchar] (16) NOT NULL,
    [5MinInterval] [time](3) NOT NULL,
    [15MinInterval] [time](3) NOT NULL,
    [60MinInterval] [time](3) NOT NULL,
    [EnglishDayNameOfWeek] [varchar] (10) NOT NULL,
    [WorkDayType] [tinyint] NOT NULL,
    [WorkDayTypeName] [varchar](7) NOT NULL,
    CONSTRAINT DimTime_TimeKey_PK PRIMARY KEY CLUSTERED (TimeKey),
    CONSTRAINT DimTime_FullTimeAlternateKey_AK UNIQUE NONCLUSTERED (DayNumberOfWeek, FullTimeAlternateKey)
) ON [PRIMARY];

The TimeKey is the day number of the week plus the ISO 8601 time-to-the-minute.   So Monday at 11:05:03am is 2110503.

My confusion is about three points:

1.  I tried building two different hierarchies from this same DimTime table:  One with the DayNumberOfWeek/60min/15min/5min/TimeKey attributes and one with WorkDayType/60Min/15Min/5Min intervals/TimeKey. 

From your videos, it seems like I must define compound keys for these levels similar to your demo of compounding year number and quarter number to keep quarters rigidly associated with their proper year numbers.

But setting up the compound key fields for one hierarchy seems to conflict with setting them up for the other since they involve the same attributes as levels except for the top one.   Must I build two separate DimTime View dimensions instead of building both hierachies from the same one?   Or do I need to build two separate cube dimensions?   Or can this be done with the same, one cube and view dimension?

2.  To use MDX functions such as AVG(LastPeriods, xx) to get rolling averages, it would seem that I need to categorize DimTime as a 'Time' dimension.   But if I do, and try to use the 'Define Dimensional Intelligence' wizard to help with this, there is no built-in attribute type that seems to apply to a 15 minute or 5 minute time interval.   Is 'Attribute Type' required for this type of usage?   Should this DimTime instead be a 'Regular' type?  If so, will "Last Periods" still work?

3.  When I try to use the 'Attribute Relationships' tab to define multiple relationship paths, I can easily define the relationships for one hiearchy.  But when I try to add a new relationship to define the second one, I get an error that 'a relationship already exists between [the two attributes I'm trying to relate]'.  Is this just another indication that I need to create multiple cube or view dimensions from the same source data; one for each hiearchy?

Thanks!


 

Thank you.   The QH2 video indeed made the process of defining multiple different hiearchies with compound keys referring to the same attributes clear.  (Your videos really are quite good BTW - by far the best resource I've found on the web for learning this interest area).  After watching QH2, I had no problem creating the dual hierarchies.

I do have a question about the proper setting for the attribute Type property of the three nnMinInterval attributes, though.   The property editor will not let me set them simply to 'Time'.   It wants what seems to be a granularity.    I populated the data for these Interval attributes as the start minute of the interval expressed in the DataWarehouse DimTable as a  full hh:mm:ss SqlDbTypes.Time,3.     So the TimeKey for alert message time 03:26:01.020 would contain a 60MinInterval=03:00:00, a 15MinInterval=03:15:00, and a 5MinInterval=03:25:00.

I assume the attribute Type should be 'Minutes' and have set it that way.  But I'd like to confirm that this will not mislead the analysis engine since none of these intervals will ever be populated on a minute-by-minute basis.  I don't want to later discover, for example, that any rolling average I calculate will somehow be based on having 60 such intervals per minute, 3,600 per hour, etc.  Is 'Minutes' correct?

Can you please point me to that video since I am working on a similar project?

Thanks

Lazalo