Databases
StackOverflow2013
Postsdbo
POIntersection in MDX
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIntersection in MDX
    primarykey
    data
    text
    <p>I recently ran into a problem in our SQL Server 2008 Analysis Services Cube. Imagine you have a simple sales data warehouse with orders and products. Each order can be associated with several products, and each product can be contained in several orders. So the data warehouse consists out of at least 3 tables: One for the Products, one for the Orders and one for the reference table, modelling the n:n relationship between both. The question I want our cube to answer is: How many orders are there which contain both product x and product y? In SQL, this is easy:</p> <pre><code>select orderid from dbo.OrderRefProduct where ProductID = 1 intersect select orderid from dbo.OrderRefProduct where ProductID = 3 </code></pre> <p>Since I am fairly proficient in SQL, but a newbie in MDX, I have been unable to implement that in MDX. I have tried using distinct count measures, the MDX-functions <code>intersect</code> and <code>nonempty</code> and subcubes. I also tried duplicating the dimensions logically (by adding the dimension to the cube twice) as well as physically (by duplicating the data source table and the dimension).</p> <p>On <a href="http://www.zeitz.net/thts/intersection.zip" rel="nofollow">http://www.zeitz.net/thts/intersection.zip</a>, you can download a zip file of 25kB size which contains an SQL script with some test data and the Analysis Services Solution using the tables.</p> <p>We are using SQL Server 2008 R2 and its Analysis Services counterpart. Performance considerations are not that important, as the data volume is rather low (millions of rows) compared to the other measure groups included in that cube (billions of rows).</p> <p>The ultimate goal would be to be able to use the desired functionality in standard OLAP (custom calculated measures are ok), since Excel is our primary frontend, and our customers would like to choose their Products from the dimension list and get the correct result in the cube measures. But even a working standalone MDX-Query would greatly help.</p> <p>Thank you!</p> <p><strong>Edit March 12th</strong> Did I miss something or can't this be solved somehow?</p> <p>If it helps to build the mdx, here is another way to get the results in sql, using subquerys. It can be further nested.</p> <pre><code>select distinct b.orderid from ( select distinct orderid from dbo.OrderRefProduct where ProductID = 1 ) a join dbo.OrderRefProduct b on (a.orderid = b.orderid) where ProductID = 3 </code></pre> <p>I tried something like this with subcubes in mdx, but didn't manage to succeed.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USThomas Tschernich
    1. USThomas Tschernich
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POIntersection in MDX
      1. USThomas Tschernich
      1. VTBountyStart
    2. VO
      singulars
      1. POIntersection in MDX
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POIntersection in MDX
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload