This chapter explains how to build and configure most commonly encountered SQL Server Analysis Services (SSAS) dimensions. Each Analysis Services multidimensional project consists of dimensions and cubes; knowing how to define and customize dimensions is essential for building successful business intelligence solutions. Analysis Services builds data structures based on one or multiple relational data sources. Therefore, before creating dimensions and cubes, you need to define data sources and their corresponding data source views—layers of abstraction between the relational database and Analysis Services objects. After you define data source views, you can create simple dimensions using a dimension wizard and subsequently customize and enhance dimensions as needed using a dimension editor.

SQL Server Data Tools (SSDT) is the primary client tool for developing Analysis Services solutions. In prior versions of SQL Server, the same tool was called Business Intelligence Development...

Each Analysis Services project could use multiple data sources. The traditional approach is to first build a staging relational database where you import data from various data repositories within your enterprise. Subsequently, you would build a dimensional model using a Star or Snowflake schema, as opposed to a normalized model you would typically use for a transactional database, for your data warehouse that has fact and dimension tables. Lastly, you build the Analysis Services solution using the dimensional model within the relational data source. This approach is still recommended, because it allows you to have more control over your data cleansing routines prior to building Analysis Services objects.

On the other hand, SSDT does give you the flexibility to connect to various relational databases and define necessary data structures within data source views, if you don't have the luxury of building the staging area or the star schema database. However, this flexibility...

Data source view (DSV) serves as a layer of abstraction between a relational data source and SSAS objects. You can import existing tables and views from relational sources into a DSV. DSVs also allow defining new columns and views (named calculations and named queries, respectively) based on the existing structures within your data sources. Let's learn how to create and enhance DSVs.

While creating a data source view, you noticed that SSDT automatically detected primary and foreign keys between DimDate and fact tables and established their respective relationships. If our data source does not have referential integrity constraints, we would have to define the logical primary keys and necessary relationships directly within the DSV.

To set up relationships between two objects within a DSV, dimension objects (tables or views) must have a primary key column and fact tables must reference this column. The column names in fact and dimension tables do not need to match, but they must have compatible data types. For example, you cannot create a relationship between an integer and string columns.

Now that you know how to create a DSV, you are ready to learn how to extend it. In the previous recipe we imported a few tables from a relational data source, but that might not be sufficient to complete your data model. You may need to import objects from multiple data sources, define calculations, or add new data views.

Named calculations allow extending an existing object to include data structures necessary for defining a dimension. The most common example is of combining the first and last name columns into a single column named as full_name. Similarly, we could concatenate the quarter and year columns to define a full description for each calendar quarter, as in Quarter 2, 2013. If you have sufficient access to the relational database, you have an option of creating any views you need for building dimensions; this approach is favored by many data warehouse and cube developers. However, it is also plausible that you won't have permission to create or alter relational objects. There is no need to worry though; named calculations are here to help. For example, suppose you have the Employee dimension based on the DimEmployee table, which includes the FirstName and LastName columns. This could be great for relational design, but you'll need to define a named calculation...

The Analysis Services dimension allows examining data across a specific business vertical. For example, the Product dimension allows managers to see which products generated the most revenue, and the Geography dimension helps determine the most profitable regions. Each dimension is based on a corresponding table or view in the relational data source; dimension table's columns will correspond to SSAS dimension attributes. Analysis Services allows creation of very simple as well as very complex dimension structures. There are many dimension and attribute properties which let us customize each dimension to meet specific business requirements. Let's begin by creating a simple dimension and then working our way through more complex ones.

Analysis Services hierarchies specify the navigation path for browsing dimension data. There are two types of hierarchies: attribute and user-defined hierarchies. For example, in the promotion dimension we just created, we immediately have a hierarchy for each attribute: Promotion Name, Start Date, End Date, English Promotion Category, English Promotion Type, Discount Percent, Minimum Quantity, and Maximum Quantity. Attribute hierarchies contain two levels.

The top-level is referred to as the All level and includes the aggregated value for all the members included in the attribute. For example, the English Promotion Category attribute hierarchy's All level will show the aggregate value of sales for all the promotion categories: Customer, No Discount, and Reseller. The other level in the same hierarchy will show individual promotion categories, such as Customer.

User hierarchies combine multiple attributes and must be defined explicitly. In this recipe we will...

While running the Dimension Wizard to create the Promotion dimension, you learned that each attribute has name and key properties. Choosing appropriate columns for these properties is essential for having the correct attribute design, so we will discuss them in greater detail here.