Any pitfalls with junk dimensions in SSAS?
2 posters
Page 1 of 1
Any pitfalls with junk dimensions in SSAS?
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]
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]
hurrican3dev- Posts : 17
Join date : 2012-08-26
Re: Any pitfalls with junk dimensions in SSAS?
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
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
Re: Any pitfalls with junk dimensions in SSAS?
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
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
Mike Honey wrote: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- Posts : 17
Join date : 2012-08-26
Re: Any pitfalls with junk dimensions in SSAS?
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
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
Re: Any pitfalls with junk dimensions in SSAS?
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.
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.
hurrican3dev- Posts : 17
Join date : 2012-08-26
Similar topics
» Re-Using Junk Dimensions
» Bridge tables versus massive junk dimensions
» Correlated - Separate Dimensions OR Single Dimensions ?
» Schema Roadblock: Dimension vs Fact [Dimensions w/FKs to other Dimensions?]
» Difference between Factless FACT and Type 4 Dimension
» Bridge tables versus massive junk dimensions
» Correlated - Separate Dimensions OR Single Dimensions ?
» Schema Roadblock: Dimension vs Fact [Dimensions w/FKs to other Dimensions?]
» Difference between Factless FACT and Type 4 Dimension
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|