SSAS Workflow
Workflow Diagram
Starting with an existing relational database or your data layers, this is the database you want to provide the data for your reports or data mining activities, this is your transactional database or your OLTP database. You may have an environment with a data warehouse, this database could also be used to provide the data for your OLAP reports and data mining activities the great thing about analysis services you can use either an OLTP database or data warehouse database as a source for your data.
So to start using analysis services you need to decide where your data is going to come from and mentioned you can use OLTP or data warehouse. As we become more familiar with analysis services you begin to understand which one to use for your given situation, people often implement a data warehouse so as to not impact on response times or performance on their OLTP database when providing the analytical reports via analysis services. Using your data from your OLTP database however allows you to provide some real-time business intelligence opportunities using such features like proactive caching.
With either approach the next step is to create the logical data schema called the data source view, which is the data schema the unified dimensional model is based on. The unified dimensional model is the basis for all your work to be done in analysis services and by implementing the data source view you provide analysis services with a standard view of the underlying data.
With the data source view you’re defining the schema to be used by analysis services so this includes the design of your dimension and fact tables that can you enhance or modify the original data model at this point. You can create named queries, named calculations or even define new relationships that don’t exist in your underlying OLTP or data warehouse. The amount of work you do at this point depends on the type of database you are connecting to, or more precisely whether you are connecting to your OLTP database using the relational normalised model or your data warehouse database that is most likely using the dimensional model. So the unified dimensional model is what the data source view is representing and is most like the dimensional model but has characteristics of the relational database model, so starting with the relational model you have a little more work to do than if working from a dimensional model.
The next step in the process is to create the dimensions that you modelled in your data source view, the dimensions can be created individually by you or you can run through the OLAP cube wizard that is available. The dimensions are where you define your attributes and user defined hierarchies and set various properties on how they will function, all topics mentioned will be covered in later articles on analysis services.
Now the dimensions have been created the next step is to create the OLAP cube, the cube consists of your dimensions aswell as the facts now called measures. You can now add any additional features at this point like key performance indicators, translations and advanced calculations. Once you’ve added all the necessary features to your cube, your project and cube are ready for deployment.When you deploy an analysis services project for the first time a new analysis services database will be created on the instance of SQL Server you specify, this database will contain all the information needed to display and serve up your cube data. Analysis server uses several methods for storing your data that you can set when setting the deployment options for your project. You can now browse and manage your database using SQL Server Management studio, instead of connecting to the database engine though you’ll specify analysis services as a server type while connecting to your database instance.
Finally now your OLAP cube is deployed you’ll be able to create reports and browse the data. Microsoft Excel is good choice for many people and you can use many third party reporting applications that can work with OLAP data. SQL Server reporting services and SharePoint are also able to integrate with analysis services and provide you with several powerful reporting options. At the end of the articles on SQL Server Analysis Server I’ll cover these options and how to get these up and running.
So that’s the very high level overview of the entire workflow process, and isn’t detailed in anyone topic on purpose as the series will dig into each item individually.
Conclusion
- You can use either your OLTP or data warehouse database with analysis services 2005 as the source for your OLAP reporting.
- The unified dimensional model is used to represent your data in a standard format within analysis services and is really the starting point for all your work in analysis services.
- The OLAP cube is deployed to an OLAP database which is separate from your OLTP or your data warehouse database that are relational databases.
- Several reporting options are available that were include in the workflow diagram, like reporting services, SharePoint, Microsoft Office and more.