Again you ask, So what am I supposed to do? Well, first, you need to appoint someone on the team to the role of metadata manager. If no one owns the problem, it will not be addressed. The metadata manager is responsible for creating and implementing the metadata strategy. The ideal candidate has to know everything. No joke. If one person has to do the whole thing, he or she will need to have SQL and DBA skills. The metadata manager needs to know how to program in the Visual Studio environment and how to create and publish
We believe the following approach is a good compromise between having little or no managed metadata and building an enterprise metadata system. Our main recommendation is to concentrate on business metadata first. Make sure it is correct, complete, and maintained. Also, make sure it is accessible to the business users. Once thats done, provide a way to view the other major metadata stores. We often see a tendency to over-engineer metadata. The key to making this strategy work is to not overdo it. Heres the basic outline:
You need to know whats out there. Survey the landscape to identify the various locations, formats, and uses of metadata in SQL Server 2005. The previous section and Table 13.1 give you a starting point. Use the tools described to explore the system for metadata locations. Where there arent any tools, you will need to create query or programmatic access to the rest of the sources so you can explore and track them. Create a list of the metadata elements you find, including where they are, where they came from, who owns them, how you view and change them, and where and how you might use them.
Identify and/or define metadata that needs to be captured and managed. These are the elements you will use more broadly and therefore need to keep updated and distributed throughout the system. What you need to manage depends on a lot of factors: your organizational predisposition to actively manage metadata, the level of support for actively managing metadata on the DW/BI team, and the resources available to address the problem. At the very least, you must manage a basic level of business metadata. We will describe alternative approaches to this later in this section.
While youre at it, decide on the definitive location for each metadata element to be managed. This is the location where the element will be stored and edited. It is the source for any copies that are needed by other
Create systems to capture any business or process metadata that does not have a home. Try to use all available pre-existing metadata structures, like description fields, before you add your own metadata tables. However, you will likely identify many fields that need a place to live; the comment field in the data model spreadsheet and the Usage History table are good examples of this. If the users are the
Create programs or tools to share and synchronize metadata as needed. This primarily involves copying the metadata from its master location to whatever subsystem needs it. Fill in the description fields, the source fields, and the business
Educate the DW/BI team and key business users about the importance of metadata and the metadata strategy. Assign metadata creation and updating responsibilities.
Design and implement the delivery approach for getting business metadata out to the
Manage the metadata and monitor usage and compliance. Make sure people know the information is out there and are able to use it. Make sure the metadata is complete and current. Being able to view the metadata is the hardest part of monitoring
Even though this is the balanced strategy between nothing and too much, it is still a fair amount of work. Make sure you include time in your project plan in all development tasks to capture and manage metadata, and that you include separate
Business metadata is the most important area to address because it supports the largest and most important segment of BI stakeholdersthe userswho cant get this information any other way. In other words, the technical folks can usually dig around and find the information they need in order to understand the contents of the data warehouse. The user community, for the most part, doesnt have this skill set (thats why youre here, remember). You must provide them with an easy, accessible way to explore the contents of the DW/BI system if you want them to know whats available. Well approach the task of providing business metadata to the users from the best case to the worst case.
If your organizations data and analytical needs are such that Analysis Services can meet them, and you have
Most of the major front-end tools either provide business metadata fields in their own metadata layer, or pull directly from the metadata fields found in Analysis Services. This would include the descriptions of cubes, dimensions, and attributes, often shown to the user right in the tools interface. Therefore, filling in the few metadata fields found in Analysis Services is a critical first step to improving users understanding of the database contents.
If youre using a third-party front-end tool, you need to explore the metadata structures
Figure 13.2: Analysis Services metadata in the Report Builder model selection
Figure 13.3 shows another Analysis Services metadata feature called the AttributeHierarchyDisplayFolder property that is used in Report Builder. As we described in Chapter 7, you can use this feature to
Figure 13.3: Analysis Services display folders in the Report Builder designer
By selecting the Demographic folder, the user sees only those attributes in the Fields: pane in the lower-left portion of the window that are relevant to customer
Beyond this initial display of descriptions and the use of the display folders, Report Builder does a poor job of presenting the limited metadata thats available in the Analysis Services object model. For example, none of the additional dimension, attribute, or measure group descriptions are surfaced in the tool. If Report Builder is your primary end user ad hoc access tool, you will need to provide your users with the ability to explore the contents of the warehouse so they can learn what it contains and how to work with it.
Perhaps youve decided your query and reporting tool doesnt do an adequate job of publishing Analysis Services metadata. There are several approaches to bridging this gap; some are more work than others. If Analysis Services is your primary user database, the
Figure 13.4: The sample Analysis Management Objects browser
Notice that the browser is opened to the same perspective shown in Figure 13.2. The description shown in Report Builder comes from the Description property. This sample program illustrates how to retrieve the properties of various Analysis Services objects. It would be
If your primary user access platform is the relational engine, or if you feel like the Analysis Services properties are too limiting, you can always go back to the relational model and provide a simple set of reports that allows users to explore the metadata in the extended properties that was created from the original business process dimensional modeling spreadsheet back in Chapter 3. Figure 13.5 shows what a simple metadata exploration report for the extended properties of the DimCustomer dimension might look like.
Figure 13.5: Exploring the relational databases extended properties
The problem with this approach is its limited to the relational structures as defined in the relational system tables. Theres no intermediate layer that lets you group
If the extended properties browser still isnt enough, you can create a simple business metadata schema that will support both relational and Analysis Services databases, accommodate multiple subject areas, and allow for additional metadata fields that may not exist elsewhere. Figure 13.6 shows a basic business metadata schema.
Figure 13.6: An example Business Metadata schema
The metadata schema is
The schema is essentially a hierarchy that starts at the database level in the
Figures 13.7 through 13.10 show a series of drill-down reports based on the metadata from the MDWT_AdventureWorksDW data warehouse database. These reports show the Adventure Works metadata as it would appear in the business metadata schema.
Figure 13.7: Example databases from the Business Metadata schema
Figure 13.8: Subject areas in the MDWT_AdventureWorksDW database
Figure 13.9: Objects in the Orders subject area
Figure 13.10: Attributes of the Promotion dimension table
Figure 13.7 shows the list of databases on the machine, only a few of which have descriptions in their extended properties. Clicking on one of the databases allows the user to drill down to the next level in the Business Metadata schema.
Figure 13.8 shows the subject areas in the MDWT_AdventureWorksDW database. Note that there are two maintenance subject areas, Audit and Utility, along with the two business process dimensional models, Orders and Exchange Rates. Continuing with the drill-down, Figure 13.9 shows the objects in the Orders subject area.
A subject area essentially corresponds to the dimensions that join to a particular fact table. Finally, Figure 13.10 shows the attributes of the Promotion dimension object.
The metadata properties of the attributes shown in Figure 13.10 are just a few of the potential properties available. These reports
The Business Metadata schema is
You can find scripts to create the example business metadata schema on the
books web site (
). There are also scripts to do the
initial population of the schema based on the relational model. If you used the
data model design spreadsheet described in
, and if you populated
the metadata columns as you were supposed to, this script
If you chose to use the schema and find you need to enhance it, we
There is more metadata work to do beyond business metadata. You will need to create a suite of reports to provide quick, easy insight into whats going on across the BI system right now, and how it is changing over time. These process metadata reports often serve as a starting point for technical folks to assess the status of the system and investigate any problems. Savvy business users want to see if their queries are running and why the system is so slow. Weve seen users use these reports to identify the culprit amongst their
The process metadata reports include:
Reports on active processes in SQL Server: You can report information on current user activity directly from the SQL Server system views or system stored procedures. You can also set up SQL Profiler trace logs to capture current activity and create a historical log file.
Reports on active processes in Analysis Services (provided through object model in the SQL Server 2005 samples directory: Administrator/ ActivityViewer): You can also set up SQL Profiler trace logs to capture current activity and create a historical log file.
Reports on currently running and historical ETL processes: These can be based on log files and audit tables as described in Table 13.1. You can also view current Integration Services activity in the SQL Server Management Studio.
Reports on Reporting Services activity: You can view history using the Reporting Services execution log schema described in Table 13.1. You can monitor current activity by adding Reporting Services events to the Performance tool.
Publish these reports on the BI web site for all to see. This supports the idea of a single place to go for business information, even for the DW/BI team.
Whats interesting in the here-and-now is even more interesting over time. Each of these real-time monitoring tools has a historical
Technical metadata reporting is not the first priority for the warehouse team because most of the development tools are already built with the technical user in mind. That is, they provide the developer or operations person with direct access to the technical metadata. Much of the functionality of SQL Server Management Studio is essentially creating, browsing, and managing technical metadata. The SQL Server team has also created some add-on tools that provide extended metadata browsing capabilities. These are listed in Table 13.1 and were discussed earlier in this chapter.
A metadata system is more than just a set of table or cube definitionsit is also a set of processes that allow you to manage those tables and cubes, obtain and distribute their contents, and keep them current. If you build a separate metadata repository, nightly refreshes of the metadata are typically acceptable. The safest approach would be to trigger a refresh whenever any of the metadata values is changed.
You will also need to build processes, most likely Integration Services packages, to extract key metadata values from the systems of record and copy them to wherever they are needed. These targets could be the business metadata schema, Analysis Services cubes, or even a Report Builder model. You will likely need to create an interface to allow the DW/BI team and business users to edit the business metadata values. This then ties in with assigning maintenance responsibility and monitoring and notification reports. Bottom line, you are building a metadata system, not just filling in a few columns as part of the initial data load.
Building a Data Warehouse: With Examples in SQL Server (Expert's Voice)
The Data Warehouse Lifecycle Toolkit
The Kimball Group Reader: Relentlessly Practical Tools for Data Warehousing and Business Intelligence
The Microsoft Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset