Unable to use the dimension table as a nested table in SQL server data tools

Question

I have the following relationship set up between my fact table and dimension tables.

When trying to create a data mining structure, I had to choose the dimension table Dimension_Status as a nested table for the fact table as I'm trying to predict the probability of "TimelyResponse" in the fact table using the "IssuedVia" in the Dimension_Status table. But when trying to do so, I get the following error.

Dimension_Status table cannot be used as a nested table because it does not have a many-to-one relationship with the case table.  You need to create a many-to-one relationship between the two tables in the data source file

What am I doing wrong here? Why am I getting this error though my dimension tables are maintaining a many to one relationship with the fact table? Please advice.

Answer 1

I could be completely missing the mark here (I haven't done a great deal of data-mining using SSAS), but from what I can tell nested tables are the "Many" side of a many-to-many relationship. From the MSDN article on Nested Tables it shows the "Products" table as being nested in the "Customer" table, because each Customer can have many Products:

In this diagram, the first table, which is the parent table, contains information about customers, and associates a unique identifier for each customer. The second table, the child table, contains the purchases for each customer. The purchases in the child table are related to the parent table by the unique identifier, the CustomerKey column. The third table in the diagram shows the two tables combined.

A nested table is represented in the case table as a special column that has a data type of TABLE. For any particular case row, this kind of column contains selected rows from the child table that pertain to the parent table.

So it looks like nested tables are not what you're after - unfortunately I'm not familiar enough with the SSA data mining tools to recommend the appropriate approach (unless switching them around and making the DimStatus table your Case table and Fact_CustomerComplaints your Nested table will work in your situation.)

Answer 2

To put it simply, your arrows are backwards.

Reverse the relationships so the tables you want to be nested are pointing to your Fact_ table.

Like so: