Database Reference
In-Depth Information
Chapter 9
Beginning the SSAS Project
Every new beginning comes from some other beginning's end.
—Seneca
For more than a decade now, alongside its relational database server, Microsoft has provided a multidimensional
database server, also known as a SQL Server Analysis Services (SSAS) cube. In all of that time, this premier
multidimensional SSAS cube server has proven itself to be both a cost-effective and powerful addition to BI
solutions around the world.
The purpose of SSAS is to provide high-performance reporting data. Reports created on a multidimensional
cube run faster compared to reports built upon a set of relational tables. As usual, faster performance means
more complexity. Creating SSAS cubes demands that you understand dimensional data in a completely new way.
It requires that you understand the difference between SSAS cubes and dimensions. It even requires you to learn
new development and administrative tools, plus four programming languages (MDX, XMLA, DMX, and DAX), if
you want to master SSAS.
Microsoft has combated this complexity by adding wizards to its development tools that walk you through
the process of creating SSAS cubes. These wizards make it easy for new developers to start building cubes and
keeps novice cube developers on the right track.
In this chapter, we begin the process of creating complex SSAS cubes, by developing the dimensions they are
comprised of. We detail the difference between cubes and dimensions and examine how to use the development
and management tools. To start, let's look at the differences between SQL Server and SSAS databases.
SQL Server vs. Analysis Server Databases
The most noticeable difference between a traditional SQL Server database and an SSAS database is SSAS's
increase in reporting performance. SSAS is designed to create and store aggregate values, and it has a very
different design structure. Let's take a moment to examine these differences.
In a SQL Server relational database, the tables and databases are logical constructs representing data
stored in physical files on the hard drive (Figure
9-1
). Each SQL Server database has at least two files, but the
SQL server engine makes it appear as if it is one object. The master data file (
.mdf
) holds table data in a binary
format. The log file (
.ldf
) holds a running account of changes made to the
.mdf
file. Whenever you add a table
to the database or add a row of data to a table, the log file records the change and the
.mdf
file receives the data.
This design strongly protects transactional statements that add, modify, or remove data in the database from any
inconsistencies.
