UDM data access – is it all done with mirrors?

I had a few spare cycles to do some reading recently, and thought I would check out the new Unified Dimensional Model (UDM) that can be used with Analysis Services 2005. I started by listening to a webcast by Amir and Ariel Netz. Interesting stuff about datamarts, data warehouse, and specialized metadata model proliferation. And the strengths of reporting against both relational and OLAP data. Although MOLAP cubes are still with us, AS2005 seems to be becoming a reporting clearinghouse, a "UDM server".

The only thing that struck me a bit strange was the concept of using live RDBMSs to feed UDM data caches as an adjunct to or replacement for datamarts and data warehouses. I've been spending a lot of time lately talking to DBAs who are concerned that features such as SQLCLR and in-database web services might blur the "focus" of a database, and make management more complex because of resource contention/sharing. I'd think that a UDM connection to a live database (rather than a reporting only database copy) might complicate management, sharing, and contention issues even more.

Reading more about this in SQL Server BOL, there IS a section on using database mirroring and snapshots to support reporting. So maybe they're not talking about a reporting connection to a live OLTP database, something that hasn't been done (with OLTP performance in mind) for a while. Maybe it's all done with mirrors.

4 thoughts on “UDM data access – is it all done with mirrors?

  1. I work with some hard-core BI/data warehousing guys and they too have reservations about the scalability of ‘pro-active caching’. But it could still be very useful certain scenarios with specific real-time reporting requirements combined with analytics. I think the top-end enterprise AS2005 implementations will continue use the traditional architecture of a subject matter database that the cubes feed off; although the staging database will become less prominent given the tremendous transformation support provided by Integrations Services.

    They do however think that AS2005 is a killer product and the UDM is cool. It, like DTS to Integration Services, has been re-architected more than the relational side. We may have a plethora of new functionality on the relational side like SQL/CLR, etc., but it seems that what was already there in terms of the 2000 relational engine has not been massively re-architected. Hence it is possible to attach a SQL 2000 mdf file to a 2005 server with minimal conversion.

    A very small selection of some cool new UDM / AS2005 features (the tip of the iceberg):
    • .NET stored procedures called by MDX. Similarly to SQL CLR, the Analysis Services process also hosts the CLR. These are especially useful for custom calculations that are too picky for MDX. Unlike AS2k COM UDF counterparts, we can receive MDX types such as members, sets and tuples as parameters instead of string representations.
    • Procedural MDX scripts. We can actually debug the MDX scripts as they execute – and graphically browse/slice/dice the data as it is calculated (and cached to disk if required)! This also means no more worries about pass or solve order.
    • Attribute based dimensions allow you to define aggregation paths for natural hierarchies yourself, significantly reducing the cube processing window. Details at my colleague’s blog post: http://blogs.conchango.com/jamiethomson/archive/2005/01/06/706.aspx.

    Mosha Pasumansky’s blog, http://sqljunkies.com/WebLog/mosha/, goes into some detail.

  2. Thanks Christian,
    I too think that AS2005, Data Mining, and UDM are VERY cool. It was just the "realtime" feature that struck me funny. I also think the SSIS improvements (both with AS2005 and Data Mining) make this a killer product line. I need to read more on .NET stored procs callable by MDX and the other features you mention, subscribed to Mosha’s blog (I already read the Hitachi one). I’ve been following Kirk, the SSIS team, and you folks’ blogs for SSIS.

    There have been big low-level changes to the structure of the relational server too, reference the resource database, SQLOS, permission overhaul, secret storage, SNI, user/schema separation etc, etc…

  3. My concern is scalability; a problem all BI vendors have. AS2000 would choke and die at volume … >500Gb … regardless of the hardware. Adding Reporting Services and Data Mining on top of the AS engine via UDM seems to be asking for trouble.

    This prompted us to move more complex calculations out of the MDX queries to either post-query rendering or pre-query storage of all calculation permutations. Neither of these is a good solution, so we look forward to seeing whether .NET procedures from MDX is a performance optimization.

  4. Terry, the scenario you describe is typical; calculations are a big performance bottleneck due to being performed on-the-fly. The workarounds you describe are also typical. I have overcome this in the past by programmatically caching reports as part of the ETL.

    One feature in AS2005 is the ability to cache the calculations in MDX script using the CACHE keyword. I’m keeping my fingers crossed that this will do the job. The script is run as part of cube processing allowing the calculations to be stored to disk along with the aggregations (excluding some special cases like mixed granularity).

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.