2

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

enter image description here

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

enter image description here

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.

Has QUIT--Anony-Mousse
  • 76,138
  • 12
  • 138
  • 194
AnOldSoul
  • 4,017
  • 12
  • 57
  • 118

2 Answers2

0

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:

enter image description here

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.)

Community
  • 1
  • 1
Jeff
  • 12,555
  • 5
  • 33
  • 60
  • Yeah I tried switching the tables. When I do so, during the state at which we select input columns and predict columns I get stuck. If I take this approach its not showing the key column for fact column. Without selecting a key column I can't move forward as well :( – AnOldSoul Jun 03 '16 at 00:37
  • Does the column need to be nested? I didn't think you had to nest to get at dimension tables in general. .. – Jeff Jun 03 '16 at 01:06
  • Basically what I have done is created the dimension and fact tables separately and created relationships between them using surrogate keys. Mapping wise this relationship is one-many. Now I need a column from dimension column as an input to my mining requirement. Therefore I had to select that table as a nested table in SQL data tools in order to get access to that column. Am I doing something wrong? :( – AnOldSoul Jun 03 '16 at 02:48
0

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:

enter image description here

Daniel
  • 10,864
  • 22
  • 84
  • 115