From Web to Web - MS BI demo

What are the Microsoft Business Intelligence tools (stack) and what are they used for?
Here I give you one easy and understandable demonstration of the SSIS, SSAS and SSRS tools using MS SQL Database and some sample data from a weather website.

Let's say that you want to do your own report and graphics of the temperatures of London.
What do you need? A machine (I am using a small VM), a database, where to store the data (in my case I choose MS SQL Database), the MS BI tools (SSIS, SSAS, SSRS) and some time :).
The idea is the following:
1. There is data of the temperature in a website. For every day we have the highest temperature and the lowest and some more data like humidity, pressure, etc.
2. The website allows us to download this data for specific period (in my case the month of January 2014) into a CSV file. Each line in the file is a day with it's data. The file has 31 lines.
3. Using the SSIS (Microsoft Visual Studio) we create a SSIS projects and create a package, which i Extracting the data from the file, Transforming it and Loading it into the database (ETL).
4. Using the SSAS (Microsoft Visual Studio) we create a SSAS project with a cube, dimensions and measures based on the data loaded into the database. We need to configure the SSAS server and deploy the SSAS project into it in order to be accessible by the SSRS.
5. Using the SSRS (Microsoft Visual Studio) we create a SSRS project with a report containing graphic and table with the temperature data based on the cube from the SSAS project.
6. Configuring the SSRS local server and publishing/deploying the report into it will allow us to visualize the report directly into our web browser. In this way the cycle is closed (from Web to Web).

So...MS BI is a set of tools allowing you to Extract, Test and Load data and then perform Analysis and at the end generate Reports of those analysis.
Somebody will say: "Why do you need all of this when it's possible to show the results directly from the web site using some scripting?". For the current example this is true, you don't need to have all of this, but for a complex system which has 10 or more different datasources (web sites, files, etc.) and If you want to create a lot of different reports, then you need this.
The cool things about the MS BI stack are:
- There are 3 separate parts (ETL, Analysis, Reports). Splitting the work.
- It is able to work with different databases (at the same time).
- The design is very visual, so the developing and debugging are easier.
- Migration is easy, everything is save as separate projects.
This is BI, it's the future and I like it :)

SSIS - How to process SSAS cubes

In this post I will show how to create, configure and monitor a processment of cubes using Microsoft SSIS package.
I'll start from the beginnig:
1. Create a new SSIS project:

Automatically a SSIS package is created with a name "Package.dtsx". If you want you can rename it.

2. Create Data Sources. You need to create the data source of the cubes (where are located your cube/s). And also my recomendation is to create the data source of the log database - MS SQL Server Database. In this database will be stored a log of all actions that the SSIS package will perform. This is one way to monitor the SSIS package activities and also keep history and even create statistics.

2a. Create SSAS Cubes Data Source:

You can check this check box in order next time to not appear this kind of "warning" message.

2b. Create SQL Server Database Data Source
In the same way like the creation of the Cube Data Source, just using the SQL Server Database connector and connecting to a SQL Server database.

3. Creation of Analysis Services Processing Task:

We will use just this type of task to process cubes and/or dimensions.

4. Configuring the Dimensions processment

4a. You can rename the Task:

4b. Add connection to the Cubes SSAS database:

4c. Add the cube first:

4d. Add all related dimensions to this cube:

4e. Remove the cube (leave only the dimensions):

5. Add another Analysis Processment task (you can copy paste the previous) and configured it in the same way like the dimensions, but this time just leave the cube (steps 4b - 4c).

6. Connect the 2 tasks. When the first task is completed successfully the execution flow will execute the second task:

7. Test the package (execute it):

Done.