|
Return to Newsletter Contents...
SQL Server Analysis Services - The Unified Dimensional Model (Part
1) - Why we should care
by: Troy Gottfried, Senior BI Consultant, CRCP, BECP
Semantic layers have become standard in almost all Business
Intelligence (BI) solutions implemented today.
In fact, many BI vendors require these semantic layers to be built prior
to utilizing their content creation tools, and for good reason.
Semantic layers (or meta-layers as they are sometimes called) allow
organizations to wrap business context around their raw data.
This reduces (or even eliminates, if implemented correctly) confusion and
redundancy, while simultaneously providing easier access to multiple data
sources.
So what, really, are these semantic layers?
At the most basic level, a semantic layer is an abstraction of data.
When we apply the concept of abstraction to data, we remove complexities
that are inherent to the underlying data structures, and focus more on the
relevant information that the data may possess.
For example, by utilizing a semantic layer an analyst need not worry
about which tables to link together in order to retrieve the correct information
from a query. Instead, they bypass
the query-building exercise entirely and select business-friendly terms that
represent the information they wish to see.
All the hard work is done by the semantic layer, and the correct
information is retrieved for the analyst.
The benefits of implementing these semantic layers are
many. The first advantage realized
involves the seamless integration of multiple data sources.
We can stand at the data warehousing pulpit and preach the benefits of
transforming and physically integrating organizational data all day long, but at
the end of the day, not all organizations have the means or skillsets to
successfully implement a data warehouse. Moreover,
some organizations that have both the means and skillsets have met with minimal
success in these endeavors. While
failure to implement a good concept does not make the concept irrelevant, it
does illustrate the difficulty of attempting to physically integrate data
without full ownership of the project.
Semantic layers can provide a short-cut, or a virtualization of a data
warehouse. In fact, many
organizations have implemented semantic layers as a proof-of-concept approach to
data warehousing, prior to taking the proverbial plunge.
Recognizing the above constraints, Microsoft developed a
new semantic layer for SQL Server 2005 called the Unified Dimensional Model
(UDM). The Unified Dimensional Model
series you read in this newsletter will focus on the benefits, as well as the
technical issues involved in implementing a UDM using SQL Server 2005 and
Analysis Services. While there are
as many ways to implement a UDM as there are types of organizations and data
structures, the remainder of this first article in the series will discuss the
advantages specific to SQL Server Analysis Services (SSAS).
One of the first major benefits that an end-user will find
in the UDM relates directly to the way in which hierarchies are handled.
Inherent in that statement is the fact that hierarchies are supported in
the UDM. This means that we can
hierarchically relate our dimensional data through levels we have designated
in our business logic. If a certain
measure, say ‘discount amount’, is only relevant at certain levels, it will only
be accessible at those levels. In
other words, we can associate measures and groups of measures to the dimensions
that are relevant for those measures.
Categorization is also a new concept integrated into the
UDM. We can now group relevant
dimensions, attributes, and hierarchies together into classes.
Using this concept, we can pass context to attributes such as URLs and
email address that allow actions to be taken when these hyperlinks are clicked.
Additionally, the new way in which hierarchies and categories are handled
paves the way for us to use multiple fact tables when designing
multi-dimensional data structures in SSAS.
Almost every multi-dimensional data structure I have seen
houses at least one time dimension. The
UDM now has built-in calendars for Fiscal, Natural, and a host of other
standardized time-frames that allow for creating multiple, alternate
time-hierarchies.
These are but a few examples of the benefits that can be
gained by utilizing a semantic layer, and specifically the UDM in SQL Server
2005. In the next article in the
series, we will continue to explore additional advantages including KPIs,
Perspectives, Attribute Semantics, and cube writeback capabilities.
This last concept is truly a great improvement.
Many semantic layers do not allow for both the reading and writing of
data to the data sources. With the
UDM, however, we can overcome that common hurdle and increase the ROI of using a
semantic layer. That, in and of
itself, is a good reason to care.
Go to Top |
Return to Newsletter Contents
|
