When there are multiple tables (larger than one table) on the dimension in the SQL Server Analysis Service, it is important to note that the second table is set to UnknownMember with NullProcessing under KeyColumns in the field properties. What is this for? Let's take a look at the following example.
Now we have a dimension called outpatient fee program, which uses two tables V_dim_billingitemcode and V_dim_billingitemanalysiscode, of which the first sheet v_dim_ Billingitemcode and fact tables are directly associated with field IDs (dimension usage general), while the second table V_dim_billingitemanalysiscode through and tables v_dim_ The Billingitemcode connection indirectly correlates to the fact table, the field ID in the dimension, the charge code from the first table V_dim_billingitemcode, the field charge collation, the project code, and the project name from the second table v_dim_ Billingitemanalysiscode, the structure of the entire dimension is as follows:
Now if the data in the first table V_dim_billingitemcode is not associated with the data in the second table V_dim_billingitemanalysiscode, that is, the first table v_dim_ Billingitemcode a few rows of data in the second table V_dim_billingitemanalysiscode can not find the corresponding data row, then the cube will be processed when the error, the wrong type of keynotfound. The reason for this error is also very simple, because the field ID in the dimension is directly associated with the fact table and the granularity is the most granular, so it is reasonable that each value of the ID field should be able to find the corresponding other field value in the dimension, and now because the first table v_dim_ A few rows of data in Billingitemcode cannot find the corresponding data row in the second table V_dim_billingitemanalysiscode, resulting in the value of the ID field not finding the second table v_dim_ In Billingitemanalysiscode
Considerations for multiple tables on SSAS dimensions