Many-to-many relationship, OLAP and MS SQL Server Analysis Services

Many-to-many relationships and MS SQL Server Analysis Services

I want to devote this post to a simple problem, as it turned out, like handling an OLAP-cube of many-to-many connections using MS SQL Server Analysis Services.


First, a little about how I approached this. The authorities said that it would be nice to deploy some analytics on the existing database. Having conducted a small Google search, it turned out that OLAP technology is perfect. And since MS SQL Server is deployed on the company’s server, the Analysis Services component is even more suitable.

Gladly rubbing my hands, I grabbed a Microsoft tutorial that comes bundled with SQL Server. Two days later I was sure that everything was on my shoulder. But it was not there ... In the existing database, most of the links between the tables turned out to be many-to-many connections, which at first did not foretell any complications. But in practice, it turns out that without additional, very meaningful waving of hands is not enough, since otherwise, the die simply gives out incorrect information.
')
In runet, finding information on this topic was not an easy task. Whether it is so self-evident to everyone, or whether the skis do not go. However, I did find a very cool, great English manual on this topic. Actually, those who understand English very well or want to understand the topic very well, it is suggested not to read further, but to leave the link: http://www.sqlbi.com/articles/many2many/

Those who, like me earlier, do not have time to read English textbooks, read on. The post does not constitute a translation of this manual: rather, a certain summation of its first examples + some basics from the Mayrosoft mentioned textbook.

Actually what's the problem. If the reader got acquainted with this post: http://habrahabr.ru/post/67272/ , then he knows that the standard schemes for Olap are the schemes "Star" and "Snowflake". And what if we were surrounded by many-to-many connections?

The easiest option is to get away from many-to-many communication using views — this will have a positive effect on the speed of processing requests. Unable to leave? Let's figure it out.

Formulation of the problem. Online store. MS SQL Server, there is a database with notorious M2M links on it, which looks like this: Table of purchase identifiers, the table of categories (food, sport, other) and the table of accounts are attached to it. Let's complicate the task: now let several people use one account at once (for example, a husband and wife make a purchase at home), respectively, a table of persons is connected via M2M. And so that it is not sugar at all: let the table of categories of people be tied to the table of persons through M2M. And we are interested in: what categories of people, what purchases do more often, and when they do it.



The example is contrived, but the problem is still visible: how to connect the future dimension with the fact table right through two M2M links? It's simple, we will tell SSAS where to look.
After completing the preliminary steps for creating dimensions (Types, Dates, Categories, Persons, Accounts) (see http://habrahabr.ru/post/67272/ ), we try to create a cube on the Sales measure (number of rows). By default, Visual Studio will offer us only three measures (Types, Accounts, Dates) - after all, only they are directly related to our measure. Having created a cube, we add the remaining two measures with our hands. Plus, we create two more auxiliary measures inside the cube that will be responsible for processing the M2M connection: Bridge Accounts Persons and Bridge Persons Categories (both are the number of rows in which tables are understandable).
Thus, we will have the following image:





We see a lot of gray boxes and the fact that Visual Studio has already processed one M2M connection: between the Bridge Persons Categories auxiliary measure and the Accounts dimension. And that's good, but not enough. If we ask our cube to give something away right now, we will not get anything good. No, measurements Dates and Accounts will do everything correctly, but here with Persons and Categories alas.

To correct this misunderstanding, let SSAS tell you where to look for information to process our requests. To do this, fill in the gray boxes on the “Use of Measurements” tab as follows: click on the gray box-> ellipsis-> type of communication; select “Many to many” -> Select the intermediate measure group, as it is written in the picture:



Voila! Everything is working. You can make sure on the next picture:



What is the magic? We have indicated SSAS, where and how to search for information about connections. Note: it is impossible, for example, to correctly fill in the intersection of the "dimension of the Categories and the measure of Sales" before you correctly fill out the "dimension of the Categories and the measure of Bridge Account Persons". In variants, only Bridge Persons Categories are dropped, since Visual Studio simply does not know any other way. But after all, auxiliary measures are also measures. And the ways for them should be indicated the same way as for ordinary (targeted) measures. As the table is filled, Visual Studio gains knowledge and offers more options.

Now we formulate a mnemonic rule about how to fill the relationship table: "Between the target measure and the target dimension, select the closest measure table to the target measure." Thus, it turned out that for the Type and Date measurements in the second column there will be a full, interesting Sales measure, and in the third auxiliary Bridge Accounts Persons. Similarly, to measure Categories.

Is it always worth filling out the link table? Not. Uninteresting information there is no need to handle. M2M connections adversely affect the performance of the cube so that, if possible, it is better to get rid of them.

Behind this all. I hope I have a useful post. At a minimum, I can say that I was looking for something similar at one time, but I did not find it. I can send the source code of the database and cube from the example.