Table not joined error with DSO | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Table not joined error with DSO

I’m trying to create and process a cube using the Decesion Support Objects (DSO). The situation is this: Cube Design:
Fact table: Client. Used for distinct counts. Number of clients being our measure. Dimension: Ethnicity. The ethnicity is also stored in the client table but as a code. The code translates into a human readable text in the "EthnicityTypes" table. What I’d like to have happen: I’d like to have an ethnicity dimension that shows the text as opposed to the code. Approach so far:
dimension = (DSO.Dimension)database.Dimensions.AddNew("EthnicityDesc", DSO.SubClassTypes.sbclsRegular);
dimension.DataSource = dataSource;
dimension.FromClause="cmClient, cmComboBoxItem";
dimension.JoinClause="cmClient.Ethnicity = cmComboBoxItem.item AND cmComboBoxItem.combobox=’ethnicity’ AND cmComboBoxItem=’CMCLN’";
/*
The cmComboBoxItem table contains lots of different values used to translate codes to text. The filter here combobox=’ethnicity’ and ‘CMCLN’ are used to make sure that we’re
just joining to ethnicity codes.
*/ lvl = (DSO.Level)dimension.Levels.AddNew("EthnicityDesc", DSO.SubClassTypes.sbclsRegular);
lvl.MemberKeyColumn=""cmComboBoxItem"."item"";//Code
lvl.MemberNameColumn=""cmComboBoxItem"."itemDesc"";//Text Description
lvl.ColumnSize = 10;
lvl.ColumnType = 130;
lvl.EstimatedSize=1;
dimension.Update();
Error Message:
A table was detected in the schema that is not joined to any other table. Any help is greatly appreciated… been beating my head on the wall here.
Both the dimension and the cube must have their from and join clauses specified.<br />So, when you add the dimension to the cube (which I don’t see you doing here), make sure you set the cube’s from and join clauses.<br /><br />This code worked for me, even though it appeared that I would add the fact table more than once, when AS processed the cube it removed the redundancies.<br /><br /> Public Sub add_cube_dimensions()<br /> Dim database_dim As DSO.DbDimension<br /> Dim cube_dim As DSO.CubeDimension<br /> Dim s_string As String<br /> For Each database_dim In m_dsoDatabase.Dimensions<br /> cube_dim = m_dsoCube.Dimensions.AddNew(database_dim.Name)<br /> m_dsoCube.FromClause = m_dsoCube.FromClause & "," & database_dim.FromClause<br /> If m_dsoCube.JoinClause Is Nothing Then<br /> m_dsoCube.JoinClause = database_dim.JoinClause<br /> Else<br /> m_dsoCube.JoinClause = m_dsoCube.JoinClause & " AND " & database_dim.JoinClause<br /> End If<br /> Next<br /> End Sub<br /><br /><br /><img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />Kristin
]]>