November 2, 2015 at 6:12 pm
Hi
I have created a cube called Product Sales.
My fact table fProductSales has the following columns: SalesKey, ProductKey, SalesStatusKey, Revenue and NoOfProducts. I have a Product Sales measure group from this with Revenue and NoOfProducts as measures.
I then have a dSales dimension table that joins to the fact table on SalesKey. This table has SalesKey, SaleCampaignKey, DateSignedKey and EmployeeKey. I have a Sales cube dimension from this.
I then have a dEmployee dimension table that joins to dSales dimension on EmployeeKey. This table has EmployeeKey, EmployeeAltKey (business key) FirstName, Surname, Title, Department etc. I have a Employees cube dimension from this.
One of the measures i need to create is Revenue/distinct(Agents). My dEmployee table contains all employees in the company but the dProductFact table will only link to employees that are agents. So i created a Distinct Count measure on the dEmployee table on the EmployeeAltKey column resulting in a new measure group Agents and measure NoOfAgents.
I have then defined the Dimension Usage for this new measure group as a fact relationship type with the Employee dimension, and a many-to-many relationship type with the Sales dimension (this did not make sense to me but is the only thing that returns values when slicing by Sales dimension). I do have a number of other dimensions in the cube but at this stage Im just trying to get the basics working to understand the theory.
The questions and issues I have are:
1. Have I done this the best way possible?
2. If i browse the cube in Excel to check the counts, the counts are correct if I am slicing by Sales attributes but if i just display totals for the measures for the whole cube then the count is for all employees and not just agents. ie It is not limited by what is in the fact table as if it does not take into account the relationship between dEmployee and fProdcutSales.
3. When i get the distinct count working properly, how do I then define the calculated measure Revenue/distinct(Agents) ? Do i need to scope this calculation in a particular way?
4. I would have thought the Dimension Usage relationship between the Sales dimension and the Agents measure group should have been a regular one? This resulted in missing key errors and if i ignore the errors and deploy then the measure does not return any values?
Many thanks for any help.
Cheers
Garry
November 5, 2015 at 7:42 am
Hi, here's my comments on your questions:
1. In my opinion, no. What you have done here is called snow-flaking. You should really have the employee key in the fact table. It is also not a many-many relationship, but you'll be able to correct that when you fix the design.
2. This is mostly due to the design error and your dimension usage properties. Because of the fact that you want to only count Agents (employees with facts associated), I'd recommend that you create a calculated measure in which you will be able to use the "nonempty" clause to exclude empty dimension members. Note the implications of this though, as it will exclude any empty members within the context of your other members on the axis (i.e. it will not be a global count).
3. You define calculated measures in the "Calculations" tab of your cube. A search for ssas calculated measures will lead you to a lot of great resources on how to create these. You will only need to use a scoped assignment if you need a global count of agents (i.e. the same number irrespective of the time period and other dimension attributes). If you do need a global count, may I suggest the inclusion of an "Agent" Boolean attribute in your dimension. This will simplify your calculated measures, and also be useful in case you need it elsewhere.
4. It should have been, but due to the snow-flake you have defined the employee dimension as both a dimension and fact table. A general rule in SSAS is that all facts should have valid dimension members...your design flaw is biting you in the you-know-what here.
Hope this helps 🙂
November 5, 2015 at 4:39 pm
Hi
Many thanks for taking the time to reply. Ja, through the last month of doing SSAS there is one thing I am starting to realise (please correct me if im wrong)....SSAS says it is capable of being used over snowflake data warehouses but it sure as nuts seems to be a major headache going that way. I have read so many posts and blogs and the majority of them dont answer my questions as they are all based on a star schema design.....seems I have chosen to cut my teeth on SSAS the hardest way possible....some days have felt like baptism by lava !!
I cant change the data warehouse design, its existing, but Im close to redefining my whole DSV with Named Queries and turning it into a star schema design to make my life easier....hahaha, that probably made some people reading this shudder.
I have solved this current issue by replacing my fact table with a named query and bringing the EmployeeAltKey onto it. I then defined the distinct count over that. It all just worked like a dream, I did not even have to change any of the Dimension Usage mappings.
Cheers
G
November 6, 2015 at 7:38 am
To be fair to SSAS, you're not really using the snowflake in a typical fashion here. A snowflake is typically used to split out levels of a hierarchy (for instance country, state, city) and where you'd like to reduce storage and duplication.
The reason you're not seeing a lot of examples out there, is because of the fact that a snowflake is usually not best practice...unless you have a really good reason for it.
You're doing the right thing by "faking" it in the DSV...sometimes that is the only thing you can do. This will of course increase processing time, but a reasonable tradeoff if you cannot change the underlying table structure.
November 8, 2015 at 8:12 pm
Yes, I have just had a conversation with someone about the challenges I have been having with SSAS and they pointed out the same thing "SSAS will handle snowflake fine but it sounds like you data warehouse is not a very good design".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply