Many-to-many filtering

Question

It's hard for me to figure out how to filter the result of a multidimensional expression.

This is my database schema.

And this is my data.

Manufacturer ╔════╦═══════════════════╗ ║ Id ║ Name ║ ╠════╬═══════════════════╣ ║ 1 ║ Awesome Computers ║ ║ 2 ║ TailSpin Toys ║ ╚════╩═══════════════════╝ Item ╔════╦═════════╦════════════════╦═══════╗ ║ Id ║ Name ║ ManufacturerId ║ Stock ║ ╠════╬═════════╬════════════════╬═══════╣ ║ 1 ║ PC ║ 1 ║ 40 ║ ║ 2 ║ Server ║ 1 ║ 10 ║ ║ 3 ║ STB ║ 2 ║ 80 ║ ║ 4 ║ Console ║ 2 ║ 50 ║ ╚════╩═════════╩════════════════╩═══════╝ Part ╔════╦══════════════════╦════════╦══════════╦═══════╗ ║ Id ║ Name ║ ItemId ║ StatusId ║ Stock ║ ╠════╬══════════════════╬════════╬══════════╬═══════╣ ║ 1 ║ MBO ║ 1 ║ 1 ║ 100 ║ ║ 2 ║ Processor ║ 1 ║ 1 ║ 100 ║ ║ 3 ║ Server MBO ║ 2 ║ 2 ║ 20 ║ ║ 4 ║ Server processor ║ 2 ║ 2 ║ 20 ║ ║ 5 ║ Main box ║ 3 ║ 2 ║ 40 ║ ║ 7 ║ Adapter ║ 3 ║ 3 ║ 30 ║ ║ 8 ║ Controller ║ 4 ║ 2 ║ 40 ║ ║ 10 ║ Adapter ║ 4 ║ 1 ║ 60 ║ ║ 11 ║ Memory card ║ 4 ║ 2 ║ 80 ║ ╚════╩══════════════════╩════════╩══════════╩═══════╝ Status ╔════╦═════════════╗ ║ Id ║ Name ║ ╠════╬═════════════╣ ║ 1 ║ No data ║ ║ 2 ║ Available ║ ║ 3 ║ Unavailable ║ ╚════╩═════════════╝ 

I imported everything into a table model solution. After that, I created two measures:

• Table: ItemStock: = SUM ([Stock])
• Table: PartStock: = SUM ([Stock])

Then I deployed the cube to the server.

By running the following MDX query ...

 SELECT NON EMPTY { [Part].[Name].CHILDREN } ON ROWS, { [Measures].[PartStock] } ON COLUMNS FROM [Model] WHERE ( { [Status].[Id].&[1] } ) 

... I get this result set ...

 ╔═══════════╦═══════════╗ ║ ║ PartStock ║ ╠═══════════╬═══════════╣ ║ Adapter ║ 60 ║ ║ MBO ║ 100 ║ ║ Processor ║ 100 ║ ╚═══════════╩═══════════╝ 

... this is normal.

However, when you run this MDX request ...

 SELECT NON EMPTY { [Item].[Name].CHILDREN } ON ROWS, { [Measures].[ItemStock] } ON COLUMNS FROM [Model] WHERE ( { [Status].[Id].&[1] } ) 

... I get this result set ...

 ╔═════════╦═══════════╗ ║ ║ ItemStock ║ ╠═════════╬═══════════╣ ║ Console ║ 50 ║ ║ PC ║ 40 ║ ║ Server ║ 10 ║ ║ STB ║ 80 ║ ╚═════════╩═══════════╝ 

I expected the items in the ItemStock to be filtered by the Part command as a many-to-many relationship. For instance. MBO, processor and adapter have links to points 1 and 4, so the result will be limited for them, and the result should be as follows:

 ╔═════════╦═══════════╗ ║ ║ ItemStock ║ ╠═════════╬═══════════╣ ║ Console ║ 50 ║ ║ PC ║ 40 ║ ╚═════════╩═══════════╝ 

What am I doing wrong?

Answer 1

MDX is unknown to me, but there is a pure explanation of SQL here.

Your model looks something like this:

Image link

To get [Items] for [Parts] with spesific [Status], I would use this pure SQL:

 SELECT Item.Name FROM Item INNER JOIN Part ON Item.Id = Part.ItemID WHERE Part.StatusID = 1; 

In your example, you can see that you are using FROM [Model], but you do not have a table named Model in your setup, so this may be VIEW or some MDX functions that you should learn. Maybe the JOIN between the tables is incorrect for the [Model] view.