Any pitfalls with junk dimensions in SSAS?

View previous topic View next topic Go down

Any pitfalls with junk dimensions in SSAS?

Post  hurrican3dev on Sun Nov 18, 2012 8:18 pm

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]





hurrican3dev

Posts : 17
Join date : 2012-08-26

View user profile

Back to top Go down

Re: Any pitfalls with junk dimensions in SSAS?

Post  Mike Honey on Mon Nov 19, 2012 6:32 pm

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
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: Any pitfalls with junk dimensions in SSAS?

Post  hurrican3dev on Tue Nov 20, 2012 10:17 am

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

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

View user profile

Back to top Go down

Re: Any pitfalls with junk dimensions in SSAS?

Post  Mike Honey on Tue Nov 20, 2012 5:30 pm

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
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: Any pitfalls with junk dimensions in SSAS?

Post  hurrican3dev on Wed Nov 21, 2012 5:05 pm

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.


hurrican3dev

Posts : 17
Join date : 2012-08-26

View user profile

Back to top Go down

Re: Any pitfalls with junk dimensions in SSAS?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum