blogs.conchango.com

I've not looked at MDM yet, but I agree that it would be useful for users to at least be able to test-drive their cube with an updated hierarchy even if their changes don't get put into production until later (I can imagine there might be some confusion if hierarchies were allowed to change at any time of day). Perhaps the feature to link up with here is dimension writeback (http://msdn2.microsoft.com/en-us/library/ms174540.aspx) which is not very widely known and not supported in most client tools.

BTW, have you seen the automated partition creation functionality that is in the Analysis Services Stored Procedure Project?

http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=Partition&referringTitle=Home

It's not quite what you requested, but it is very useful for prototyping. Come to think of it, it probably wouldn't be that hard to add to this functionality to get to what you do want. Hmmm....

It does feel that this imight be going full circle. A lot of OLAP started on the desks of business users and possibly Analysis Services more than anything else moved it to the IT dept. But I personally would like to see it happen.

Technically this is interesting. Like Chris says you already have something close to this like dimension writeback, however it requires a developer to manage this.

Problem: This will require that you will manage slowly changing dimensions with type one? And this will mean that end users will see something like the missing data wizard each time the open a report, or changes in dimension structures that will be hard to trace.

Speed is good but it has a cost as well

Jamie! I think that we will need two approaches for this.

Build solutions where users do not care about history, a sort of a dynamic collection of data that never passes a data warehouse. This is more or less how finacial markets works. I have also seen this requirement in areas like delivery accuracy in manufacturing.

The other is the traditional way with history and a DW. In sales you have a longer time span from order to delivery and possible claims before you can actually estimate the profitability of an order/customer.

It should also be possible to mix these approaches. I agree with you "What is BI" blog entry that we will have to review what we are building and develop our approaches.

Keep on writing more about what MS is doing in MDM Jamie!

Collections of secured hierarchies is a new aspect of one version of the truth.

Quick but wrong information sometimes drives the currency and stockmarkets. I have a degree in economics and know that statistics for unemployment, inflation rates and economic growth are not correct when they are published, every month.

Still most markets react as this is the truth.

Sometimes quick and accurate information is the best, if you can collect that information.

Quick but wrong should never be better than slower but good information.

I have been in the business since 2000 and have learned that for a BI System to be effective you need (among other things):

1.  Master Data Management - I would go so far as to say that you need someone assigned to the management of the contextual data on a full-time basis.  If done properly, this should reduce the need for complex ETL processes like "Fuzzy Lookups" or "History Restatements".  I cringe at the idea of letting the end users manipulate the "Master" data in form of Dimension Writebacks.  With the exceptions of Forecasting and Budget Planning, end users should never be allowed to write to the Cube or Dimensional data.  The data source is the only place changes should be made to contextual data and it should only be done by the Master Data Manager(s).

I don't believe there is a need for a Master Metric Data Manager, but there is definitely a need for Metric Data Definitions for use throughout the company.  Unlike the Master Data Managment for contextual data, this should not require a FTE once the definitions are established.

2.  ETL - You need a fast ETL process that will catch and handle "Unknown" contextual data and alert the Master Data Manager(s) of these conditions.  

3.  OLAP - You need an OLAP system that will aggregate the metrical data at the appropriate levels of the contextual data to deliver instant query response times and without impacting OLAP processing times.

4.  Security - Your OLAP system should provide adequate security mechanisms to prevent end users from accessing contextual data without the proper permissions.  The process of defining the security needs for the contextual data could include the Master Data Manager, but why?