The SSAS project includes several components that should be
present in order to develop an Analysis project. The Picture below describes
the process in general.
Figure 1 - General overview of the Analysis Services
Project
Your work environment may include several databases (OLTP -
Online Transaction Processing database) that store information in an optimized
way for Atomic transactions (Insert, Update, etc.). This would describe the
first rectangle which includes databases for different departments in an
organization. You may also have created a data warehouse database that you
would fill each end of the day with data from other various systems. Note that
the interaction between the OLTP databases and the data warehouse database
could be developed using the SSIS (SQL Server Integration Services). Also, the
interface between the first and the second rectangle is SQL Server Integration
Services is simply abbreviated as IS.
Please note that your current databases does not need to be
MS SQL based, however, it would be of great help to you to be MS SQL based
especially if you are going to use proactive caching and other built in
features between the MS SQL database engine and SSAS engine.
Once you have your data sources available for Analysis
project and created them in your model, you can start by creating your Unified
Dimensional Model (UDM). You can find more about these concepts in the previous
article called Analysis Services (Concepts). If you
take a look in the middle rectangle, this is where the most important things
take place. I am going to stress here that the UDM is only a logical model of
the databases and does not affect in any way the underlying databases (or data
sources). So for example, if you create a new query in the data source views,
it is not stored in the original database. It is also the case if you establish
a new relationship between your tables.
As you can see in the UDM model, you must create data source
views as the first step, which is simply a logical data model that specifies
your data coming from various sources.
Using the data source views, you create your own new queries
(and save them as views) that can contains new calculations or even in the same
table create a new calculated fields. Also included in the data source views is
the option to delete and create relationships between different tables.
Using the UDM model, you can create the dimensions (think of
dimensions like primary code tables like branch or currency table). The dimensions
can be as simple as one table or as complex to include many tables per one
dimension.
Based on the created dimensions as well as at least one fact
table that include the data to be represented as report (usually a transaction
table), we can start generating a new Online Analytical Processing (OLAP) cube
that would filter the data using the dimensions. SSAS allows you to create KPI
(Key performance indicators) as well as include different languages translation
(Not for the fact data). Once the OLAP cube is present, you can use different
reporting tools including, but not limited to, SQL Server reporting Services
(SSRS), Microsoft Office (especially Excel) as well Microsoft SharePoint.
3rd party tools for reporting are also available, but they
will not be discussed in this series.
