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
But, there have only been a few studies of metformin use in pregnant women to confirm its safety.
your needed drugs through a site specializing in discounted tretinoin cream online pharmacy help increase my blood flow?
We were not able to conclude based on this study whether this unexplained dermopathy represents a new condition, as has been proposed by those who use the term Morgellons, or wider recognition of an existing condition such as delusional parasitosis.
Merck Veterinary Manual, 9th edition online version.
Receive low prices when you tadalafil 6mg vs 9mg . ED drugs come in lower price.
But that seems like forever ago that I went through any of this.
In our platform, we bring you the newest updates and thrilling news about the most popular UK celebrities from the worlds of broadcasting, reality TV, and celebrity culture. Whether you’re a fan of hit reality shows like Love Island, The Only Way Is Essex, or Made in Chelsea, or you’re interested to track the lives of the UK’s top social media content creators, our platform covers it all. From spicy behind-the-scenes drama to exclusive discussions, we ensure you’re in the know with everything happening in the world of your preferred personalities – https://ukeventnews.uk/why-isn-t-instagram-music-accessible-to-certain/ .
UK reality TV personalities have gained huge attention over the years, turning from everyday characters into household names with massive supporters. Our blog investigates their personal and professional lives, offering perspectives into their latest projects, connections, and dramas. Whether it’s a new relationship brewing on Love Island or a cast member from Geordie Shore launching a new business, you’ll find in-depth stories that display the glamorous yet sometimes chaotic lives of these stars.
Огромный выбор мотозапчастей для любых моделей | Подберите идеальные детали для вашего байка | Улучшите характеристики мотоцикла с новыми деталями | Оригинальные мотозапчасти от ведущих производителей | Широкий ассортимент запчастей для тюнинга мотоциклов | Приобретайте высококачественные запчасти для максимальной производительности | Лучшие предложения на запчасти для тюнинга | Выбирайте качественные детали для максимальной надежности | Закажите необходимые детали для вашего байка с доставкой | Широкий ассортимент запчастей по доступным ценам | Оптимальные решения для тюнинга мотоцикла | Улучшите характеристики своего байка с новыми деталями | Качественные запчасти для мотоциклов по выгодным ценам | Выбирайте оптимальные детали для вашего байка | Быстрая доставка запчастей на дом
авто мото запчасти авто мото запчасти .
Promo Codes home appliances savings http://www.skidki-i-kupony.ru .
вывод. из. запоя. на. дому. ростов. http://sportandpolitics.ukrbb.net/viewtopic.php?f=24&t=17802 .
. [url=https://www.news.soomaliforum.com/threads/the-secrets-to-building-a-happy-and-strong-relationship-as-a-couple.132155/]https://www.news.soomaliforum.com/threads/the-secrets-to-building-a-happy-and-strong-relationship-as-a-couple.132155/[/url] .
вывод из запоя дешево ростов-на-дону [url=http://www.rolandus.org/forum/viewtopic.php?p=106422]http://www.rolandus.org/forum/viewtopic.php?p=106422[/url] .
купить диплом бакалавра в ростове на дону [url=https://1oriks-diplom199.ru/]1oriks-diplom199.ru[/url] .
частный нарколог на дом [url=https://www.spilkuvannya.rolevaya.com/viewtopic.php?id=66]https://www.spilkuvannya.rolevaya.com/viewtopic.php?id=66[/url] .
вывод из запоя кодирование краснодар [url=https://vkontakte.forum.cool/viewtopic.php?id=19618/]вывод из запоя кодирование краснодар[/url] .
вывод из запоя на дому краснодар цены [url=https://serpentarium.ukrbb.net/viewtopic.php?f=3&t=16709/]вывод из запоя на дому краснодар цены[/url] .
вывод из запоя воронеж [url=http://to.iboard.ws/viewtopic.php?id=8064/]вывод из запоя воронеж [/url] .
вывод из запоя врачом на дому [url=http://www.svarog.forum24.ru/?1-0-0-00000226-000-0-0-1730749853]вывод из запоя врачом на дому[/url] .
вывод из запоя в екатеринбурге https://honey.ukrbb.net/viewtopic.php?f=45&t=16709/ .
Полезная информация о применении индустриальных масел, Выбор профессионалов: индустриальные масла, Как подобрать индустриальные масла, для повышения эффективности работы, которые изменят ваш бизнес, для увеличения срока службы оборудования, Экологическая ответственность в выборе масел, и как выбрать безопасные для природы продукты, Советы по рациональному использованию масел, для оптимизации бюджета
и 20а масло [url=https://industrialnyemasla.ru/]и 20а масло[/url] .
вывод от запоя в стационаре сочи вывод от запоя в стационаре сочи .
вывод из запоя нижний новгород стационар [url=https://snatkina.borda.ru/?1-11-0-00000205-000-0-0-1730809079]вывод из запоя нижний новгород стационар[/url] .
вывод из запоя стационарно вывод из запоя стационарно .
выведение из запоя в нижнем новгороде выведение из запоя в нижнем новгороде .
поздравления с днем рождения жене [url=https://www.pro-happy-birthday.ru]поздравления с днем рождения жене[/url] .
лечения наркозависимости в стационаре лечения наркозависимости в стационаре .
Как избежать рисков при покупке диплома колледжа или ВУЗа в России
выводу из запоя в стационаре [url=cinemania.forum24.ru/?1-9-0-00000068-000-0-0-1730818520]выводу из запоя в стационаре[/url] .