sdswiger
asked on
Analysis Services - Calculations Based on Dimension Value
Hello:
I've trying to create a calculation based on a dimension. Basically, I have a "Type of Goal" which is text that could display like the following:
Sales Goal
Sales Volume
Pending Sales
Pending Volume
My dimensions involve time, geographic location, etc...My measures involve dealing with SUMs and COUNTs based on Pricing and Counts of Records. I'm looking to create a calculation that calculates differently based on the Type of Goal as listed above with 4 examples. Taking "Sales Goal" for example, this would change to calculate the number of records based on certain variables such as the Sale Date falling within the Time frame being reported on within the CUBE (i.e.: Quarter, Year, Month, etc...). The "Sales Volume" for example, this would change to calculate the sum of the Sales Price based on the status of the deal being Sold with the Sale Date falling within the Time frame being reported on within the CUBE (i.e.: Quarter, Year, Month, etc...).
Is it possible to setup a calculation to change based on the "Type of Goal"? If so, could anyone provide an example of how to do this? Once I get the grasp of setting this up then we'll be good to go. I'm somewhat new to calculations within CUBEs so be gentle, :P.
Anyways, any ideas/examples on this would be fantastic!
I've trying to create a calculation based on a dimension. Basically, I have a "Type of Goal" which is text that could display like the following:
Sales Goal
Sales Volume
Pending Sales
Pending Volume
My dimensions involve time, geographic location, etc...My measures involve dealing with SUMs and COUNTs based on Pricing and Counts of Records. I'm looking to create a calculation that calculates differently based on the Type of Goal as listed above with 4 examples. Taking "Sales Goal" for example, this would change to calculate the number of records based on certain variables such as the Sale Date falling within the Time frame being reported on within the CUBE (i.e.: Quarter, Year, Month, etc...). The "Sales Volume" for example, this would change to calculate the sum of the Sales Price based on the status of the deal being Sold with the Sale Date falling within the Time frame being reported on within the CUBE (i.e.: Quarter, Year, Month, etc...).
Is it possible to setup a calculation to change based on the "Type of Goal"? If so, could anyone provide an example of how to do this? Once I get the grasp of setting this up then we'll be good to go. I'm somewhat new to calculations within CUBEs so be gentle, :P.
Anyways, any ideas/examples on this would be fantastic!
ASKER
Thanks for the response EugeneZ
Regarding the examples, Report Builder is not the direction I'm looking towards. This is a CUBE that basically will become an interactive Score Card for the business. Let me provide a bit more information.
The row dimensions: (descriptions in parenthesis)
Sales Goal (count of records based on the status of the deal as being either Sold, Pending, etc...)
Sales Volume (sum of the sales price based on those deals with a status of Sold, Pending, etc...)
Pending Sales (count of records based on the status of the deal being Pending)
Pending Volume (sum of the sales price based on those deals with a status of Pending)
Other row dimensions will be present which may deal with geographic location, possibly broken down further into detailed locations, etc...
The column dimensions would be time and the goals for each row dimension based on what the user is interacting with. We have goals that are broken down to the type of goal (i.e.: Pending Sales, Pending Volume, Sales Goal, Sales Volume, etc...) and are even broken down to the most detailed geopgrahic location along with time aspects. This would be a complete interactive Score Card CUBE that our financial analysts would be able to analyze profitability, etc...in order to help build the company, etc.... Hopefully I've provided more information.
The goals table that I discussed above (the dimension table) contains the goal which would be the standard sum of the values within the table and require no special calculations to be setup. This would be a simple SUM of the values. Where the trick comes into play is changing how items are calculated based on the row dimension that deals with the "Type of Goal", which may be Sales Goal, Sales Volume, Pending Sales, Pending Volume, etc...The calculation should change what it's calculating and how based on this. This is what I'm trying to conquer and solve. The CUBE would be available online through Reporting Services as an Excel file, provided with interactivity through a Pivot Table drag-drop style, CUBEs :)).
Anyways, hope this helps further.
Any help from anyone would be greatly appreciated! :))
Regarding the examples, Report Builder is not the direction I'm looking towards. This is a CUBE that basically will become an interactive Score Card for the business. Let me provide a bit more information.
The row dimensions: (descriptions in parenthesis)
Sales Goal (count of records based on the status of the deal as being either Sold, Pending, etc...)
Sales Volume (sum of the sales price based on those deals with a status of Sold, Pending, etc...)
Pending Sales (count of records based on the status of the deal being Pending)
Pending Volume (sum of the sales price based on those deals with a status of Pending)
Other row dimensions will be present which may deal with geographic location, possibly broken down further into detailed locations, etc...
The column dimensions would be time and the goals for each row dimension based on what the user is interacting with. We have goals that are broken down to the type of goal (i.e.: Pending Sales, Pending Volume, Sales Goal, Sales Volume, etc...) and are even broken down to the most detailed geopgrahic location along with time aspects. This would be a complete interactive Score Card CUBE that our financial analysts would be able to analyze profitability, etc...in order to help build the company, etc.... Hopefully I've provided more information.
The goals table that I discussed above (the dimension table) contains the goal which would be the standard sum of the values within the table and require no special calculations to be setup. This would be a simple SUM of the values. Where the trick comes into play is changing how items are calculated based on the row dimension that deals with the "Type of Goal", which may be Sales Goal, Sales Volume, Pending Sales, Pending Volume, etc...The calculation should change what it's calculating and how based on this. This is what I'm trying to conquer and solve. The CUBE would be available online through Reporting Services as an Excel file, provided with interactivity through a Pivot Table drag-drop style, CUBEs :)).
Anyways, hope this helps further.
Any help from anyone would be greatly appreciated! :))
ASKER
Oh, forgot to add in that there may be other variables which determine the final value displayed (the measure) within the custom calculation and may be items such as, "Did the Sale Date fall within the time frame along with the status being Sold, Pending, etc...?", questions like these would be built into the custom calculation and change based on the "Type of Goal" being displayed within the row dimension.
Hopefully I've made some sense here, :P.
Hopefully I've made some sense here, :P.
Hi,
I think what do you want is posible. It's depend on your dimension and cube structure. To load data to your cube based on your requirement you can do a bit programming. just put your formula as per your requirement. You can use .net, vb, etc, which support OLAP. And you can use mdx query to retrieve data you want to see. Put data in the cube is depend on the formula you put in the programming script.
Regards
Veronika
I think what do you want is posible. It's depend on your dimension and cube structure. To load data to your cube based on your requirement you can do a bit programming. just put your formula as per your requirement. You can use .net, vb, etc, which support OLAP. And you can use mdx query to retrieve data you want to see. Put data in the cube is depend on the formula you put in the programming script.
Regards
Veronika
ASKER
Hi Veronika and thanks for responding.
I figured it would be an MDX script (custom calculation) but am unsure how to even come close to solving the problem. I know .NET, VB, etc...but not MDX Scripting, it's a bit new to me. Any examples or anything would be great.
I figured it would be an MDX script (custom calculation) but am unsure how to even come close to solving the problem. I know .NET, VB, etc...but not MDX Scripting, it's a bit new to me. Any examples or anything would be great.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://technet.microsoft.com/en-us/library/aa964121.aspx
Working with Analysis Services Cubes in SQL Server
http://www.informit.com/articles/article.aspx?p=337135&seqNum=1