Data Reflections Discussions of data warehousing, transformation and analysis by the Business Intelligence Team at Tallan.

Metadata Management- Fun stuff, right?  Depending on what your relationship to an organization’s data is, it can have many different meanings. Database developers and administrators tend to focus on technical metadata, which provides descriptive information on table structures, job streams, ETL mappings, data lineage, etc.

Business users have a need for metadata as well. Attribute definitions, business rules, formulas & calculations, and system definitions provide context to your data and help turn it into real information. Often in the form of a corporate glossary or data dictionary, business metadata can provide clarity to an organization.

One of the challenges of getting your organization to buy in on the need for Metadata Management is that it is often not well understood by business stakeholders. Have you ever been in a meeting that was called because of a dispute over the definition of a business term? Have you ever seen the same field, like “Transactions” for instance, listed on two different reports with different numbers? When business terminology is ambiguous and not well documented, productivity is lost and resources are wasted trying to understand and resolve such discrepancies. It also impacts the trust your people have in your data. And it happens over and over again in the average company. These are Metadata Management issues and they are costing you money.

In an effort to describe how Metadata Management impacts your organization, we have provided some resources that we hope will help. The first is a webinar that I have presented to businesses interested in understanding Metadata Management and some of the options available when implementing a solution. It is available here. This webinar describes the differences between business and technical metadata, outlines its role in the organization, and suggests a framework for creating a metadata repository from the ground up.

We have also posted a case study that explains what can go wrong in an organization that lacks a metadata management strategy. If you are wondering how expensive this oversight can be, the paper describes a project gone wrong because of the lack of available metadata. You may find similarities in your own company.

Half the battle with Metadata Management is in understanding what metadata is and the role it plays in your organization. We hope this content will help with that understanding and get you thinking about how much more efficient you can be with better data management.In future posts, we will get more into the nuts and bolts of implementing a solution.

This post is intended to introduce Analysis Services 2005/2008 foundational concepts.

SQL Server Analysis Services is a component included in the Microsoft SQL Server product, and its use is fully covered under the same license (which applies to Integration Services and Reporting Services as well). Like the database engine, SSAS has a range of features stratified by Express, Workgroup, Standard Edition and Enterprise Editions.

SSAS is an OLAP, multi-dimensional database. While a relational data warehouse can also be described as OLAP, products such SSAS, Cognos and Essbase have fundamental differences. These OLAP servers:

· Aggregate data from a variety of sources into a compressed format optimized for query response
· Emphasize end-user navigation with modeling capabilities such as hierarchies, or drill-paths
· Employ MDX as the standard query language for retrieving result sets
· Use XMLA as the standard communication mechanism between clients and servers
· Automate the aggregation of granular data

OLAP browsing tools, such as Excel Pivot tables, enable users to drag in measures (facts in DW terminology) and constrain and slice them by dimensional attributes (columns in a dimensional DW table). This is a typical browsing session of the AdventureWorks cube in Management Studio:

(more…)

Reporting Services Installation

Reporting Services 2008 is included as part of the installation for SQL Server 2008. To install Reporting Services, select the checkbox that says “Reporting Services” under the “Instance Features” section.

As part of the installation, a Service Account will be configured for the Report Server.

The Report Server runs under a single Windows Service using either a Built-in account or Domain Windows user account.

The report server service contains:

• Report Server Web Service
• Report Manager
• Background processing application for scheduled report processing and subscription delivery

(more…)

Two big pieces of news on the Microsoft BI front:

1. Performance Point Server is being dismantled. After the next service pack, MS will cease develop of the product as its own entity. The Monitoring & Analytics capabilities will be bundled into the enterprise version of SharePoint. The Planning application looks to be dead in the water, however.

   Although PPS was complicated and unwieldly, it had potential and an interesting vision. I don’t see the failure of any part of the MS BI stack as a positive thing. The official press release and blog reactions are here.

2. While Microsoft had previously described a major-minor release schedule as the model for SQL Server going forward, the next version sounds like an interesting departure from that. Code-named “Kilimanjaro”, the next SQL Server release has been described as minor and BI-focused.

   Two components described thus far are “Madison” - which incorporates DATAllegro technology enabling 100+ terabyte warehouses, and “Gemini”. If we can believe the hype, and in this case I think there may be something to it, Gemini will allow relatively large cubes to be built by Excel users in memory, queried and moved into SharePoint. Some details on this are here.