Unified Dimensional = Model

SQL Server 2005 Books Online

Unified Dimensional Model

A user who wants to retrieve information directly from a data source, = such as=20 an Enterprise Resource Planning (ERP) database, faces several = significant=20 challenges:

The contents of such data sources are frequently very hard to = understand,=20 being designed with systems and developers instead of users in mind. =
Information of interest to the user is typically distributed among = multiple heterogeneous data sources. Even if dealing only with = different=20 relational databases, the user must understand the details of each, = such as=20 the dialect of SQL that is used. Worse, those data sources might be of = very=20 different types, including not only relational databases but files and = Web=20 services.
Whereas many data sources are oriented toward holding large = quantities of=20 transaction level detail, frequently the queries that support business = decision-making involve summary, aggregated information. With = increased data=20 volumes, the time that is required to retrieve such summary values for = interactive end-user analysis can be prohibitive.
Business rules are generally not encapsulated in the data sources. = Users=20 are left to make their own interpretation of the data.

The role of a Unified Dimensional Model (UDM) is to provide a bridge = between=20 the user and the data sources. A UDM is constructed over one or more = physical=20 data sources. The user issues queries against the UDM using a variety of = client=20 tools, such as Microsoft Excel.

There are advantages to the end user even when the UDM is constructed = only as=20 a thin layer over the data source: simpler, more easily understood model = of the=20 data, isolation from heterogeneous backend data sources, and improved=20 performance for summary type queries. In some scenarios, a simple UDM = can be=20 constructed automatically. Greater investment in the construction of the = UDM can=20 generate additional benefits that accrue from the richness of metadata = that the=20 model can provide.

The UDM provides the following benefits:

Greatly enriches the user model.
Provides high performance queries supporting interactive analysis, = even=20 over large data volumes.
Captures business rules in the model to support richer = analysis.
Supports =E2=80=98closing the loop=E2=80=99: letting users can act = upon the data they see.=20

Basic End User Model

Consider an example where a user wants to compare sales with quotas = for=20 different time periods.

The sales data is stored in the main Sales and Inventory database, = which=20 contains many other tables. Even after identifying the relevant tables, = the user=20 may find that data for a single entity, such as Product, is distributed = over=20 many tables. Because referential integrity is enforced by the = application logic,=20 no relationships are defined between those tables. The Sales Quotas are = stored=20 in the database of another application. Neither database captures any = business=20 rules, such as the fact that to compare quotas with actual sales, the = date that=20 the order shipped must be used instead of the many other dates for = orders (date=20 ordered, date due, data scheduled, and so on).

Accessing the Data Sources Directly

First, consider the case where the user accesses the data sources = directly.=20 The following illustration shows an example of a query being constructed = using a=20 sample tool.

By this point, the user has made significant progress. This progress=20 includes:

Sifting through the large number of cryptically named tables to = find the=20 ones of interest.
Identifying which columns should be used to join the tables = together.
Selecting those columns that contain the details of interest, from = many=20 tables with much system-oriented detail. For example, among the 11 = columns in=20 the tables that store details about product categories, only the two = name=20 columns are actually relevant to the user.

The user is now engaged in defining where =E2=80=98outer=E2=80=99 = versus =E2=80=98inner=E2=80=99 joins should=20 be used, and how to group details to provide the required aggregates. =

However, the user faces more difficult tasks. For example, how can = the user=20 join data from the other data source? Even if one of the databases = supported=20 distributed queries, most users cannot construct the required query, and = tools=20 may provide insufficient support to the user in this task. The code = sample shows=20 one method of querying the external data.

Copy Code

SELECT Quotas.QuotaAmount, =
Quotas.EmployeeId, =E2=80=A6
FROM OPENROWSET('SQLOLEDB','seattle1';
'Sales';'MyPass',
   'SELECT * FROM Forecasts.dbo.SalesQuota=E2=80=99 ) As =
Quotas

When other data sources are used, such as Web services, the user is = presented=20 with another big hurdle in determining how to make the correct remote = calls, and=20 then how to process the returned XML to combine it with the other data. =

Finally, after this work is performed for one query, much of it must = be=20 redone for the next query, and every succeeding query.

Accessing the Data Sources by using a UDM

By contrast, the following diagram shows an example of how building a = query=20 appears to a user accessing a simple UDM constructed over these data = sources.=20

The design interface shown in this example is available in the = development=20 tools provided with Microsoft SQL Server 2005. However, any interface = that=20 supports the UDM could be used, including client tools such as Office = Excel or=20 Office Web Components (OWC), or one of the many reporting and analysis=20 tools.

The tree view on the left presents the contents of the UDM. Note the=20 following points in this example:

Only user-oriented, relevant items are exposed to the user. System = columns=20 such as row guids, or date last modified, are not visible.
The names used are friendly names, instead of the = developer-oriented=20 naming conventions employed in the underlying database.

The UDM also groups all attributes for each business entity into = separate=20 =E2=80=98dimensions=E2=80=99, such as Product, or Employee. The client = could therefore refer to=20 the Product Color, Subcategory, and Category in this example without = explicitly=20 performing joins between the many tables involved.

Columns that represent transaction values, or measurements are then = presented=20 as =E2=80=98measures=E2=80=99. For example, users are typically = interested in aggregating=20 columns such as the sales amount or sales quota. This method of = presenting the=20 data as =E2=80=98measures=E2=80=99 and =E2=80=98dimensions=E2=80=99 is = called Dimensional Modeling.

The right side of the diagram shows the elements that are included in = the=20 current query. In this case, to request =E2=80=9CSales Amount and Quota = by Product=20 Category,=E2=80=9D the user defined the query simply by dragging the = three relevant=20 items from the tree view into the right side of the design interface. = The user=20 did not have to specify the details that are required to actually access = the two=20 different data sources, or perform the correct joins between the many = tables=20 involved.

The model defines the simple default formatting: for example, the use = of=20 currency symbols. Richer formatting can also be defined, including = conditional=20 formatting, such as displaying a value in red if the value is below a = certain=20 threshold.

The same model supports various queries. For example, results can be = broken=20 down by employee just by dragging in an attribute from the Employee=20 dimension.

Extending the Basic Model

The previous example demonstrates how even a simple UDM can greatly = simplify=20 the basic exploration of data. However, there are other challenges to = consider=20 when providing users access to data. For example:

A UDM that supports many different types of queries from different = users=20 might grow to considerable size. How can we ensure that a user who is = working=20 on a particular task is not overwhelmed with irrelevant = information?
How do we support the requirements of global users, who want to = see=20 reports in their native languages?
How do we make it easy to ask all the common questions about time? = For=20 example, a user might want to show sales compared with the same period = of the=20 previous year.

This section examines some of these questions to show how the UDM = supports=20 extending the basic model to enable more advanced data exploration.

Hierarchies

Although the consolidation of all the attributes of an entity into a=20 dimension greatly simplifies the model for the user, there are = additional=20 relationships between the attributes that a simple list cannot express. = In the=20 previous case, Category, SubCategory and SKU define one of the = hierarchies in=20 which products can be organized. The UDM lets you define such = hierarchies,=20 because users frequently want to perform analysis based on such = hierarchies. For=20 example, after viewing the totals by Category, the user might drill down = to=20 SubCategory, and then drill down to the lowest SKU level,. Each = hierarchy is a=20 sequence of attributes that can be used in queries to ease such=20 drill-down/drill-up scenarios.

The following diagram is an example of how hierarchies might appear = in an=20 interface shown to the end user. The model contains several different=20 hierarchies by which products can be organized. The query that is shown = here=20 answers this question: =E2=80=9Cshow sales and quotas grouped by product = category, and=20 then broken down into subcategory.=E2=80=9D The query was defined by = dragging the=20 =E2=80=9CProducts By Category=E2=80=9D hierarchy into the grid. To view = the detailed data, the=20 user double-clicks the =E2=80=9CBike=E2=80=9D category to expand the = subcategories.

The UDM handles the details of how to move between levels of a = hierarchy. The=20 UDM also handles such details as the fact that Quotas are not available = at the=20 SubCategory level, but only at the Category level.

One special kind of hierarchy is a parent-child hierarchy, covering = entities=20 that have an involuted relationship to themselves. In the next = illustration, the=20 Employee dimension has a hierarchy named =E2=80=9CEmployees By = Organization Structure=E2=80=9D.=20 Use of this hierarchy makes it easy to navigate the parent-child = relationship=20 and analyze the rolled up values at each level of the organization. For = example,=20 the sales quota for the VP of Sales, Charles Marshall, includes the sum = of the=20 sales quotas of all his staff, plus any sales quotas associated directly = with=20 him.

Categorization

Users naturally apply categorizations to their data. For example, a = user=20 might say =E2=80=9Cthese attributes are all about an employees personal = details" or=20 "this attribute is an e-mail address.=E2=80=9D The UDM provides two = mechanisms=20 specifically aimed at providing additional value based on such=20 categorizations:

Dimensions, attributes, and other objects can be put into = semantically=20 meaningful categories, enabling the object to be used more = intelligently by a=20 client tool. For example, an attribute can be marked as being a URL. = The=20 report that contains this attribute could then enable navigation based = on the=20 values of the URL. Another attribute might be marked as being an = e-mail=20 address. In this case, a reporting client might automatically open a = new=20 e-mail upon some user action.
Measures, hierarchies, and other objects can be grouped into = folders that=20 are meaningful to the user. This grouping lets the reporting tool = display=20 large numbers of attributes in a more manageable way. For example, = there might=20 be a group of attributes labeled as =E2=80=98Customer = Demographics=E2=80=99.

Time

Time information is generally recorded in the underlying data source = by using=20 either DateTime or Date data types. Although users who are proficient in = SQL or=20 XPath can extract the date information that is required to total data by = year,=20 even they might find it difficult to compose a query for questions based = on=20 other aspects of time, such as =E2=80=9CShow sales by day of = week=E2=80=9D or =E2=80=9CShow a breakdown=20 by fiscal year, starting on July 1.=E2=80=9D

However, the UDM has a built in knowledge of time, which includes the = following calendars:

Natural
Fiscal
Reporting (=E2=80=98445=E2=80=99 etc.)
Manufacturing (13 periods)
ISO8601

Therefore, the model can include a time dimension that provides a = rich set of=20 attributes defining details of each day. The following illustration = shows the=20 results when the user elected to see the sales amount and quotas for = fiscal year=20 2001. To do this, the user simply dragged the relevant item from the = tree onto=20 the filter area. The UDM knows how to translate that user action into a = range of=20 dates, and additionally understands the business rule that says that = orders=20 shipped on those dates must be included in the query, not the orders due = or=20 ordered on those dates. The correct join is implicitly made by the UDM. =

Moreover, the UDM provides specific support for answering common = questions=20 related to time, including period-to-period comparisons such as = =E2=80=9Ccompare this=20 month with the same month last year.=E2=80=9D

Translations

In the previous examples, both the model contents and the data are = displayed=20 in a single language.. However, international users frequently have a = need to=20 view metadata in their local language.

To address this, the UDM allows translations of metadata to be = provided in=20 any language. A client application that connects using a particular = locale would=20 receive all metadata in the appropriate language.

The model can also provide translations of data. An attribute can map = to=20 different elements in the data source, and provide the translations for = those=20 elements in different languages. For example, if the user connects by = using the=20 same tool that we have been using for the previous examples, but from a = client=20 computer that has a French locale, both the UDM and the query results = would be=20 displayed in French, as shown in the illustration.

Perspectives

Although the example model used here is of very modest size, = real-world=20 models might have a much wider scope, including tens of measures and = dimensions,=20 with each dimension including tens or hundreds of attributes. Users = engaged on a=20 particular task generally do not have to see the complete model. To = avoid=20 overwhelming users with the sheer size of the model, we need the ability = to=20 define a view that shows a subset of the model.

The UDM provides such views, called perspectives. A UDM can have many = perspectives, each one presenting only a specific subset of the model = (measures,=20 dimensions, attributes, and so on) that is relevant to a particular = group of=20 users. Each perspective can then be associated with the user security = roles that=20 define the users who are permitted to see that perspective.

For example, a perspective named Seattle Inventory could be defined = that=20 includes only measures from the Inventory measure group, hides the = =E2=80=9CWarehouse By=20 Location=E2=80=9D hierarchy, and makes the default City be = =E2=80=9CSeattle=E2=80=9D.

Attribute Semantics

A UDM provides additional semantics for attributes. These semantics = are aimed=20 at making the information more easily consumable. The following are some = examples of semantics that can be applied to attributes:

Names vs. Keys: Looking at the relational database, it might not = be=20 apparent that EmployeeID is a meaningless, unique, system-generated = key. The=20 UDM resolves this issue by letting the Employee attribute have both a = key (the=20 unique EmployeeID), and a name (for example, a concatenation of = FirstName and=20 LastName). A query such as =E2=80=9Cshow the employees=E2=80=9D will = correctly distinguish=20 employees who have the same name, by using their unique IDs, but will = display=20 the meaningful name to the user.
Ordering: The values of attributes frequently must be displayed in = some=20 fixed order that is not a simple alphabetical or numeric order. The = UDM lets=20 you define a default ordering to manage this requirement. For = example:
- The days of the week appear as Sunday, Monday, Tuesday, and so = on.
- Priorities are displayed in the order High, Medium, and = Low.
Discretization: For numeric attributes, sometimes it is not useful = to=20 display each distinct value of an attribute. For example, seeing the = sales for=20 all the different prices for a product ($9.97, $10.05, = $10.10,=E2=80=A6) is much less=20 useful than seeing the sales per price range ( <$10, $10 - = $15,=E2=80=A6). The UDM=20 lets you discretize attributes into such ranges by using various = criteria.=20

Key Performance Indicators (KPIs)

Businesses frequently define Key Performance Indicators (KPIs), which = are=20 important metrics used to measure the health of the business. The UDM = allows=20 such KPIs to be created, so that businesses can group and present data = in a way=20 that is easier to understand. A KPI can also use a graphic to display = the status=20 and trend, such as a traffic light to indicate good, average, or = bad.

Each KPI in the UDM defines up to four expressions for each = performance=20 metric:

Actual value
Goal value
Status A normalized value between -1 and 1 that=20 represents the ratio of actual vs. goal (-1 is =E2=80=98very = bad=E2=80=99, 1 is =E2=80=98very=20 good=E2=80=99)
Trend A normalized value between -1 and 1 that=20 represents the trend over time (-1 is =E2=80=98getting much = worse=E2=80=99, 1 is =E2=80=98getting much=20 better=E2=80=99)

The use of KPIs lets client tools present related measures in a way = that is=20 much more immediately understood by the user. The following figure shows = an=20 example of how three KPIs, organized into display folders, might be = displayed by=20 a client tool.

Performance

Interactive exploration by users requires fast response times. This=20 requirement presents challenges given the very large data sets over = which such=20 exploration is frequently conducted.

To improve performance, the UDM provides caching services. Caches can = store=20 both the detailed data read from the underlying data source, and = pre-calculated=20 aggregate values based on that data. However, use of such cached values = might=20 imply some degree of staleness of the data. Business requirements = dictate how=20 current information must be. In some cases it may be critical to show = the latest=20 data, whereas in other cases, it might be perfectly acceptable to show = data that=20 is two hours, or two days, old.

To reflect these policies dictating the currency of the data, the UDM = allows=20 the cache to be either explicitly managed (for example, a schedule could = be=20 defined to refresh the cache daily at 2 a.m.) or transparently managed = by using=20 proactive caching. The user can specify how up-to-date the data must be, = and the=20 UDM will provide automatic cache creation and management to enable the = fastest=20 possible query response.

Analytics

The previous sections addressed how the UDM can support interactive=20 exploration of data. However, just making information available from the = underlying data sources, even if in a much more easily understood and = usable=20 form, clearly does not meet the goal of incorporating business logic = into the=20 user's model. Therefore, the UDM provides the ability to define both = simple and=20 complex calculations over the data.

Basic Analytics

Queries generally return aggregated data. For example, a typical = query shows=20 sales by category, instead of showing each and every sales order. = However, there=20 is nothing in the underlying relational data that defines how a = particular=20 measure should be aggregated. For example, sales amount can sensibly be = summed,=20 but unit price should be averaged. The UDM adds this semantic.

The method of aggregation can be defined by using a variety of = schemes:

A simple aggregation function, such as Sum, Count, Distinct Count, = Max, or=20 Min can be used.
The aggregation can be defined as being semi-additive. This means = that a=20 simple function such as Sum is used for all dimensions except Time, = where Last=20 Period is used. For example, although the Inventory Level can be = summed from=20 Product to Product Category, the inventory level for the month is not = the sum=20 of the inventory levels for each day; instead, the inventory level for = the=20 month is the inventory level of the last day in the month.
The aggregation can be based on the type of account, such as = Income vs.=20 Expense.
The aggregation can be customized to fulfill any special=20 requirements.

A UDM can also contain calculated members. These members have no = direct=20 association with the source data but are instead derived from that data. = For=20 example, a calculated member, Variance, can be defined to calculate the=20 difference between Sales and Quota.

Similarly, a UDM can define sets of entities of interest to the user: = for=20 example, the top ten customers by volume of sales, or the most important = products. These sets can then easily be used to restrict the scope of a = query to=20 a particular set of entities.

Advanced Analytics

Sometimes the calculations required by users are far more complex = than the=20 =E2=80=98Variance=E2=80=99 example given earlier. The following are some = examples of complex=20 calculations:

Show the moving three-month average for each time period.
Compare year-on-year growth for this period and the same period = last=20 year.
If sales are reported in the base currency, convert sales back to = the=20 original currency, using the daily average exchange rate at the time = of=20 sale.
Calculate the budgeted sales per category for next year as a ten = percent=20 increase over this year, and then allocate budget down to each product = based=20 on relative average sales over the last three years.

The UDM provides a rich model for defining such calculations, and is = like a=20 multi-dimensional spreadsheet, where the value of a cell can be = calculated based=20 on the values in other cells. However, even this metaphor does not = adequately=20 describe the richness of calculations in the UDM. A cell might have its = value=20 calculated not just on what the value of another cell is, but also what = the=20 value in that cell used to be. Therefore, simultaneous equations can be=20 supported; for example, profit is derived from revenue minus expense, = but=20 bonuses, which are included in the expenses, are derived from = profit.

In addition to providing the powerful Multi Dimensional Expressions = (MDX)=20 language, which is designed specifically for authoring such = calculations, the=20 UDM also enables integration with Microsoft .NET. This integration lets = stored=20 procedures and functions be written in any verifiable .NET language, = such as=20 C#.NET or Visual Basic .NET. The stored procedure or function can then = be=20 invoked from MDX for use in calculations.

The client is isolated from the details of such calculations. To = client=20 applications, it only appears that now the model has more useful = measures. In=20 the example that follows, the user is viewing various calculated = measures, based=20 on Sales, for the most profitable products sold in the United = States.

Integration with Data Mining

The ability to show data in a rich, readily understood form is = valuable, but=20 users also need the ability to infer new information from that data. =

The UDM is tightly integrated with Data Mining technology, to allow = data to=20 be mined and later to use the discovered patterns for prediction.=20

Making Data Actionable

For a user, seeing data frequently leads immediately to additional = questions,=20 or to the desire to take some action. For example:

=E2=80=9CWhat are the detailed sales that contributed to that = number?=E2=80=9D
=E2=80=9CThe quota is too low =E2=80=93 I have to increase = it.=E2=80=9D
=E2=80=9CThat looks odd =E2=80=93 I want to mark that number with = a comment.=E2=80=9D
=E2=80=9CWhat details for that promotion do we have on our Web = site?=E2=80=9D

It is not enough to present data to users in an easily understood = manner. It=20 is also necessary to make it easier for them to take action based upon = the data=20 they see.

The UDM supports this in two ways:

Allowing changes to be written back into the data
Enabling actions to be associated with the data

Writeback

The UDM is not read-only. The data can also be updated through the = UDM. In=20 the case of measures, the updates can be stored separately from the = original=20 values, as deltas to those values.

In addition, it is possible to update summary numbers. For example, = consider=20 a Budgeting scenario. The budgeted amount might eventually be known down = to a=20 detailed level (by team and account), but initially values are known = only at a=20 more summarized level (by department and account type).

Actions

The UDM supports actions as a link between the data and an action = taken based=20 on the data. The main kinds of actions are:

URL: Go to a specified URL. This type of action supports both = directing=20 the user to some URL to obtain further information, and directing the = user to=20 some Web-based application that allows a new task to be performed. For = example:
- For a product, go to the company website describing that = product.
- For a product/warehouse combination, go to the Web-based = inventory=20 management application, passing the product/warehouse as parameters, = to=20 allow the safety stock level to be increased.
Reporting: Execute a specified report. For example, for a given = product,=20 the action could execute a parameterized product report that provides = the=20 product description and the current order status.
DrillThrough: Drill through to the lowest level of detail = available. For=20 example, a user examining total sales by product and customer could = drill=20 through to view all sales transactions contributing to the total. =

The actions can be associated with particular regions of the data. = For=20 example, an action to navigate to a Web page might apply to each = product, but=20 the action to see detailed stock transfer transactions would apply to = each value=20 of Quantity by product and warehouse.

While actions are defined as part of the UDM, it is the = responsibility of the=20 client application to retrieve the details of the actions that are = applicable,=20 offer the actions to the user, and then initiate the action as required. =

Security

Access to the UDM can be controlled. The key features of security are = as=20 follows:

The UDM provides role-based security. Roles can be defined, = permissions=20 granted to the roles, and users included as members of each role. The = actual=20 permissions of a user are the union of permissions granted to each = role to=20 which the user belongs. Permissions for a role can also include = =E2=80=98strong=20 denies=E2=80=99, which remove rights regardless of other roles to = which a user might=20 belong.
Administrative permissions (for example, to change a UDM) can be = granted=20 independently of data access permissions. Also, separate permissions = can be=20 defined for reading the metadata of the object, and for read/write = access to=20 the data.
Data can be secured at levels of granularity down to individual = cells. For=20 example, you can limit the ability of users to view the sales of the = product=20 =E2=80=98Widget=E2=80=99 to customer =E2=80=98ACME=E2=80=99. Security = can also be conditional: for example, a=20 role might be allowed to see the total salary for a department only if = there=20 are more than five employees in that department.
The permissions can define whether visual totals should be used, = in which=20 case totals reflect only the lower level members to which the user has = permissions. Cell access can also be contingent, which means that = cells=20 derived from other cells are viewable only if all the other cells are = also=20 viewable. For example, if profit is derived from income and cost, then = users=20 can view profits only for products for which they have permission to = view both=20 income and cost.

Other Resources

Analysis = Services=20 Concepts [ http://msdn2.microsoft.com/en-us/library/ms176117.aspx ] =

Help and Information

Getting = SQL Server=20 2005 Assistance [ = http://msdn2.microsoft.com/en-us/library/ms166016.aspx ]=20