Geekly Articles each Day
📜 ⬆️ ⬇️

Create an OLAP cube. Part 2

OLAP

So, we continue to create a cube.
I will remind that in the previous article , we created Data Warehouse for storage of voices of a habr-user for a habr-topic. For those who want to start creating a cube right away, I laid out a script that creates and fills the repository (on my machine, the script took 10 minutes and generated 1866268 habr-voices).
In order to create an OLAP cube, we need:


Let's start with.


')
Start Business Intelligence Studio, menu File-> New-> Project, select Analysis Services Project in the tab Business Intelligence Projects:

New project

Call the project (I called the HabraCube ).
In the created project, in Solution Explorer we see:

Solution Explorer

Something suggests that you just need to “fill in” daddies from top to bottom - and yes, right click on the Data Sources -> New Data Sources folder.
In the Data Source Wizard window we create a connection to the HabraDW database, as we can see - anything can be a storage, as long as there is a driver through which you can get data.

Go to the next level - right click on the daddy Data Source Views -> New Data Source View.
In the Data Source View Wizard window, we select the created connection to HabraDW (by default you will call it Habra DW), and on the next page we see the labels of our repository.

Data Source View

I note the convenience of the Add Related Tables button - in case your storage contains a lot of tables, it is sometimes difficult to select the ones that will be needed for the cube. The Add Related Tables button adds all the tables on which the currently selected depends, that is, by selecting, for example, a fact table, you can transfer the dimension tables needed for these facts in one click.

So, we complete the wizard, we see the familiar table schema and go to the next level.

Right click on the daddy Cube -> New Cube (I know, I know, so far everything is very simple, but no one said it would be difficult ;-)).

In the Cube Wizard window, leave the default cube creation mode from the Data Source, and Auto build, create attributes and hierarchies. Next, select our Habra DW data source view, created in the previous step, and after a short analysis of the storage metadata by the studio, proceed to the identification of facts and measurements.

Identify Facts

As you can see, for us we have already chosen the table FactHabravote as a fact table and all Dim * tables as dimension tables. All we need to specify is the time dimension at the top. Remember, I once said that time is given special importance in OLAP, this is where we need to carefully define it. Go to the next page of the wizard and map map from the DimTime table with logical time definitions (year, month, day, day of the week, etc.).
My mapping is as follows:

The next page shows us the numerical data ( Measures ), which we can analyze in a cube - in our case, we leave the “Vote” option selected - this is the actual voice value, and the Fact Habravote Count is the number of records in the table (this measure can be immediately renamed, for example in votes count).

Next, go to the Review New Dimensions page, make sure that the measurements look the way we want, and we are surprised that in the DimTime measurement the studio itself has defined the hierarchy Calendar Year - Month Name - Full Date, consisting of year, month and day.

Review New Dimensions

I note that the definition of hierarchy is not related to the fact that before that we specified the DimTime table as a time dimension, that is, if we had a logical hierarchy in the data, for example, in the DimPost table, we will say Category-> Blog-> Post Title, the studio would also define it and build it with high probability, analyzing all or part of the data itself.

Well, on the last page, we give a beautiful name to our cube (for example, HabraCube ) and click the Finish button.

The cube is ready, and our Solution Explorer now takes the following form:

Solution Explorer After

Without going into the details of what was finally generated (about this in the next article), let's zaplishim our cube to the server and prepare it for work.
Right click on the HabraCube project (yes, almost everything is done by right click) - select Properties and on the Deployment tab, specify the Server on which Analysis Services and the cube database name are running (by default HabraCube suits us).
So, one more right click on the HabraCube project, select Process ..., and in a few seconds - the Run button ...

If you did everything correctly, services are running on the server and your Windows account has access to it and the rights to create a multidimensional database, then you will receive a joyful window and the inscription Process succeeded.

Process

My congratulations!
What now? Well ... I would leave the analysis of the cube itself for the next posts, but who will be very impatient - after closing the Process windows, go to the farthest Browser tab in the open cube editing window (if you didn’t touch anything, then it should be opened in front of you) :

Browser bar

Well, then - completely on your imagination. Here's what, for example, turned out for me:

Browser Result

Conclusion



And yet - how effective is the use of wizards? I will say this - for the average production of the system, after the wizards there is still a lot of need to "file". The wizards themselves were created more for presentation purposes, and they help a lot if you knew about cubes only from the school geometry course before.
But, as in any other area, only “manually” you can fully convey all the subtleties and build the most effective system, therefore, using the wizard you need to know what it is you will generate, and where you need to correct it, generates.

Announcements of the following series:

(to be continued...)

Source: https://habr.com/ru/post/67731/

More articles:


All Articles