Known problems in Relational MR Database CDSC

InputDatasource(myInputDataSource)
ConnectionString = "Provider=mrOleDB.Provider.2; _
Data Source=mrRdbDsc2; _
Initial Catalog=C:\Work\ROJECTX.mdd; _
Location="Provider=MSOLEDBSQL.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=PROJECTX;Data Source=LocalHost"; _
MR Init Project=PROJECTX"
SelectQuery = "SELECT * from Hdata"
End InputDatasource

Metadata(en-US, Question, Label)
LoopQ "LoopQ" loop [0..2] fields -
(
Q1 "This is a question in the loop"
categorical [1..1]
{
A "A" exclusive,
B "B" exclusive,
C "C" exclusive,
D "D" exclusive,
E "E" exclusive
};

)expand;
End Metadata

OutputDataSource(myOutputDataSource)
ConnectionString = "Provider=mrOleDB.Provider.2; _
Data Source=mrRdbDsc2; _
Initial Catalog=''; _
Location='Provider=MSOLEDBSQL.1; _
Integrated Security=SSPI; _
Persist Security Info=False; _
Initial Catalog=PROJECTY; _
Data Source=LocalHost'; _
MR Init Project=PROJECTY"
MetaDataOutputName = "C:\Work\PROJECTY.mdd"
End OutputDataSource

Because the RDB DSC2 does not support unexpanded level variables under expanded level variables, another version of the household.mdd file and associated, backup relational database have been added to the UNICOM Intelligence Developer Documentation Library. These new files are installed to:

Unable to convert unexpanded loop 'LOOP_NAME' to an expanded loop. To convert the loop, create a new project and transfer the existing data to the new database.

Metadata(en-GB, Question, Label)
  LoopQ "LoopQ" loop
    {
      X "X",
      Y "Y",
      Z "Z"
    } fields -
    (Q1 "This is a question in the loop"
       categorical [1..1]
       {A "A" exclusive,
        B "B" exclusive,
        C "C" exclusive,
        D "D" exclusive,
        E "E" exclusive
       };
    Q2 "This is another question in the loop"
    long;
    );
End Metadata

InputDatasource(myInputDataSource)
ConnectionString = "Provider=mrOleDB.Provider.2;_
Data Source=mrRdbDsc2;_
Initial Catalog=C:\Program Files\IBM\SPSS\DataCollection\7\Interviewer Server\FMRoot\Master\ProjectX\ProjectX.mdd;_
Location="Provider=MSOLEDBSQL.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ProjectX;Data Source=LocalHost";_
MR Init Project=ProjectX"
SelectQuery = "SELECT * FROM HDATA"
End InputDatasource

Metadata(en-US, Question, Label)
LoopQ "LoopQ" loop
{
X "X",
Y "Y",
Z "Z"
} fields -
(
Q1 "This is a question in the loop"
categorical [1..1]
{
A "A" exclusive,
B "B" exclusive,
C "C" exclusive,
D "D" exclusive,
E "E" exclusive
};

Q2 "This is another question in the loop" long;

)expand;
End Metadata

OutputDataSource(myOutputDataSource)
ConnectionString = "Provider=mrOleDB.Provider.2;_
Data Source=mrRdbDsc2;_
Initial Catalog=C:\Program Files\IBM\SPSS\DataCollection\7\Interviewer Server\FMRoot\Master\ProjectY\ProjectY.mdd";_
Location='Provider=MSOLEDBSQL.1;_
Integrated Security=SSPI;_
Persist Security Info=False;_
Initial Catalog=ProjectY;_
Data Source=LocalHost';_
MR Init Project=ProjectY"
End OutputDataSource

The internal changes in the MDM in UNICOM Intelligence Data Model 2.7 mean that the MDM now creates the variables (VariableInstance objects) that relate to the same question or loop in a different order to previous versions of the UNICOM Intelligence Data Model.

This can cause data to be written to the wrong columns if you do not use an .mdd file when you transfer data from a relational MR database that was created with an earlier version of the UNICOM Intelligence Data Model. However, this problem can be avoided by using the .mdd file in the input data source connection.

This problem is not specific to the RDB DSC 2 and is actually caused by a problem in the MDM. The problem occurs because unfortunately the MDM does not give you an error if you create a new variable using the name of a variable that existed in an earlier version but which was of a different data type.

For example, suppose that in version 1, you create a categorical variable called Pets and then activate the project. This involves the UNICOM Intelligence Data Model synchronizing the case data with the metadata and creating a Pets column of type categorical in the case data. You then delete the Pets variable in version 2 and reactivate the project. Although you have deleted the variable from the current version of the metadata, the Pets column is still in the database. If in the next version you add a new variable called Pets, but make it a text variable, you can activate the project without getting an error. However, you can not write the text case data to the Pets column, because it is still of type categorical.

This problem would not have arisen if the new variable had a different name (such as NumberOfPets) or if it had the same data type as before. In addition this problem would not have arisen if you had not deleted the Pets variable in version 2, because an error would have occured when the project was activated.

Therefore, make sure that after the case data has been synchronized with the metadata (which happens during activation), you do not create a new variable using the name of a variable that was in an earlier version but which was of a different data type.

When you use RDB DSC 2 to connect to an RDB database, SQL UPDATE statements might fail, particularly when inserting large amounts of data. A similar problem might also occur when an SQL DELETE statement is used to delete data that is stored in the OtherData table in a database that uses the older RDB DSC schema. Typical error messages when the failure occurs during an UPDATE statement are:

This problem occurs when you are performing queries on the HDATA hierarchical tables and a sub-select statement is used to retrieve data from a lower level but only the LevelId column is specified. For example, if you run the following query on the Short Drinks sample, you will find that no rows are returned for the frequenz grid:

This occurs because RDB DSC 2 filters Null rows at lower levels and considers rows to be Null if all of the columns other than the LevelId are Null. Therefore when only the LevelId column is bound, RDB DSC 2 considers all of the child rows to be Null. However, if you bind another lower-level column, the rows are returned. For example, the following query returns the rows for the frequenz grid:

This is usually caused by a deadlock on the UNICOM Intelligence Interviewer - Server Admin database, a problem that is silently ignored by the . In UNICOM Intelligence 4.0 and later, the Export Data activity connects to the UNICOM Intelligence Interviewer data using RDB DSC 2's Favor Memory option, which reduces the likelihood of deadlocks.

To resolve this problem in UNICOM Intelligence 3.5 and earlier, write a data management script (DMS) file to export the UNICOM Intelligence Interviewer data, and include the Favor Memory option in the connection string for the input data source. For more information about the Favor Memory option, see Connecting to a relational MR database using RDB DSC 2. For more information about writing a DMS script, see Transferring data from a relational MR database (RDB).