thanks for the information - do you know where I can get a demo version? have used FRx for a number of years and am very eager to get management reporter
thanks & regards
Hi Jeff,
Glad you found it useful.
As far as I'm aware, Management Reporter is only available on volume licensing (licensing.microsoft.com/.../Default.asp) or more recently for MSDN subscribers (http://msdn.microsoft.com). I downloaded mine from MSDN, but I didn't see an evaluation version.
If an evaluation version becomes available then I'll mention it on this site
Best regards
Jeremy
As I mentioned in my post a few weeks a ago, the PEL Allocate statement is a powerful method of moving
Nice post geremy,
I assume you can help me out with a little problem i am having with Allocate statement.
I am using a Push Cross Model Allocation Rule to push data from one model to another. The dimensions and members sets for both the models are exactly same.
Planning Business Modeler is also validating the rule correctly. However, when i try to execute the rule. I get the following error,
"Some of the model's dimension members are invalid or missing. The model may need to be redeployed."
I have also redployed the model several times but to no avail.
Following is the PEL for the rule.
SCOPE ([Business Driver].[All Members].Members,
[Project].[All Members].Members,
[Scenario].[All Members].[Actual],
$CurrentPeriod$.Lag(0)
);
ALLOCATE(
THIS,
(),
1,
()
END SCOPE;
Any urgent reply will be most appreciated.
Hi Hameer,
I've noticed that you have the statement [Project].[All Members].Members in your scope. If you change that to a single member, or to [Dimension].[Member Set].[All].LeafMembers, then you should find that your allocate statement runs without error.
Take a look at the MDX query that gets generated when you have the Members function in the scope. When you specify the Members function in the scope, even though you can't see it in the member selector, the scope will include the All member. Although this is fine for the source of your query, the target cells of your ALLOCATE() statement is the 'this' keyword, which of course means that it's the same as the scope. The end result is that PPS is trying to write data to the All member, which is not possible.
Hope that helps,
PerformancePoint gives you the flexibility to have multiple assumption models to use as you please in
Management Reporter take only Financial Data Mart(FDM) type as data source.
i want to create reports in another data source type,kindly i need your help.
Hi Amarneh,
First of all, Management Reporter can also connect to PPS Financial Models that contain a currency dimension.
If you want to make it connect to other data sources, then you need to create a provider using the new Data Integration Toolkit - technet.microsoft.com/.../cc514360.aspx
There is also a good discussion on the forums that talks about connecting to other data sources - forums.microsoft.com/.../ShowPost.aspx
Hey Jeremy,
Thanks for the great post here and on the msdn site - I managed to get the ALLOCATE rule working, but ran into the problem of always appending. No issue, just create that 'this=NULL' rule.
However, I am using a PushCrossModelAllocation rule, so I don't have the source members to select for my scope when creating the 'this=NULL' rule.
How do I get around that?
Thanks
Jerry
Hi Jerry,
Great to hear that you found it useful.
Unfortunately the Pull and PushCrossModelAllocation rules require an ALLOCATE or TRANSFER statement, meaning that you can’t use ‘this=NULL’ to delete the data in the destination model.
One option that you have is to create a PullCrossModelAllocation rule in the destination model. Alternatively, you could write a Native SQL rule to do the delete. With the Native SQL rules, you can effectively simulate cross model operations. All that you would need is essentially a delete statement and a where clause against the destination model fact table. You could then execute a rule set containing 1)The Native SQL delete and 2)Your PushCrossModelAllocation rule - both from the source model.
So unfortunately there’s no easy way around the problem. You have a choice of either re-writing your rule to do a Pull or getting stuck into the Native SQL rules. Hope that one of those work out for you – if not let me know
Jeremy, you mentioned "returning the number of records affected" - could you give an idea how it can be done?
Pingback from Master Data Management « Charlie Maitland’s Blog
So, although later than the trail blazers , I thought I'd write up a brief note about the day one
So, although later than the trail blazers , I thought I'd write up a brief note about day one of
Hi,
Yes I did mention returning the number of records affected, although in hindsight you will only ever see this if you are running your native rule in PBM.
If you do want to return the number of records affected, then you need a line in your in your native rule that is: IF object_id(N'tempdb..[#GeneratedCalc_Temp1]') IS NOT NULL INSERT INTO #GeneratedCalc_Temp1 VALUES (@retVal).
This temp table is then queried by BPM when the rule completes in order to display the number of rows affected. Note that the variable used (@retVal) should have @@rowcount appended to it after each statement. So if you have a delete statement first, then under this statement write set @retVal = @@rowcount. Then in the subsequent insert/update statement(s) write SET @retVal = @retVal + @@rowcount, so that @retVal always holds the total number of affected rows for the rule.
Hope that's what you're after
Having posted about NativeSql business rules a while back , I though that I might as well cover NativeMdxQuery
Hi Jeremy,
i think instead of using VBA!DateAdd("d", [Measures].[Value], "30/12/1899") you could just write VBA!CDate([Measures].[Value]) in MDX.
BTW, nice post!
Thomas
Hi Thomas,
You're right - you can use CDate instead of DateAdd. It seems that my VBA isn't as sharp as it used to be! I'm not sure which is quicker, but its good that the MDX/VBA combination gives you options.
Many Thanks
Jeremy has already announced the release of PerformancePoint Server SP2 and it's great to see that
Pingback from Bookmarks about Excel
Pingback from User links about "hierarchy" on iLinkShare
Interesting to see yesterday that XLCubed have released version 5 of their popular OLAP reporting tool
Pingback from Websites tagged "olap" on Postsaver
I'm a bit new to MDX, but apparently this leap year thing is a real problem for people to handle in MDX.
You say"
In the MDX Script, the first step is to scope on a member called [Time Analysis].[Time Analysis].&[2], which is the 12 Months to Date member in my Time Utility dimension:"
I don't quite understand what [Time Analysis] is....is this a dimension table that is already popuplated with some kind of data, based on Time Utility dimension. If so, what kind of data does Time Utility contain?(I'm look for field names I guess)
In this case I'm referring to a Time Utility Dimension. They can also be known as a Time Analysis Dimension or Date Tool.
It's generally always a flat dimension, so the structure to hold the members would only ever have a column to support one attribute/level. A lot of people even define the 4 needed members directly in a view, rather than holding them in a table.
Here are a couple of approaches to this:
sqlblog.com/.../datetool-dimension-an-alternative-time-intelligence-implementation.aspx
www.obs3.com/ssas_tips.shtml
Hope that helps...
Although I didn't mention it, the inspiration for my last post was the excellent MDX Solutions 2nd
Good summary. The indicator functionality in RS reminds me a lot of the icon set functionality in Excel conditional formatting. Which has seen some minor tweaks in O2010. I know ordinary users have quite a high degree of success with the Excel UI, but I'm not sure as many people are successful with the PivotTable portion of the exercise... Overall I think Excel + Excel Services get you to a very similar column as RS2008R2.
Another thing I'd think about adding to the comparison is where the KPI is defined. Is it in the data source (AS KPI) or in the report? If you add a KPI from an OLAP data source does the reporting tool automatically know how to render the indicator? PowerPivot doesn't support KPIs yet, but if it did I think this data point becomes more relevant...
Not that I want to make this a more complicated story, I've been advocating for simplicity as much as I can. :)
Hi Peter,
Thanks for the comment - all good points.
You can certainly use SSAS KPIs in SSRS, but it doesn't pick them up automatically. Worth another blog post I think. PerformancePoint Services, incidentally, does pick them up ok.
Long term, I'd love to see something like a KPI abstraction that is re-usable in Excel, AS, SSRS and PPS (aka all the reporting tools). I think this has the potential to be a big time saver. But maybe the formatting is so easy to use it's not a big deal?
niiiice
I agree, if there was something that was re-usable across all parts of the MS BI stack then it would certainly come in handy. The formatting part in SSRS is pretty easy though, they've done a slick job on the interface.
good explanation...may be if you can specify with more example and actual views would be really helpful..may be I am being just a child to be spoonfeeded...:)
hi, I would like to add complex business rules. I have a date attribute.
e.g.
if
Day(DateColumn) is not equal 15 OR Day (DateColumn) is not equal 30
then
action
very interesting blog
thanks!
I am very exciting of hearing about a book of master data services. In June i am gone a buy it.
On this moment i am creating a prototype with Master data services. Only I have a question about it. On the internet I can't find a answer so I hope you now more about it.
Is there a possibility in MDS to keep history of the data. Example: I have a bike in blue color. I change it to yellow. Now i will see that the bike is now yellow but that he was blue.
If you have any input for me, i'd like to hear from you.
Sebastiaan
Hi Sebastiaan,
MDS wil keep the history of your data by default. In your example, if you change the bike from blue to yellow, you can find out that it used to be blue from the transaction history.
To access this feature go to Explorer->Review->Transactions. You can also look at this across versions by looking in the Version Management feature.
Hope that helps
I'm trying to find more details on the migration process from MDS Sql Server 2008 R2 to MDS in SQL Denali. Did you know if the migration path would be possible ?
Thanks.
I've heard that the underlying db hasn't changed much, aside from the fact there are now entity specific staging tables. So that looks good for migration, but I haven't heard anything concrete. Perhaps we'll learn more when CTP3 is (apparently) released this week.
There will be easy upgrade from SQL Server 2008 R2 into Denali. You can use it to test your current models on Denali CTP3.
Great stuff. Myself also interested in DQS. Nice reading
Thanks for the info! I proposed using Collections to my customer to solve a different but similar reporting need - however, we haven't implemented yet because I heard a rumor that Collections may become deprecated in a future version of MDS. Any thoughts on that?
Thanks!
Melissa Coates
Hi Melissa,
My thoughts are that I hope not! I certainly haven't heard that they're going to be dropped, and I've noticed that the collections UI has been given a significant update in RC0. If I hear anything I'll be sure to post something...
Great practical tip on Power View. Its a really useful tool that I think people are going to take to.