Database Business Analyses with Microsoft Tools.

This is an example how to:

- Define your databases facts tables.

- Build data warehouse.

- Create new Dimensions.

- Create a Cube.

- Define reports based on your cube.

We shall use:

- Microsoft Server Management Studio. (Only for creating databases, steps 1-2)

- SQL Business Intelligence Development Studio. (I'll call it just Studio)

This is simple project, and there are no guarantees as usual.

Micrtosoft has created lots of tutorials, you can always have a look there:

http://msdn.microsoft.com/en-us/library/ms170228%28v=sql.105%29.aspx

Also I found this tutorial very useful: http://www.youtube.com/watch?v=ctUiHZHr-5M

Let us begin:

1) You've got your application, which is using a database. Here is the structure of the database of mine:

2) You need to create new database, based on original one, which will have statistic's data in fact tables. Fact tables are created by joins. Time table will be later generated on server.

3) When you've got your non empty data warehouse (yes, it's called this way, this last database of yours, with facts tables) you can open the Studio. Click File -> New Project -> Analysis Services Project.

4) When an act of project creation will be complete, click "New Data Source" on Solution Explorer.

5) Click New on this new window, then choose provide for SQL Server, insert your server name (dot for localhost), and select your database-warehouse's name.

5a) Important. (It took me a lot of time to generate a cube because of the authentication problems - even if connection is tested, later for analyses another login is required). I used my user name and password on "Impersonation Information" view of Data Source Wizard.

 

6) Create Data Source View. It's quite intuitive, just choose your new data source and click "next" all the time. It will generate a view of your database, like this one:

7) Create Time Dimension, by clicking "Create new dimension" on dimensions folder at Solution Explorer. Choose "Generate a time table on the server" and click "next". Then choose apropriet calendar's first and last days for your project, and select needed "Time periods" like Year or/and month and so on.

8) Create New Cube by clicking "New Cube". Use default "Use existing tables" option, on "Select Measure Group Tables" view choose your fact tables. Select desired measures (we will come back to them later). Based on your database's dependencies it will generate the rest of dimensions. (Like User Projects, User Roles, Comments in my case).

9) Now you've got the cube. In the cube structure view you will see your tables colored, yellow ones are fact tables. We almost finished, we just need to add few more measures and connect the Time table. Click add Cube Dimension on your cube and choose your Time dimension.

10) Next we need to connect it. Go to Dimension Usage view. Click on propriet time cells, and use Regular relationship type. Choose your Measure Group Column.

11) We can add new Measures from the structure view and rename them there. Just select the column you need, and usage type. (I'm saying "just" but as for me it's quite tricky)

 

12) Now we need just to process the cube. The sad truth is, that I lost lots of time here, because of magic errors, sometimes restarts helped me, but not always. Be sure you enter proper username and password in one try when it will be asked for, otherwise it's difficult to find how to login (I never found). Click "Run" in next wizard. And "Yes" on popup's.

13) When cube is deployed, you can go into Browser view and drag your dimensions onto columns and rows, you measures into the middle, this way you will get some nice stats.

14) Creating reports. Go to File -> New Project -> Report Project. On Solution Explorer click "Add new shared Data Source" choose SQL server.

15) Create new report in way you need it, I've used Query builder for it, a tool from the Report Wizard.

So as you can see there are lots of Wizards here, that means all of this is a magic. Don't worry.