smarter Dimensions

How to use DMQuery

Monday, December 1st, 2008 | Tools

 There are a few tools that you should always be using to help you with your Dimensions work this article is about the tool “DMQuery”. We can use DMQuery to look at data and see what information particular respondents have recorded, but it should also be noted that we can use DMQuery to edit data , get counts and even market research style table results.

The DMQuery tool comes with the Dimensions development library and if you have this installed you can find it in the following directory.

C:\Program Files\SPSS Dimensions\DDL\Code\Tools\VB.NET\DM Query

It is probably better to put an icon on your desktop of DMQuery because as you become familiar with the datamodel the more you will use this tool.  At this stage I would also like to add that there are two other tools that you should look at , and they are

How to use MDMExplorer
How to use Windmsrun

So once we have an icon on our desktop, double click it and you should see a screen like this.

Connection Properties

 

The first thing we need to do is to connect to our datasource, in this example we are going to connect to the museum dataset that comes with the DDL and can be found in this directory,

C:\Program Files\SPSS Dimensions\DDL\Data\Dimensions File

If you have not seen the Data Link Properties wizard before then read this article to get yourself familiar with it. If you do know what to do then set the metadata location to the museum.mdd file and then specify the Dimensions Data File as the case data type and then click ok. If we have connected correctly we should see this screen,

Connection OK

 

The next thing we need to do is to provide DMQuery with a SQL select statement.

Simple Select statements: The basic statement we use is “Select * from vdata” and if we use this, we will see the following,

Show Data

What we have typed in is a SQL statement that has requested to get all the variables from the “VDATA” table. The VDATA table is a virtual representation of your survey results and as you will see above there is one respondents results on one row. We know this because the first column is the Respondent.serial and this is the unique number that was given to the data when it was collected. If we wanted to limit the number of fields we see in DMQuery we need to specify them in the select statement. Type this in “Select respondent.serial, age , gender from vdata” and look at the result.

three columns of data

What we will see that we now only have three columns to look at and we will see just the unique serial number assigned to the respondents results, that number that represent the age and the number that represents the gender. Don’t forget that if you modify the connection string and get the DSC to return category names you will see something that may be a little more helpful to you.

Show UIDS

 

We can take this statement one stage further by using the format command. Type this in “Select respondent.serial, age.format(‘b’) , gender,gender.format(‘b’) as GenderText from vdata” and look at the results.

format

Where Clauses: We have briefly looked at some of the statements we can use to limit the number of fields ,so next we might want to see how we can limit the number of rows. We can do this by adding a where clause to our statement, so type this in “select respondent.serial, age.format(‘b’) , gender,gender.format(‘b’) as GenderText from vdata where respondent.serial < 9″ and look at the results.

format command

Where clause

where clause categorical

We can see that the list of records is now in alphabetical “Ascending” order, this is the default order but if you wanted to see them in descending order then you can just add the keyword DESC on to the end of the statement, for example “select respondent.serial, age , gender from vdata where gender = {male} Order By respondent.serial desc”

descending order

 

Grouping: as well as ordering we can group things together, we normally do this when we want to count information in particular columns or get a unique list of the column results. Type this in “Select age from vdata group by age” and look at the results.

grouping

group by totals

Table Statements: We can take the above statement even further and produce a table style set of records.Type in the following “SELECT desc_leave AS Description, SUM(gender = {male}) AS Male, SUM(gender = {female}) AS Female FROM vdata WHERE desc_leave IS NOT NULL GROUP BY desc_leave” and look at the results.

tables

Updates to data: We have briefly looked at how we can look at the data in DMQuery, so the next thing we may want to do is to look at how we can modify it. Firstly before we do anything I suggest that we take a copy of the datafile so that don’t loose the original data. It is probably safer to close down dmquery , take a copy of the files and the loadup DMQuery again. Once we have a copy of the files and dmquery is connected again type in the following statement “select respondent.serial,gender from vdata where respondent.serial = 4″ and look at the results, please note if the dsc you are using does not allow you to write to the data then you will get errors rather than an updated datafile.

Updates

 

 

We will see that we get one record and it is the answer to the gender question for respondent 4. Lets assume we know respondent 4 to me female and have been asked to update this record. I should stress that you should only do this if you are confident in doing so and that you have a copy of the data , if you make a mistake it will be possible for you to destroy the integrity of the data. Only use DMQuery as an editing tool if you don’t have mrStudio or you need to make one or two small changes. Lots of changes should be done in mrStudio.

So back to the code, to update the record type in the following “update vdata set gender = {female} where respondent.serial = 4″and then look at the results

update with where

we will see that nothing is displayed, this means it has worked to see that the changes have been made type the following “select respondent.serial,gender from vdata where respondent.serial = 4″ and look at the results again.

select with where

 

 

You will now see that respondent 4 is a female. When you are doing this sort of thing, it is good practice to copy your select statements to note pad or something so that you can keep coming back to them, and it also gives you a history on what you have been doing so if something goes wrong you can re-trace your steps. Doing this has saved me a few times and its very useful when you have to try and reproduce things.

using notepad

 

 

Deleting Data: If for some reason we wanted to delete respondents 4 record, then we can use the following statement “delete from vdata where respondent.serial = 4” If we run this statement we will see the following.

deleteing

 

 

To see if the record has been deleted , run the following “select respondent.serial,gender from vdata where respondent.serial = 4” and you will see it is no longer.

Inserting rows: If we wanted to add respondent 4 back into the data then we could with the following statement “insert into vdata (respondent.serial) Values (4)“, if we take a look at the data after the insert we see the following

inserting rows

 

 

We can see that the gender and age question is blank, so what we should have done is to make sure we know what all the required values where and then insert them also, to do this we could have done a “insert into vdata (respondent.serial,gender,age) Values (4,{23},{4})” or we could have used an update statement “update vdata set gender = {23},age={4} where respondent.serial = 4”

As mentioned before, we need to be very careful when we do this sort of updating, because if we get something wrong we could mess up the data, to explain , type the following “select respondent.serial,gender ,age from vdata where respondent.serial = 4” and make sure we have the following

show the data before the edit

Now run the following statement “update vdata set gender = {4},age={23} where respondent.serial = 4” and then look at the data after

mulitple update

 

You will now see that we have the results in the wrong columns. So once again … be careful …

That’s it for now, but if you would like more information about anything you see in this article then please sign in and add a comment and we will see what we can do.

4 Comments to How to use DMQuery

jhendricks
2 December, 2008

Another thing to mention here is the ability to use DMQuery to look at BOTH vdata and hdata. While VDATA provides a flat view of the data in a tabular format that is useful for general-purpose reporting, HDATA provides access to heirarchical data and you can look at, for example, counts of a household rather than household members or whatever kind of hierarchies you have defined. HDATA is generally applicable to data captured using mrInterview or other Dimensions data capture products, though tools and utilities exist that can help you add hierarchies to non-Dimensions data as well.

Allison
2 December, 2008

DMQuery is also great for looking at logs if you are debugging. You can SELECT based on loglevel or use the Find function in your SELECT statement to get entries with certain words in the log message.

Leave a comment