Referenced Relationship Type in SSAS

28
18602
Referenced Relationship Type
Referenced Relationship Type

Friends,

When the Dimension table and the Fact Table(also called as Measure Group Table) are related through some other dimension table  then that type of relationship is called as Referenced Relationship Type. Lets take a small example from AdventureWorks database.

I am taking the following tables to demonstrate Referenced relationship type.

1) FactInternetSales – Fact/Measure group table

2) DimProduct and DimProductSubCategory – Dimension tables

In this case FactInternetSales and DimProduct tables have foreign key relationship on the column “ProductKey” and hence to fetch Product wise sales then the query looks like –

Select EnglishProductName,sum(sales_Amount) from FactInternetSales
INNER JOIN DimProduct on FactInternetSales.ProductKey = DimProduct.ProductKey
Group by EnglishProductName

Now if you wish to get data at Subcategory Level then the query looks like –

Select EnglishProductSubcategoryName,sum(sales_Amount) from FactInternetSales
INNER JOIN DimProduct on FactInternetSales.ProductKey = DimProduct.ProductKey
INNER JOIN DimProductSubcategory on DimProductSubcategory.ProductSubcategoryKey = DimProduct.ProductSubcategoryKey
Group by EnglishProductSubcategoryName

If you observe the above query then it is clear that We are not using any column to display from DIMPRODUCT  table but still we are using it to give reference between DimProductSubcategory table and FactInternetSales tables.

We used INNER JOIN to join FactInternetSales and DimProduct tables and the same relationship can be provided  in SSAS cube which is called as Regular Relationship Type. As we cannot give regular relationship type between FactInternetSales and DimProductSubcategory tables as they dont have KEY to join(in other words not directly related), we can use REFERENCED Relationship type to relate those two tables. If we want to relate the above query with SSAS Cube implementation then DimProduct and DimProductSubcategory tables are Dimension Tables in Cube and FactInternetSales is Fact Table . PFB the screen with out providing relationship between dimension and fact.

Referenced Relationship Type
Referenced Relationship Type

Now lets see the steps to be followed to provide Regular Relationship Type between a dimension(DimProduct) and a Measure Group(FactInternetSales). Use the Define Relationship dialog box to define a relationship between a cube dimension and a measure group in Cube Designer. You can display the Define Relationshipdialog box by clicking … on a cell in the Grid pane on the Dimension Usage tab in Cube Designer.

1) Select the relationship Type as “Regular”.

2) Select the attribute that defines the granularity of the measure group with respect to the dimension. This attribute is usually the key attribute of the dimension.(ProductKey in our case).

3) Then it automatically displays Dimension Table and Fact Table.

4) It also displays the Dimension Table columns in RELATIONSHIP GRID and you have to select appropriate Measure Group Columns.

PFB the screenshot of the same.

Regular Relationship Type
Regular Relationship Type

5) After selecting Measure Group Columns click OK.

We set the relationship between InternetSales and Product using Regular relationship type. Now lets see how to give referenced relationship between FactInternetSales and DimProductSubCategory. Before going to that following things should be in mind while giving referenced relationship type.

1) The dimension which is acting as Intermediate should be created first.

2) The columns that are required to join the fact and second dimension should be selected as  Attributes in the Intermediate dimension. From the above SQL query it is clear that my DimProduct dimension(which acts as bridge between fact and subcategory) requires a min of two columns as attributes and i.e ProductKey to join with Fact and ProductSubcategoryKey to join with DimProductSubcategory Dimension.

Now please find the steps below to give referenced  relationship –

1) Select the relationship Type as “Referenced”.

2) Select the intermediate Dimension(In our case it is Dim Product).

3) Select the attributes from both the dimensions which forms a relationship between these two dimensions(In our case it is ProductSubCategoryKey).

4) Materialize option is checked and leave it as it is.

5) Click on OK.

Referenced Relationship Type
Referenced Relationship Type

That’s it .. It is as easy as this .. Happy Coding !!

Regards,

Roopesh Babu V

28 COMMENTS

  1. Both cause similar side effects, like headaches, flushing, and heartburn cheap cialis online PE is characterized by ejaculation prior to, or soon after, vaginal penetration with minimal stimulation, with the male having no control over ejaculation

  2. Prescription orders dispensed by licensed pharmacies verified by PharmacyChecker. clomid crazies Keep in mind, my DH has had two SA s, three months apart, and the first was 11 million and the second was 12 million both low 20 is considered normal and he was diagnosed as low sperm count.

  3. One of the most unique aspects of transportation in Lancaster County is the covered bridge, which gives travelers a glimpse into the travails of yesteryear. clomid half life And now, it is prohibited to explicitly depict his image in any form.

  4. Thus, some casinos allow you to get wager-free with no deposit, but they ask you to deposit a certain amount to access your winnings. We’ve also collected a list of the best bitcoin casino welcome bonuses and also Casino Level Up Adventure Bonuses, you might want to check them!! BetFury offers several methods for beginners and new player with zero bankroll to earn free crypto. Build your balance using several features – Crypto free boxes, FunFury races, or the Fury wheel. It is not easy to find the top crypto gambling games that are played worldwide on online casinos. However, in this article, we have listed some of the most popular, highly anticipated NFT games players are playing and earning rewards from it. You can try your hands on the variety of blackjack games on offer on this crypto casino site. As the most popular casino card game worldwide, it doesn’t come as a surprise that it enjoys such a high level of popularity at Coinslotty. You can have a lot of fast-paced fun and let your brain have a great workout when you play on Coinslotty. https://amyyuan.ca/community/profile/dennisgosling57/ Bitcoin-accepting poker rooms and poker networks are much smaller than the biggest fiat poker rooms. This size issue adds to the problem, as the fewer players you have at a poker site, the less likely you’re going to get a ring going at a game that’s not Holdem. ‌ You can use Bitcoin (or other cryptocurrencies) no matter where in the world you’re located. When you play in a Bitcoin casino, you won’t have to worry about currency exchanges – you can just play using your Bitcoins.‌ As Bitcoin gains more and more acceptance across the world, the number of top poker sites Bitcoin is valid on will continue to grow. With instant transfers and no transaction fees, Bitcoin will likely become invaluable to serious online poker players. Finding the best Bitcoin poker site can be a challenge. There are dozens of bonuses, rewards and rakeback options to choose from. Not only that, but tournament and ring game selection can vary between sites and the stakes may not always be appropriate. Fortunately for Bitcoin poker players, we’ve collected all of the best sites in the house and compared them for you on this page. Browse our list of the best Bitcoin poker rooms below!

  5. The process for obtaining medical cannabis is the same in Ontario as it is in other Canadian provinces, however, the process is still quite different than the process of getting other types of prescriptions. Medical cannabis in Canada is regulated under the Cannabis Act. Monday to Friday 8:30 am to 4:30 pm (Closed statutory holidays)Due to the COVID-19 pandemic, our office is closed to walk-in traffic until further notice. Additionally, d ue to staff shortages related to COVID-19 and the high volume of calls and emails, our response times may vary. Thank you for your patience. Toronto Sun. WARREN: provinces should set the marijuana age at 21 for now. 2018. torontosun.com opinion columnists warren-provinces-should-set-the-marijuana-age-at-21-for-now. Accessed 3 Nov 2019. https://thedowncastoutcastchristian.com/community/profile/sgokarol4560016/ © 2022 Winnipeg Free Press As mentioned above, SunLife does provide medical cannabis insurance coverage, but only to people employed by a business with the cannabis coverage add-on bundled with their plan. If your employees are using medical marijuana there will be increased liabilities to your business. For example, preventing an employee from using a prescribed medication while at work could result in the employee pursuing a human rights tribunal claim. The resulting legal fees and or expenses to defend a human rights claim are not included under standard Commercial General Liability policies. A separate Legal Expense policy including Employment Practices Wrongful Acts coverage may be required. “Medical marijuana has become a very important part of their treatment program and pain management program,” said Connor, referring to patients who have cancer, multiple sclerosis, rheumatoid arthritis, or those requiring palliative care.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

8 + 2 =