SSAS Referenced Dimension

Question

Post reply

SSAS Referenced Dimension

ktflash

Default port

Points: 1400
More actions
August 17, 2021 at 5:45 am

#3920612

Ahoi,
i have been looking at the different relationship options the Multidimensional Model offers, because i only have the Regular and the M:N ones in actual use.
The one that confuses me is the Referenced Dimension type.
From my understand it allows connecting a Dimension and Facts by using an intermediate Dimension which has a foreign key to the other dimension. (1 Fact, 2 Dimensions)
So you basically have a Customer and a Geography Dimension and the Customer intermediate Dimension allows connecting InternetSales and Geography.
What i dont understand is that you can create a Customer Dimension that also directly has the Geography information. So bascially add the Geography columns to the Customer Dimension and only have one Dimension with all information. (1 Fact, 1 Dimension) This is what a consultant once told me once, to have all information in the least amount of dimensions possible for best performance.
Anyone has any ideas what the advantages/disadvantages of these 2 approaches are?
I want to be the very best
Like no one ever was
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
August 17, 2022 at 11:22 am

This was removed by the editor as SPAM
September 26, 2022 at 3:36 pm

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

KevinYang SSC Enthusiast Points: 162 More actions · Answer 2

I have similar issue, waiting for solution

you can go[/url] for more information about study

PB_BI SSCoach Points: 17461 More actions · Answer 3

ktflash wrote:

What i dont understand is that you can create a Customer Dimension that also directly has the Geography information. So bascially add the Geography columns to the Customer Dimension and only have one Dimension with all information. (1 Fact, 1 Dimension) This is what a consultant once told me once, to have all information in the least amount of dimensions possible for best performance.
Anyone has any ideas what the advantages/disadvantages of these 2 approaches are?

You're talking about (I think) denormalisation but doing it in SSAS. Creating a sort of "god" dimension and relating it back to one mega measure group. Great....until you process it. You'll take a big hit this way. What problem are you specifically trying to solve?

Referenced dimensions (which is the other option I think you're asking about) have their own set of cons, written up here very nicely: https://sqldusty.com/2013/01/25/gotchas-with-referenced-dimensions/

With this approach you'll more than likely hit performance problems further down the chain, in your MDX queries.

What the "consultant" told you ("least amount of dimensions as possible for best performance") is, at best, an "it depends" issue. There are a lot of design patterns that can be used in SSAS that can improve performance in certain areas but it has to be tailored to a specific problem, there is no one hard and fast rule.

This reply was modified 2 years, 7 months ago by PB_BI.

I'm on LinkedIn