Create an OLAP cube. Part 2

So, we continue to create a cube.
Let me remind you that in the previous article , we created a Data Warehouse to store the votes of Habr users for Habr topics. For those who want to start immediately creating a cube, I laid out a script that creates and fills the repository (on my machine, the script took 10 minutes and generated 1866,268 habr-voices).
In order to create an OLAP cube, we need:

• SQL Server on which our HabraDW is stored (anyone will do);
• Microsoft SQL Server running Analysis Services (2005/2008);
• Business Intelligence Studio , which is included in the package of client applications for Microsoft SQL Server, and integrates with Visual Studio if you have it installed (2005/2008);

Let's start with

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



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



Something tells you that you just need to "fill in" the folders from top to bottom - and yes, right-click on the Data Sources -> New Data Sources folder.
In the Data Source Wizard window, create a connection to the HabraDW database , as you can see - anything can be the repository, if only there is a driver through which you can get data.

We go to the next level - right-click on the folder Data Source Views -> New Data Source View.
In the Data Source View Wizardwe select the created connection to HabraDW (by default it will be called Habra DW), and on the next page we see the signs of our repository.



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

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

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

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



As you can see, we have already selected the FactHabravote table as a fact table and all Dim * tables as dimension tables. All we need to specify for ourselves is the measurement of time at the top. Remember, I once said that particular importance is given to time in OLAP, and here we must carefully determine it. We go to the next page of the wizard and map the columns from the DimTime table with logical definitions of time (year, month, day, day of the week, etc.).
My mapping is as follows:

• Year -> CalendarYear
• Month -> MonthName
• Date -> FullDate
• Day of Week -> DayNameOfWeek
• Day of Month -> DayNumberOfMonth

The next page shows us the Measures that we can analyze in the cube - in our case, leave the Vote measure selected - this is the actual value of the vote, and Fact Habravote Count is the number of entries in the table (this measure can be renamed right away, for example , in Votes Count).

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



I note that the fact of defining a hierarchy has nothing to do with the fact that before that we indicated a DimTime table as a time dimension, that is, if we had a logical hierarchy in the data, for example, in the DimPost table, say Category-> Blog-> Post Title, then the studio would also identify and build it with a high probability, having analyzed 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:



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

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



My congratulations!
What now? Well ... I would leave the cube analysis itself for the next posts, but I’m 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, it should just be open in front of you) :



Well, then on completely to your imagination. Here is, for example, what happened to me:

Conclusion

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

Announcements of the following series:

• Quite a bit of XMLA (oh!), And also, we parse and correct what the wizards generated for us;
• Analyze the cube!
• Mdx

(to be continued...)