Knight's Microsoft® Business Intelligence 24-Hour Trainer: Leveraging Microsoft SQL Server® Integration, Analysis, and Reporting Services with Excel® and SharePoint®

11

Understanding SSAS

Now that you’ve loaded your data warehouse, you may find that with all that great data, your

report backlog is starting to grow. Most organizations that we ask have a report backlog of

50 or more reports and a report writer who is constantly battling to catch up. SQL Server

Analysis Services (SSAS) to the rescue! SSAS allows your users to create complex ad hoc reports

without having to involve IT.

SSAS is another service that you’ve already licensed if you’ve purchased SQL Server, one that

can pre-aggregate data and make it easier for your users to query data via familiar interfaces

like Excel or Reporting Services. SSAS has two major functions: a high-performance cubing

technology and a data-mining technology. We focus on the cubing technology until Lesson 19,

when you’ll get your hands dirty in data mining.

As you may remember, a data warehouse is made up of fact and dimension tables. The fact

tables hold measures, which are the numeric columns that you want to put on the report. The

dimensions hold the columns in which you’d like to filter your data. SSAS adopts many of the

same items but has changed their names. For example, measure groups in SSAS are collections

of measures within a single fact table. The columns in dimensions (Product Name, Gender,

City, or State, for example) are called attributes.

To develop an SSAS database you need to create an SSAS projec ...