Referenced Relationship Type in SSAS

By
roopesh.valluru
-
568
23590
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

RELATED ARTICLESMORE FROM AUTHOR

568 COMMENTS

  23. mexican border pharmacies shipping to usa [url=http://mexicopharm.pro/#]buying from online mexican pharmacy[/url] reputable mexican pharmacies online

  26. mexican pharmaceuticals online [url=http://mexicopharm.pro/#]medicine in mexico pharmacies[/url] best online pharmacies in mexico

  29. online shopping pharmacy india [url=http://indiapharm.pro/#]trusted india online pharmacies[/url] india pharmacy

  32. indian pharmacy paypal [url=https://indiapharm.pro/#]trusted india online pharmacies[/url] buy medicines online in india

  53. canadian pharmacy store [url=http://pillswithoutprescription.pro/#]canadian online pharmacy no prescription[/url] price prescriptions

  58. canadian prescription filled in the us [url=http://pillswithoutprescription.pro/#]canadian online pharmacy no prescription[/url] compare prescription drug prices

  61. cheapest ed pills [url=http://edpills.pro/#]over the counter erectile dysfunction pills[/url] erection pills online

  63. india pharmacy [url=http://indianpharmacy.pro/#]pharmacy website india[/url] online shopping pharmacy india

  67. Online medicine order [url=http://indianpharmacy.pro/#]world pharmacy india[/url] best india pharmacy

  91. how to buy cialis online [url=https://cialiswithoutprescription.pro/#]buy Tadalafil 20mg[/url] generic cialis 20 mg

  102. how much is a cialis prescription with no insurance [url=https://cialiswithoutprescription.pro/#]how much is cialis without insurance[/url] cialis online canada paypal

  108. how to get cytotec online [url=https://cytotecpills.pro/#]buy cytotec pills[/url] cytotec abortion pill

  114. best pills for ed [url=https://edpillsfd.com/#]ed meds online without doctor prescription[/url] online ed medications

  117. cheapest ed pills online [url=https://edpillsfd.online/#]buy ed pills online[/url] ed meds online

  123. legitimate online pharmacy usa [url=http://pharmfd.com/#]trusted online pharmacy reviews[/url] online pharmacy viagra

  128. These activities are consistent with constitutive expression of Cyp1b1 in endothelial cells, and more predominantly in perivascular supporting cells, which maintain the cellular oxidative state especially under hyperoxic conditions buy cialis and viagra online Five Star Reviews from Amazon

  129. ed pills that work [url=https://edpillsfd.com/#]ed pills gnc[/url] erectile dysfunction pills

  131. pharmacy rx [url=https://pharmfd.online/#]online pharmacy no prescription[/url] canada online pharmacy no prescription

  134. buy propecia no prescription [url=https://propecia.pro/#]where can i get propecia for sale[/url] where can i buy generic propecia for sale

  139. buy kamagra online [url=https://kamagratabs.pro/#]Oral Jelly 100mg Kamagra price[/url] Kamagra 100mg

  146. can you buy propecia for sale [url=https://propecia.pro/#]can i buy propecia without rx[/url] can you buy cheap propecia no prescription

  170. reputable mexican pharmacies online [url=http://mexicanpharmacies.pro/#]п»їbest mexican online pharmacies[/url] best online pharmacies in mexico

  174. world pharmacy india [url=http://indiameds.pro/#]pharmacy website india[/url] indian pharmacy

  177. canada drugs [url=https://canadapharmcertified.pro/#]the canadian drugstore[/url] canadian pharmacy uk delivery

  181. levitra from canadian pharmacy [url=https://fastpills.pro/#]prescription drugs without the prescription[/url] buy prescription drugs without doctor

  201. can i get generic mobic pills [url=https://mobic.store/#]can i buy cheap mobic[/url] where can i get cheap mobic tablets

  205. amoxicillin over counter: [url=https://amoxicillins.com/#]amoxicillin order online[/url] generic amoxicillin

  238. can you buy zithromax over the counter in canada [url=https://azithromycin.men/#]zithromax buy online no prescription[/url] zithromax 500 mg for sale

  256. canadian drug prices [url=https://certifiedcanadapills.pro/#]canadianpharmacymeds[/url] best online canadian pharmacy

  262. To announce present dispatch, follow these tips:

    Look for credible sources: http://valla-cranes.co.uk/wp-content/pages/who-left-fox-13-news.html. It’s high-ranking to secure that the expos‚ outset you are reading is reliable and unbiased. Some examples of reliable sources include BBC, Reuters, and The New York Times. Read multiple sources to stimulate a well-rounded view of a particular statement event. This can improve you listen to a more over paint and keep bias. Be hep of the perspective the article is coming from, as set good telecast sources can be dressed bias. Fact-check the low-down with another origin if a scandal article seems too sensational or unbelievable. Till the end of time make sure you are reading a known article, as news can substitute quickly.

    By means of following these tips, you can evolve into a more informed news reader and more intelligent be aware the world around you.

  332. doxycycline 1mg [url=https://doxycyclineotc.store/#]doxycycline canadian online pharmacy[/url] doxycycline pills online

  348. mexican mail order pharmacies [url=https://mexicanpharmacy.site/#]best online pharmacy in Mexico[/url] mexican online pharmacies prescription drugs

  360. MA: In other words, Samson does not think that crypto is going to help spread the wealth. And there’s actually early economic research to suggest the wealth already in crypto is pretty concentrated. According to this one study that looked at Bitcoin, they have the .01%. The researchers estimated just 1 100 of a percent of bitcoin investors held more than a quarter of all the bitcoin in circulation. Indeed, cryptos like Bitcoin are now back to levels last seen in December 2017. Even those that have held Bitcoin since then have little to show for it for a close to five-year investment. If this market continues to consolidate and slide, the hit to retail investors will be quite large. This will impact their financial stability and future consumption.
    http://kyeyangencplus.co.kr/bbs/board.php?bo_table=free&wr_id=881148
    @CryptoTaxHQ I evaluated a few different solutions for doing my @arbitrum (+ other crypto) taxes and you guys had the best Arbitrum support. I filed this weekend after using CryptoTaxCalculator to generate the necessary reports and am quite confident in their accuracy! It is important to note if you have multiple digital wallets in which you store your cryptocurrency, transferring from one to another is tax-free. However, where you pay for transfer fees in cryptocurrency, these transactions will constitute disposals and may be taxable.  The only way to legally avoid paying taxes on crypto is to relocate to a country that does not tax crypto. Check out our article on Top Tax Friendly Crypto Countries to learn which countries have favourable crypto tax treatment. It is not advisable to try and hide crypto taxes as many exchanges now enforce KYC and have agreements with local tax authorities.

  364. mail order pharmacy india [url=http://indianpharmacy.life/#]buy medicines from India[/url] top online pharmacy india

  393. Independent. Since 2003 Download Digi Online app for Windows 10, 8 However, not all the Digi TV channels are present with the app, as the rest of them is probably expected to come with future updates. However, right now you can use the Windows 10, 8 Digi Online app to watch live such important sports events as the UEFA Champions League, UEFA Europa League, MotoGP, Formula 1, Formula 3 and plenty more. The TV Digi channels are all there: Digi Sport 1,2, 3; Digi24, Digi Film, Digi World, Digi Life, Digi Animal World and UTV. Prin intermediul tvcanale.live, poți accesa programe de televiziune în timp real, inclusiv filme, seriale, documentare și evenimente sportive, fără a fi nevoie să plătești costuri suplimentare. Această alternativă vine în ajutorul celor care întâmpină probleme cu alte platforme de streaming, precum TVRON, tvhd-online sau Cool Tv, care se pot bloca sau funcționa defectuos.
    https://pharmesthetic.com/bbs/board.php?bo_table=free&wr_id=1955115
    Football | Hockey | Tennis | Basketball | Handball | Volleyball | Baseball | Am. football | Rugby Union | More sports » All Games | LIVE | Finished | Odds Audience Geography. Where are livescores.biz visitors located? Bet on Soccer from your mobile with 1xbet! Server IP address resolved: YesHttp response code: 200Response time: 0.79 sec.Last Checked: 09 22 2023 Live score, Livescore today, mobile livescore, soccer livescor, fscore, yesterday livescor… Audience Geography. Where are livescores.biz visitors located? REFRESH Football | Basketball | Volleyball | Handball | Tennis | Badminton | Cricket | Baseball | More sports » REFRESH REFRESH Soccer | Basketball | Tennis | Ice hockey | Field hockey | Handball | Volleyball | Baseball | Am. football | Rugby Union | Cricket | More sports »

  435. reputable indian online pharmacy: india online pharmacy – buy medicines online in india indiapharmacy.pro
    world pharmacy india [url=https://indiapharmacy.pro/#]best online pharmacy india[/url] indian pharmacies safe indiapharmacy.pro

  437. maple leaf pharmacy in canada: canadian drugs – canadian pharmacy online canadapharmacy.guru
    online canadian pharmacy review [url=http://canadapharmacy.guru/#]canadian pharmacy prices[/url] legitimate canadian pharmacies canadapharmacy.guru

  494. tadalafil for sale from india [url=https://tadalafil.trade/#]tadalafil price uk[/url] tadalafil tablets 20 mg online

  498. sildenafil prescription prices [url=https://sildenafil.win/#]sildenafil tablets from india 100mg tablets[/url] generic sildenafil coupon

  501. sildenafil fast delivery [url=https://sildenafil.win/#]sildenafil tablets 120mg[/url] sildenafil over the counter india

  506. 875 mg amoxicillin cost [url=http://amoxicillin.best/#]buy amoxil[/url] amoxicillin 500 mg purchase without prescription

  510. where can i buy zithromax medicine [url=https://azithromycin.bar/#]generic zithromax over the counter[/url] order zithromax over the counter

  512. amoxacillian without a percription [url=http://amoxicillin.best/#]purchase amoxicillin online[/url] amoxicillin 500mg

  516. amoxicillin medicine over the counter [url=https://amoxicillin.best/#]cheap amoxicillin[/url] where can i get amoxicillin 500 mg

  521. zithromax purchase online [url=http://azithromycin.bar/#]zithromax antibiotic without prescription[/url] zithromax capsules

  533. online canadian pharmacy review [url=http://canadiandrugs.store/#]trust canadian pharmacy[/url] canadian pharmacy online ship to usa

  560. farmacie online affidabili [url=http://kamagrait.club/#]kamagra gold[/url] farmacia online piГ№ conveniente

LEAVE A REPLY

Please enter your comment!
Please enter your name here

61 − 59 =