Role Playing Dimensions in SSAS

296
17174

Friends,

I am backkkk .. Long gap between my last post and this .. Here, in this post we are gonna discuss about ROLE PLAYING DIMENSIONS. I am gonna take same old and our favorite database “Adventure Works” to explain about Role Playing Dimensions. Lets say I have a scenario i.e I wish to see the sales happened based on Due dates,Ship Dates and Order Dates.These three dates MAY BE SAME and also MAY NOT BE as in many cases the ORDER will be given on one date and Shipment will be done on some other date and Dues will be paid on another date and hence this is very important for any Analyst to see the data based on different date types to get better idea about business. In this case I have to maintain THREE DATE KEY columns in my Fact table ONE for EACH date type given above. If you see the Fact table in Adventure Works database, it is designed in the same fashion.  I ran the query to fetch data from FactInternetSales table for the above given columns.

Select Top 100 OrderDateKey,ShipDateKey,DueDateKey,SalesAmount from FactInternetSales

From the above screenshot it is clear that Due Date,Order Date and Ship Date need not to be fall on same dates and hence we maintain different columns for each to track the status. Now, I wish to see the Total Shipment sales year wise. To get this I need to join the Fact table with Time table and the query looks like below.

Select DimTime.CalendarYear,Sum(SalesAmount) SalesAmount from FactInternetSales
Inner Join DimTime on DimTime.TimeKey = FactInternetSales.ShipDateKey
Group By DimTime.CalendarYear

In the above query I joined fact table (FactInternetSales) with Time table(DimTime) with SHIPDATEKEY as I wish to see the Sales year wise based on Shipments and the output can be seen in the screenshot given below.

Now, I wish to see the Total sales year wise based on ORDERED date. To get this I need to join the Fact table with Time table and the query looks like below.

Select DimTime.CalendarYear,Sum(SalesAmount) SalesAmount from FactInternetSales
Inner Join DimTime on DimTime.TimeKey = FactInternetSales.OrderDateKey
Group By DimTime.CalendarYear

In the above query I joined fact table (FactInternetSales) with Time table(DimTime) with ORDERDATEKEY as I wish to see the Sales year wise based on ORDER Date and the output can be seen in the screenshot given below.

And the final scenario i.e Sales year wise based on Due Date and the query looks like below.

Select DimTime.CalendarYear,Sum(SalesAmount) SalesAmount from FactInternetSales
Inner Join DimTime on DimTime.TimeKey = FactInternetSales.DueDateKey
Group By DimTime.CalendarYear

The output of the above query can be seen in the below screenshot.

From the above three results, it is obvious that the SALES VALUES doesn’t match as the transactions happened on different dates for each type. In OLTP database scenario, DIMTIME in the above query is referred as MASTER Table(where we will have master data of each entity) and FACTINTERNETSALES is referred as TRANSACTION table where we capture all transactions. In OLAP, we call DIMTIME as a Dimension and FACTINTERNETSALES as MEASUREGROUP or FACT.

Okie .. If the requirement is either one of the above mentioned three scenarios then NO ISSUES. We can create a Dimension using DimTime table, MeasureGroup using FactInternetSales table and give relationship between these two. What the Case if the user want to see the data based on all the three date types. Then the SQL query looks like some thing like given below.

Select a.CalendarYear,Sum(SalesAmount) SalesAmount from FactInternetSales
Inner Join DimTime a on a.TimeKey = FactInternetSales.DueDateKey
Inner Join DimTime b on b.TimeKey = FactInternetSales.OrderDateKey
Inner Join DimTime c on c.TimeKey = FactInternetSales.ShipDateKey
Group By a.CalendarYear

Even though the Date Types are different, the master data for all these are JUST Dates and hence ONLY ONE master table will be maintained. So, we have to use ALIAS of same DIMTIME table to join with different date types as shown above. The Group by and the Select list has a column “a.CalendarYear” and hence the data will be GROUPED yearwise based on DUE DATE as the alias “a” is joined with fact table with DueDateKey. To get the Sales Data year wise based on Order Date we simply need to change the Group By and Select list item a.CalendarYear to b.CalendarYear and to c.CalendarYear to get data based on Ship Date.

The same thing can be handled in SSAS Cubes using ROLE PLAYING DIMENSIONS property. PFB the Steps to define the same.

1) Add the fact table and the Dimension table to DSV as shown below and make sure RELATIONS are given for three date keys in Fact table to DimTime table.

2) Define dimension using DimTime table.

3) Create an Empty cube and add the Measure Group with FactInternetSales table.

4) Now add the dimension to the cube and see the magic. When you add one dimension, it adds THREE one for EACH date type. This is created based on the relationships given in DSV. I have THREE relations between DimTime and FactInterNetSales tables and hence THREE Dimension added. If you remove one relation ship in DSV and when you try to add the same dimension then it will add ONLY TWO dimensions. So, SSAS server will takes the headache of adding ALIAS Dimensions for each relationship given in DSV between these two tables.

Even though THREE ALIAS dimensions are created at Cube level, all the three will be pointing to the same underlying dimension as shown in below screenshot.

In the Dimension Usage tab the relations ships will be automatically set to the corresponding columns i.e ShipdateKey for Ship Date Dimension and so on as shown below.

So, the headache of creating ALIAS dimensions and giving relationships will be taken by SSAS Server if and only if the relationships are given in DSV between these tables. Now, what the case if the relationships are not given in SSAS( a rare case that happens). In this case all the headache will be on Developers who is developing the cube.

The steps remains the same as discussed above until Step 3. PFB the steps to be followed after completing the above given first three steps.

1) Add the dimension to the Cube and rename it to SHIP DATE.

2) Add the same dimension again and rename it to DUE DATE.

3) Add the same dimension again and rename it to ORDER DATE.

4) Go to Dimension Usage tab and click on the cell of each intersecting point and select the relationship type as REGULAR in the pop up wizard and select the columns to link. In the below screenshot I selected DueDateKey under MEasure Group Columns as I am relation DUE DATE Dimension to Fact table. Similarly we have to use OrderDateKey and ShipDateKey for Measure Group Columns to link with ORDER DATE Dimension and SHIP DATE Dimension respectively.

After adding relationships for all the three dimension PROCESS the cube and validate data by browsing cube. In simple Role Playing Dimension is a dimension which will have only ONE in database level and act as many DIMENSIONS in Cube.

That’s it .. Hope you guys understood the concept. Happy Coding !!

Regards,

Roopesh Babu V

 

296 COMMENTS

  1. [url=https://prednisoneforsale.store/#]prednisone 20mg capsule[/url] no prescription prednisone canadian pharmacy

  2. [url=https://doxycyclineonline.store/#]buy doxycycline from canada[/url] doxycycline without prescription

  3. proposal and dissertation help 3000 words
    [url=”https://bestdissertationwritingservice.net”]dissertation proposal writing services[/url]
    dissertation writing uk

  4. writing dissertation abstract
    [url=”https://dissertationhelpspecialist.com”]dissertation write up[/url]
    writing dissertation and grant proposals

  5. [url=https://canadiandrugs.best/#]prescription meds without the prescriptions[/url] best non prescription ed pills

  6. definition of dissertation
    [url=”https://helpwithdissertationwritinglondon.com”]writing your dissertation in a week[/url]
    books thesis dissertation help

  7. casino no deposit bonus win real money usa
    [url=”https://1freeslotscasino.com”]online casino free bonus no deposit[/url]
    safe online casino

  8. [url=https://stromectolbestprice.com/#]ivermectin anti inflammatory[/url] ivermectin dosage for pigs

  9. naltrexone 4.5mg [url=http://reviaus.com/#]naltrexone brand name india [/url] low dose naltrexone lyme treatment how to get prescription for naltrexone-bupropion

  10. [url=http://stromectolbestprice.com/#]ivermectin dogs dosage[/url] ivermectin side effects horses

  11. ช่วยตัวเอง ให้มีเงิน Slot Pg เรามาลองมาดูกันว่าถ้าอยากมีเงินเก็บมากขึ้นเพื่อสร้างความมั่นคงให้กับชีวิต ทุกคนล้วนคาดหวังให้ตัวเองสุขสบาย ลอง slot pg มีกิน มีเงินใช้จนถึงวันสุดท้าย ของชีวิต

  12. online casino with free signup bonus real money usa no deposit
    [url=”https://onlinecasinosdirectory.org”]online gambling real money[/url]
    online casino with free signup bonus real money usa no deposit

  13. causas disfuncion erectil [url=https://comprarcialis5mg.org/it/]cialis generico[/url] disfuncion erectil herbolario [url=https://comprarcialis5mg.org/it/cialis-5mg-prezzo/]cialis 5 mg prezzo[/url]

  14. djmusik djmusik Nie ma jeszcze żadnego komentarza. Dodaj go jako pierwszy! Mysia_Krosno H3AsO4 rys350 marekgoraczka11 Nie ma jeszcze żadnego komentarza. Dodaj go jako pierwszy! Mysia_Krosno rys350 Valinor Valinor Nie ma jeszcze żadnego komentarza. Dodaj go jako pierwszy! H3AsO4 djmusik H3AsO4 H3AsO4 JUSTIII86 djmusik Nie ma jeszcze żadnego komentarza. Dodaj go jako pierwszy! Mysia_Krosno H3AsO4 nusiaras29 nusiaras29 H3AsO4 Nie ma jeszcze żadnego komentarza. Dodaj go jako pierwszy! H3AsO4 Nie ma jeszcze żadnego komentarza. Dodaj go jako pierwszy! marekgoraczka11 Nie ma jeszcze żadnego komentarza. Dodaj go jako pierwszy! JUSTIII86 Valinor djmusik JUSTIII86 marekgoraczka11 marekgoraczka11 nusiaras29 Nie ma jeszcze żadnego komentarza. Dodaj go jako pierwszy! marekgoraczka11 marekgoraczka11 https://spiritofdiscernment.com/community/profile/veroniquehaase0/ Technologia VR znalazła szerokie zastosowanie w wielu dziedzinach naszego życia. Jednak temat gier hazardowych przez długi czas pozostawał na uboczu i dopiero w połowie 2022 r. coraz więcej twórców IT zacznie aktywnie pracować nad stworzeniem odpowiedniej zawartości gier w formacie VR. Trzeba również przyznać, że wielki sukces w cybergamingu przyniósł tej technologii wiele korzyści i w niedalekiej przyszłości powinien zmienić sferę hazardu online na lepsze! “Kontrakt sponsorski z Widzewem Łódź to dla nas ważny etap skutecznie i szeroko realizowanej od lat strategii wspierania polskiego sportu. Cieszymy się, że klub o tak bogatej historii ponownie będzie grał w Ekstraklasie, a także dołączy do grona sponsorowanych przez STS podmiotów. Jestem przekonany, że nasza współpraca przyniesie dużo korzyści zarówno STS, jako liderowi rynku bukmacherskiego, jak i Widzewowi, który od lat jest czołową piłkarską marką w Polsce. Skorzystają z niej także kibice, dla których przygotujemy aktywacje i atrakcyjne promocje” – powiedział chief marketing officer Grupy STS Paweł Rabantek, cytowany w komunikacie.

  15. The standard textbook perfect menstrual cycle is considered 28-days long, with ovulation occurring around day 14 of the menstrual cycle. ovulation calculator on clomid Clomid can also cause changes in your cervical mucus, which may make it harder to tell when you re fertile and may stop sperm from getting into your uterus.

  16. ovulation calculator clomid what I have read is that it isnt really that hard to get your body to hyperovulate especially if you have no issues ovulating on your own, the hard part is getting both eggs fertilized and implanted, several people might hyperovulate but only one egg fertilizes and implants, or one egg quality isnt very good so they both implant and with early u s show your pregnant with twins and then m c one most the time its absorb back into the body with no bleeding and next u s there is just one aka vanishing twin.

  17. Psilocybin is classified as a Schedule III substance in Canada under the country’s Controlled Drugs and Substances Act,3 meaning selling magic mushrooms could carry criminal penalties, although possession is legal. There is, however, an option to purchase magic mushroom microdoses from a private dispensary that the government is as of yet not attempting to take down in any substantial capacity. Many people experience nausea while using magic mushrooms, typically within the first hour. Vomiting and stomach cramps are both common. Many report having more success with capsules or tea, as opposed to the whole mushrooms. Grinding up mushrooms to use in smoothies could also help smooth digestion. But there is no foolproof solution, so it’s best to go into an experience with magic mushrooms anticipating possible side effects, which are in no way an indication that something has gone wrong. https://web-wiki.win/index.php?title=How_to_make_tea_from_magic_mushrooms Microbiologist Dirk Hoffmeister recreated in a test tube the biological process that magic mushrooms use to make psilocybin. His group identified four key enzymes—the machinery of biochemistry—that convert the starting material, 4-hydroxyl-L-tryptophan, into the psychoactive final product. The active ingredient in magic mushrooms is a compound called psilocybin, first described in 1958 by chemist Albert Hoffman. Our bodies convert it to the molecule psilocin, which interacts with brain receptors to produce the hallucinations and euphoria shrooms are known for. Hey everyone. I live in a place where mushrooms are fairly plentiful in season and as a result I’ve developed a few ways of extracting them that might be of interest. You are about to edit a recipe that you don\’t own. We will add a copy of the edited recipe to your cookbook.

  18. While cash games at play money tables do serve to educate players as far as the mechanics of the game go, meaning knowing things such as the rules of the game, how the betting works, and other basic elements of playing poker online, that’s about all you can expect really as far as the benefits of playing play money cash games go. Aside from that, what tends to happen is that play money players develop some terrible habits and have to ditch what they have learned at the play money tables in favor of a completely new strategy. BetMGM Poker Ontario, powered by partypoker software, is also live with a full suite of popular game types. Ontario players who played on partypoker’s global client will see a familiar interface at BetMGM Poker Ontario. Many players started playing online poker from free games. That’s because playing free poker online you can develop your poker skills and learn the ins and outs of popular games like Texas Hold’em and Omaha poker before you decide to play for real money. https://page-wiki.win/index.php?title=New_mobile_slots_no_deposit_bonus New! Virtual Offerings! “‘Bristol Casino – Future Home of Hard Rock’ is something of which Bristolians can be very proud. We are glad that the project is having an immediate impact in boosting Bristol’s economy, by bringing at least 600 new, good-paying jobs to the city. This is only a start, as the project will generate even more jobs when the permanent casino opens.” Allen did not say Hard Rock planned to build a casino using his company's signature Guitar Hotel design. Bitstarz Gaming It offers one of the best Bitcoin casinos around today. They provide excellent customer service, an entertaining game selection, and hundreds of other bonuses that make this casino hard to beat! If you wonder what do you get in the package it is “You will stay in a Caribbean Suite – it’s a jungle out there. Literally. Might as well take it in from the privacy of your balcony. But that’s just the start. Whether your suite includes two double beds or a sprawling king, you’ll indulge in the spaciousness of this suite – realizing that your stay at the Hard Rock Hotel & Casino Punta Cana is anything but status quo. From 300-thread count linens to a fully stocked mini bar and even your own double

  19. The east end of Toronto is decidedly lacking in licenced cannabis stores, as there isn’t a single location past Yonge Street. Some estimate to marijuana market to be as big as $5 billion nationwide. When the federal government establishes guidelines for marijuana legalization, there will be a fight over who controls the industry and people are lining up to get their share. Dispensaries appear to be no different, putting in the leg work now, the very risky and illegal legwork, to get a foothold with the expectation of a payday. Once registered, order your medication directly from your licensed cultivator. Complete and send your registration application form and the original of your medical document to Health Canada if you: While they are still stocking the shelves and preparing for their grand-opening, the store already offers a wide variety of flower, gummies, baked goods and chocolate bars. Look for more product coming in every day. You can get a free pre-roll for signing up as a member which only takes a few moments. You can also take advantage of their amazing two ounces for $100 deal, which is unheard of in Toronto. Stop by 1176 Danforth Ave and check out the store for yourself. https://paxvox.com/community/profile/elvirahewitt675/ Marijuana is also used to manage nausea and weight loss and can be used to treat glaucoma. A highly promising area of research is its use for PTSD in veterans who are returning from combat zones. Many veterans and their therapists report drastic improvement and clamor for more studies, and for a loosening of governmental restrictions on its study. Medical marijuana is also reported to help patients suffering from pain and wasting syndrome associated with HIV, as well as irritable bowel syndrome and Crohn’s disease. It is essential that you have the required information and documents in an electronic format ready for upload before starting the application process.  Please carefully review the requirements below and the appropriate guidance documents to ensure you are prepared to complete the entire application before beginning the process.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

− 3 = 3