The "Current time member" on the KPIs designer is poorly documented and that's an understatement. Does it actually do anything or is it unfinished code?
Has anyone else come to the conclusion that KPIs in SSAS were never "finished"? We've found numerous holes in the way KPIs have been implemented.
I have moved some KPI:s, for a client, from ProClaritys KPI Builder, to SSAS2005 KPI:s.
So far this have worked fine and we are able to show KPI:s in other tools than ProClarity.
As an example the coming Excel 2007 release supports SSAS2005 KPI:s.
These KPI:s can also be imported in to Business Scorecard manager/server. If you need additional functionality I recommend the Business Scorecard Server instead. Most important is that you can group KPI:s and assig weights to each KPI and or parent KPI.
I can agree that view tab in BI-Dev Studio does not always work. Im not sure about the time current member setting but you can use named sets or a default member to point to a current time member.
Kind regards
Thomas Ivarsson
|||Current Time Member is not used by the KPI browser included in BI Dev Studio. Current Time Member is intended as a way to specify a default time member that browsers can show, generally based on the most recent available and complete data. However, when a browser allows the user to slice on time for multiple KPIs, the Current Time Member is less useful since it may be different for each KPI.
KPIs are fully functional on the Analysis Services. There are some known issues with browsing KPIs (specifically around filtering) in the KPI Browser UI, but this is intended only as a sample browser to help in development and it should not prevent you from using this feature. We will be looking into improving the KPI Browser in the next major release.
|||Matt, thank you for your response. That's exactly what I needed to know.
How could the company I'm with get more involved in defining enhancements for Analysis Services? You mentioned the KPI Browser UI is "intended only as a sample browser" and that KPIs are fully functional; however, the more we dig into it the more we lean toward implementing our own architecture for KPIs because what is in SSAS falls short on many things. There should be a way to specify which date dimensions are valid for each KPI and which are not. Some KPIs may be restricted to the fiscal calendar for instance. Others will only be valid for certain date dimensions due to percentages they calculate. KPIs can't be formatted as percents without the status and goal calulations blowing up.
Also, and this is a MAJOR problem with SSAS in general, there is no support for multi-currency considerations. Yes, you can format a raw number as currency using the locale of the server, but there is no support for dealing with multiple amounts all in different currencies and formatting each of them with a currency code. We can't drop the Format as currency call and format it within our application based on another query to retrieve the currency type because then we would not be able to differeniate between percents and amounts. I'm having to explicitly look for $ and ($ in order to see if it's an amount and, if not, format it as a percent.
The multi-currency issue is a much larger problem outside the needed KPI improvements though. I just want to say though that an "amount" by itself is completely meaningless in a large financial application. There should be a currency data type down to the DB level that ties an amount and currency code together at all times. I'm never going to get that one though!
|||
Hi Terry,
The Associated Measure Group property is intended as the mechansim for specifying valid dimensions. I would be interested to hear if you've tried this and still found it to be inadequate.
Please explain this comment " KPIs can't be formatted as percents without the status and goal calulations blowing up" as I'm not aware of any issues here.
We actually do have support for dynamic (or static) formatting of currency values via the Language property (http://sqljunkies.com/WebLog/mosha/archive/2005/10/13/mdx_format_currency.aspx)
-rob
|||Thanks for your reply.
What I was referring to with KPIs is that there is no way (as far as I know) to say that KPI 1 is only valid for the Calendar Hierarchy, KPI 2 is only valid for the Fiscal Hierarchy, KPI 3 is only valid for months and not years or quarters, etc. If you have multiple KPIs in a dashboard and want them filtered based on a time dropdown containing values like "Current Calendar Quarter", "Fiscal YTD", etc. then this becomes an issue.
KPI values can't be formatted as percents for display purposes otherwise the status and goal calculations will throw an error. You have to format them as "Standard" instead of "Percent". After we found this we assumed that is why Adventure Works is also not formatting some of it's KPIs as percents which should be formatted as such.
Thank you for referring me to Mosha's article on formatting currencies in MDX. There is a lot more functionality available in SSAS regarding this than I knew about. I have multi-currency experience outside of the BI world, but I'm still learning how to do it in a dimensional-modeling environment. I would appreciate any other article references on designing a currency dimension. One thought I had on Mosha's article is that if your application is running in Europe and displaying multiple currencies on the same screen (and let's assume they're returned from the same MDX query) then each currency will need a comma as a decimal separator instead of a period. However, you don't want amounts in U.S. dollars to be formatted with a Euro symbol. Also, on many occassions you would want the currency symbol displayed on either the left or right-hand side of the amount (depending on what is common for that currency, dollars versus yen for example) and then the three-letter currency code to the right of the amount (to separated U.S. dollars from Canadian dollars for example). This level of multi-currency support always had to be special-coded for in the pre-.NET/SSAS '05 world anyway; however, I still claim that it would be dramatically easier to handle if there were a Currency data type across the whole .NET /SQL Server platform composed of an amount and currency code in one composite object. I'll keep dreaming... :)
Terry
No comments:
Post a Comment