Referenced Relationship Type in SSAS

By
roopesh.valluru
-
554
48651
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

554 COMMENTS

  166. Игровые автоматы делают вечер незабываемым.: balloon game – balloon игра

  508. п»їJust now, I came across a helpful resource concerning safe pharmacy shipping. It details the safety protocols for generic meds. For those interested in cheaper alternatives, take a look: п»їmore info. Might be useful.

  509. For those looking to save on pharmacy costs, I recommend checking this report. It reveals shipping costs. Good deals found here: п»їhttps://polkcity.us.com/# mexican pharmacies.

  510. п»їRecently, I stumbled upon a useful resource concerning safe pharmacy shipping. The site discusses how to save money for ED medication. In case you need cheaper alternatives, go here: п»їUpstate Medical. Good info.

  511. п»їLately, I came across a great resource concerning Indian Pharmacy exports. It explains CDSCO regulations when buying antibiotics. If anyone wants cheaper alternatives, visit this link: п»їhttps://kisawyer.us.com/# indianpharmacy com. Worth a read.

  512. п»їTo be honest, I found a useful page about Mexican Pharmacy standards. It details FDA equivalents for ED medication. If you are looking for affordable options, take a look: п»їpurple pharmacy mexico. Good info.

  513. Stop overpaying and save on pharmacy costs, I suggest checking this page. It reveals shipping costs. Best prices found here: п»їhttps://polkcity.us.com/# mexican medicine.

  514. Sharing, a helpful guide on buying meds safely. It breaks down the best shipping methods for generics. Full info: п»їhttps://polkcity.us.com/# mexico prescription online.

  516. п»їActually, I came across an interesting page regarding ordering meds from Mexico. It explains how to save money for generic meds. If you are looking for affordable options, take a look: п»їpolkcity.us.com. Good info.

  517. For those looking to save money on prescriptions, I recommend checking this report. It reveals shipping costs. Good deals found here: п»їhttps://polkcity.us.com/# mexican pharmacies that ship to us.

  519. п»їJust now, I found a useful page regarding ordering meds from India. The site discusses the manufacturing standards on prescriptions. For those interested in cheaper alternatives, visit this link: п»їpharmacy website india. Cheers.

  520. п»їTo be honest, I came across a helpful guide regarding Indian Pharmacy exports. It explains how to save money when buying antibiotics. For those interested in reliable shipping to USA, take a look: п»їkisawyer.us.com. Worth a read.

  522. п»їJust now, I stumbled upon an interesting guide concerning ordering meds from India. It covers the manufacturing standards for generic meds. For those interested in reliable shipping to USA, take a look: п»їpharmacy website india. Good info.

  527. Canlı casino oynamak isteyenler için kılavuz niteliğinde bir site: canlı casino siteleri Hangi site güvenilir diye düşünmeyin. Editörlerimizin seçtiği bahis siteleri listesi ile sorunsuz oynayın. Detaylar linkte.

  528. Selam, ödeme yapan casino siteleri bulmak istiyorsanız, hazırladığımız listeye mutlaka göz atın. Lisanslı firmaları ve fırsatları sizin için listeledik. Güvenli oyun için doğru adres: güvenilir casino siteleri iyi kazançlar.

  529. п»їHalo Bosku, lagi nyari situs slot yang hoki? Rekomendasi kami adalah Bonaslot. Winrate tertinggi hari ini dan pasti bayar. Isi saldo bisa pakai Pulsa tanpa potongan. Daftar sekarang: п»їhttps://bonaslotind.us.com/# Bonaslot slot salam jackpot.

  530. Greetings, Lately stumbled upon the best source from India for affordable pills. If you need ED meds at factory prices, this site is the best place. It has secure delivery to USA. Check it out: India Pharm Store. Cheers.

  532. Hello, Lately discovered a great source from India for cheap meds. If you want to buy ED meds safely, this site is very reliable. It has secure delivery guaranteed. More info here: safe indian pharmacy. Hope it helps.

  533. Dostlar selam, bu site kullanıcıları adına kısa bir duyuru paylaşıyorum. Bildiğiniz gibi site adresini tekrar değiştirdi. Giriş hatası varsa endişe etmeyin. Çalışan siteye erişim adresi şu an burada: Vaycasino Kayıt Paylaştığım bağlantı ile doğrudan siteye girebilirsiniz. Güvenilir bahis keyfi için Vay Casino doğru adres. Herkese bol şans dilerim.

  535. Dostlar selam, bu popüler site oyuncuları için kısa bir duyuru yapmak istiyorum. Herkesin bildiği üzere site adresini erişim kısıtlaması nedeniyle tekrar güncelledi. Giriş problemi yaşıyorsanız çözüm burada. Yeni siteye erişim bağlantısı şu an burada Casibom Yeni Adres Bu link ile direkt hesabınıza girebilirsiniz. Ayrıca kayıt olanlara verilen yatırım bonusu kampanyalarını da inceleyin. Güvenilir casino deneyimi sürdürmek için Casibom tercih edebilirsiniz. Tüm forum üyelerine bol şans dilerim.

  536. Bahis severler selam, Casibom sitesi kullanıcıları adına önemli bir duyuru yapmak istiyorum. Bildiğiniz gibi site giriş linkini BTK engeli yüzünden yine güncelledi. Giriş problemi varsa doğru yerdesiniz. Yeni Casibom güncel giriş adresi şu an paylaşıyorum Resmi Site Bu link üzerinden vpn kullanmadan hesabınıza girebilirsiniz. Ek olarak kayıt olanlara verilen freespin fırsatlarını mutlaka inceleyin. Güvenilir slot deneyimi için Casibom tercih edebilirsiniz. Tüm forum üyelerine bol şans dilerim.

  539. Chào cả nhà, nếu anh em đang kiếm sân chơi đẳng cấp để cày cuốc Game bài thì tham khảo địa chỉ này. Không lo lừa đảo: sun win. Về bờ thành công.

  547. Hi guys, anyone searching for a trusted drugstore to order pills hassle-free. Take a look at this site: prilosec. Selling a wide range of meds with fast shipping. Thanks.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

+ sixty one = sixty five