Any pitfalls with junk dimensions in SSAS?

[hurrican3dev]

Hi,

I am planning on using a Junk Dimension to support a fact table, where there many indicators
related to the fact I need to support. They are mostly Y/N flags.

Any pitfalls I need to know about? These should aggregate normally in the cube I am hoping.


Thanks

Below is an example of the junk dimension.



CREATE TABLE [dbo].[DimJunk](
[JunkDimensionKey] [int] IDENTITY(1,1) NOT NULL,
[JunkAlternateKey] [int] NOT NULL,
[Indicator1] [varchar](2) NOT NULL,
[Indicator2] [varchar](2) NOT NULL,
[Indicator3] [varchar](2) NOT NULL,
[Indicator4] [varchar](2) NOT NULL,
[Indicator5] [varchar](2) NOT NULL,
[Indicator6] [varchar](2) NOT NULL,
[Indicator7] [varchar](2) NOT NULL,
[Indicator8] [varchar](2) NOT NULL,
CONSTRAINT [PK_DimJunk_JunkDimensionKey] PRIMARY KEY CLUSTERED
(
[JunkDimensionKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

[Mike Honey]

Hi hurrican3dev

For SSAS I prefer to load indicators with more descriptive values e.g. instead of a Key_Customer_Indicator with values Y/N, I would use values 'Key Customer' / 'Not a Key Customer' (and possibly 'Unspecified') . I find this avoids confusion in the client tools where users can lose track of which Y/N Indicator they are looking at. It's also more flexible as you can have more than two states per Indicator with minor effort.

Technically you can build Junk Dimensions in SSAS directly off the original Fact table indicators, but this is best avoided IMO as it is painful to maintain (the dimension key is made of all the indicators) and can be slow to process (your example would generate 8 "SELECT DISTINCT ..." queries across the entire Fact table).

BTW your int PK should probably be a bigint - int will only give you 15 indicators before it blows up.

Good luck!
Mike

[hurrican3dev]

Mike,

Great feedback. I will definitely give the indicators more specific names and use more specific values other than Y/N to populate (based on the indicator type).

Some of my facts table are going to have dozens of related indicators, so I think a junk dimension is the only way to go. I've just never
seen it used before (new to DW'ing SSAS) and wanted to see if it would aggregate measures correctly along with other dimensions.
So the indicators will not be on the fact at all, just stored in the dimension.

Just looking to get the "tires kicked" on this concept. Is this a common approach (dare I say best practice)?

Thanks,

The Hurricane

Hi hurrican3dev

For SSAS I prefer to load indicators with more descriptive values e.g. instead of a Key_Customer_Indicator with values Y/N, I would use values 'Key Customer' / 'Not a Key Customer' (and possibly 'Unspecified') . I find this avoids confusion in the client tools where users can lose track of which Y/N Indicator they are looking at. It's also more flexible as you can have more than two states per Indicator with minor effort.

Technically you can build Junk Dimensions in SSAS directly off the original Fact table indicators, but this is best avoided IMO as it is painful to maintain (the dimension key is made of all the indicators) and can be slow to process (your example would generate 8 "SELECT DISTINCT ..." queries across the entire Fact table).

BTW your int PK should probably be a bigint - int will only give you 15 indicators before it blows up.

Good luck!
Mike

[Mike Honey]

Hi The Hurricane,

Yes you are on the right track. Aggregation will work fine as long as you can assign each fact row to the correct junk dimension row.

Junk dimensions are rarely conformable so you may end up with one per fact.

Mike

[hurrican3dev]

thanks again Mike. incredibly helpful.


since the OLTP system I am building a warehouse for is chock full of check-boxes and yes-no indicators this
pretty significant in terms of opening the floodgates for a modeling solution.

[Sponsored content]