Many times I’ve been asked by my coworkers and friend to show them how to create and work with OLAP, Cubes, SSAS, MDX and so on and so on.
Therefore I decided to start writing a simple manual on how to use SSAS from scratch.
In the examples, I will use AdventureWorksDW2008R2 as a data warehouse.
The central concept of the architecture
In the previous post
, I talked about Kimbal architecture of Datawarehouse, but again I will try to illustrate how a straightforward Business intelligence solution should look like.
From the diagram, It is clear that there are many processes to be done before we start working with designing the OLAP Cube.
For these sessions, I will not get into details about what are those processes, but I will keep strictly to the OLAP design and usage of the Microsoft SQL Server Analytical Services (SSAS) tool.
SSAS alongside with SSIS and SSRS is part of the Microsoft’s BI solution and can be accessed with opening Visual Studio, which is installed on your computer with installing Business Intelligence Development Studio add-ins to Microsoft Visual Studio.
If you are not able to access SSAS and If you had installed SQL Server 2008 from a CD or Media, then you can run the installation media, and make sure you had selected BIDS as an option, along with Management Tools.
1. Create a new solution
For creating a new project, go to New -> Project and then Choose Analysis services project.
2. Create a data source
First thing after creating a solution is creating a data source to the data warehouse that we will use.
Under Data sources -> Right Click -> New Datasource. From Datasource, vizard chooses New to create a new connection manager.
3. Choose Impersonation options