THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Metadata for BI Solutions in the Microsoft stack

You know, today Microsoft doesn't have a solution to handle metadata of a BI Solution. Really, they don't have nothing to handle a single layer of the architecture like the Data Warehouse.

In a (how much near?) future this gap could be filled by using Oslo. Yes, Oslo is not a solution by itself, it's a platform, but studying it I see that it is the more natural way to produce a very well integrated metadata solution.

What do you think about it? Is someone already working on this? If not, I would like to know the reasons...

Published Sunday, May 17, 2009 7:11 PM by Marco Russo (SQLBI)

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Ella Maschiach said:

Hi Mr. Russo,

I’m terribly sorry, perhaps I didn’t understood you correctly, but when you say that Microsoft doesn’t have metadata for BI Solutions, aren’t you overlooking the SQL Server Metadata Toolkit and the Rowset Viewer?

http://code.msdn.microsoft.com/SqlServerMetadata/Release/ProjectReleases.aspx?ReleaseId=1174

http://blogs.microsoft.co.il/blogs/barbaro/archive/2007/12/29/discovering-your-metadata.aspx  

Or are you saying you find them as incomplete solutions to your needs?

Could I possibly ask you to clarify your point?

With thanks in advance,

Ella

May 18, 2009 3:59 AM
 

Marco Russo (SQLBI) said:

Hi Ella,

thanks for your feedback.

I know, SQL Server Metadata Toolkit is a sample solution, but it is not a "product" and (I think) for this reason there is not an ecosystem around it.

Oslo is an infrastructure with a precise idea about a central repository of informations and dependencies. It is not committed to Data Warehouse and BI processes, but I think that, as a platform, it could be easily adapted to it.

Just to make an example of what is missing in SQL Server Metadata Toolkit: if you use database views to decouple layers in a BI solution architecture (like we did a lot in SQLBI Methodology), you lose a granular view of dependencies at the column level (and maybe also at the table level when joins and subqueries are involved).

What is necessary (to me) is:

- a central repository for metadata, open to third parties products and to customization

- a set of tools that analyze the system and populate the metadata repository, finding dependencies regardless of the technique used (sql views, DSVs, SSIS transformations, and so on)

- a set of standard APIs allowing client tools (like Excel, for example) to query the metadata repository providing dependencies AND documentation (for both end-users and technical people)

- an integration with development environment (read BIDS, but also Database modeling tools) to do impact analysis when you make any changes

Sounds it better?

Thanks again,

Marco

May 18, 2009 4:16 AM
 

Ella Maschiach said:

Hi Mr. Russo,

Thank you for your quick and detailed reply. I see what you mean now, and I admit I know of no solution that answers the needs you raised. You make a very good point.

Thank you,

Ella

May 18, 2009 8:23 AM
 

Ron said:

Hi Marco,

What I know of Oslo is that it builds on a very essential link that is currently missing today in almost every platform (even SAP). That is the functional - natural language - fact models and business rules where applications and data models should build upon.

As you mention, this could be a great platform to build IT solutions (including BI) from. The short term problem you have this fundamentally changes the way people build software today. Looking at the past with business rule engines-companies and where they are today, this does indicate that the whole world must build model driven in the future to get most benefit. This is not realistic I think.

As a company we are currently looking into how to build BI systems model driven, but the handicap you have is that a BI systems is always are a snapshot of the metadata of OLTP systems at a certain point in time. When they do not synchronise metadata automatically I am afraid you end up building separate environments with separate lifecycles.

In my opinion integrated platforms (ERP + CRM + BI + ...) built upon model driven architectures might score very well in this situations.

But the next problem you run into is: what to do with external and unstructured metadata that you don't control / own?

In the end, a very interesting topic and we certainly will take a look at what Oslo can bring to the BI space. We certainly do also find that standards like SBVR play a key role in this area.  

Ron Torrico

May 18, 2009 3:56 PM
 

Brian Gager said:

Hi Marco,

This is a very timely topic for me. In my consulting capacity, I have ended up writing some custom apps to help our clients manage their metadata. There are three issues I am seeing with customers relating to metadata:

1) Ability to mine existing metadata out of the SSAS and relational databases, keeping dependencies between both.

2) Ability to easily manage an online corporate glossary and tie these business terms and definitions to elements of the data warehouse. (This to me is a huge problem on the business side that often gets overlooked by us folks on the IT side).

3) Ability to tie process/job control tables or functions to the metadata in the repository.

4) Ability to tie this in with downstream applications. For instance, if you had a metadata chain from enterprise reports --> data dictionary --> data warehouse elements -->process control/ jobs, you could really add visibility to both the IT and the business sides.

Another question for all of you: Where do you think business metadata (i.ei dictionaries)  should be stored and managed? Within BIDS? Within a separate application, like a web front end? Is anyone actually using extended properties or descriptions to house their business metadata?

I am very interested in hearing if this resonates with people or have you already found solutions out there that do all of this?

Here's a link to a webinar I did recently on this subject. One of the challenges I am finding is educating the business side about metadata and why it costs them so much money to ignore it.  http://www.tallan.com/events/pages/090416-246a4bc7c08846f48ca70645d5e4435a.html

May 22, 2009 11:33 AM
 

Marco Russo (SQLBI) said:

Brian,

thanks for your feedback.

I completely agree with you about the issues that arises in the real world. Exactly for this reason, I think that an external repository, based on a wide distributed metadata platform open to third-party extensions (and Oslo could be the right one for this) could be the base to found an ecosystem that aggregates and integrates informations coming from different sources.

However, this is just an opportunity and everything has to be written here. Limits of existing solutions are their closeness and/or their relationship with technology-specific choices that make an impact (or at least some constraint) on the BI solution architecture.

On a side note, I would only mention that in SQLBI Methodology (http://www.sqlbi.com/sqlbimethodology.aspx) we just touched the issue of dependencies between SSAS and relational detabases by making a specific use of database VIEWS, beacuse their dependencies are easily trackable with existing tools.

Marco

May 23, 2009 5:41 AM
 

Kasper de Jonge said:

Hi Marco,

I attended a presentation of MS Marcel Westra about MS MDM, available in SQL Server 2008 R2 Master Data Services (http://www.microsoft.com/sqlserver/2008/en/us/mds.aspx).I was greatly impressed by his presentation, with functions like:

"Master Data Hub that provides central management of master data entities and hierarchies and provides a comprehensive role based security model that ensures fine-grained, secure access to master data."

and

"Flexible Data Model completely defined in a metadata driven data model. Data administrators are free to define the data model in any way that satisfies the needs of their organization. As new business entities appear or current business entities change, the master data hub data model can change to accommodate the new business requirements."

it appears to be the masterdata management tool you were asking for.

What i would like to know is how this data will be made available to the organisation? By means of Dublin with entities described by Oslo? and how the BI get access to this data, Gemini to use the Berlin webservices? It would alter the way we work greatly but i like the SOA way of getting the data to not only BI but other applications in the organisation. Or would this MDM replace our datawarehouse and would we Query it to fill our cubes/reports.

So many question, but still great changes are on the horizon.

Kasper

May 25, 2009 3:20 PM
 

Marco Russo (SQLBI) said:

Uhm - I think MDM is another topic by itself. It cannot replace the data warehouse, its scope is more about what we would call the "dimensions" side of the data (qualitative attributes), but quantitative attributes still comes from OLTP systems. Data Warehouse is the central repository for these data. Metadata should be able to track data relationships between source (ie OLTP) and reports (ie Olap Cubes, Data Marts, and so on) covering all the intermediate steps (ETLs and so on).

May 26, 2009 6:46 PM
 

Johan said:

The whitepaper about metadata driven ETL architecture has somewhat remarkably been removed http://msdn.microsoft.com/en-us/library/cc313017.aspx.

MDM is about distributing likewise business keys throughout diverse apps easyfying integration efforts.

MS Oslo, for now, provides the foundational layers which will expand over the total MS stack to provide MDA engineering. Somewhat different with current MDA approaches it is able to run Apps as a model instead of codegenerated apps which 'comes from models'. The difference in MDA style , like the UML PIM camp it could serve, can be found here http://loekb.blogspot.com/2006/02/will-real-mda-please-stand-up.html. A MDA fashion also executed by Mendix.com, a 'cool vendor' stipulated by gartner.

June 1, 2009 6:08 PM
 

SQLBI - Marco Russo said:

A few days ago, I talked about Oslo and metadata issues with BI stack . I got several comments and the

June 5, 2009 12:07 PM
 

alex said:

hello

with SQL 2008 R2 does it offer the metadata management for BI solution ?

I am new to this and try to find how to put in place metadata management.

thanks

Al.

June 12, 2012 5:01 AM
 

Marco Russo (SQLBI) said:

Unfortunately neither SQL 2008 R2 nor SQL 2012 offers a built-in solution  for metadata management.

June 12, 2012 5:36 AM
 

Chris Frederick said:

Hi Marco,

Have you seen Microsoft's Project Barcelona yet?  It looks really neat.  I was impressed by the "Web Crawler" approach to collecting enterprise-wide meta-data.  Plus, they plan on letting us write our own crawlers for non-MS products (like Oracle, or Business Objects for example...).

http://blogs.msdn.com/b/project_barcelona_team_blog/

Best,

Chris

July 10, 2012 2:54 PM
 

Marco Russo (SQLBI) said:

Chris,

it looks promising but I still don't see a release coming - I hope to see some news soon.

July 10, 2012 4:43 PM
 

Octoni Simbolon said:

Hi ,

I'm interested with this topic. Recently we have build a BI Reporting Solution on SSRS platform, but the data warehouse is produced in Oracle environment (ETL & Database as well).

In the future, we have plan to build a "Business" Metadata solution to make easier for the users while browsing the report from SSRS, they could also get some answers of most questions such :

*) where does the data come from ?

*) what is the formula to get the value ?

*) what are the justification to have that kind of value ,

*) etc..

Do you suggest we should just prepare a separate repository table, insert the documentation manually,  and displayed it on SSRS ? :)

Thanks

Octoni Simbolon

July 19, 2012 11:23 PM
 

Marco Russo (SQLBI) said:

Octoni, this is a long discussion.

In my experience, existing products are not completely integrated with the development process and lack in automation, and nobody is willing to invest the time required to keep a separate set of metadata information updated.

In an ideal world, the metadata information is automatically generated by analyzing the solution. With Microsoft BI Stack this is teorically possible but in practice it hase been done only by third party tools, until now.

You might take a look to BI Documenter or similar products.

http://pragmaticworks.com/Products/Business-Intelligence/BIDocumenter/Default.aspx

July 20, 2012 7:42 AM
 

Paul B Felix said:

I realize this is an old post but it still ranks well so I'm leaving a note for others that stumble across it. LeapFrogBI is a metadata driven etl development platform designed from the ground up to streamline the creation and maintenance of SQL Server data marts. Check out this SaaS platform at LeapFrogBI.com and start reaping the benefits of metadata driven etl.

December 8, 2012 11:48 PM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement