Background


A dataset brings together one or more versions of a template from Synoptec or one or more data tables that contain common outcomes. This allows a user to create a report in the analyzer module. 


Terminology


To allow us to describe datasets it would be helpful if we used the same words. Here is an explanation of the terminology we will be using;


Terminology Description

Dataset

A group of questions from one or more reporting or datamart tables.


Mapped dataset

A mapped dataset is used when one or more data sources will be combined to form one dataset. This type 

of dataset allows you to select specific columns form a table to be used in the dataset. Transformation 

can be performed on these columns to ensure all data sources return the same data format.

Static dataset

A static dataset is used when a table or view has already been defined in the database to be used as a dataset.

This is commonly used when a dataset has more than 50 datasources. Using the dataset for anything more than

50 datasources may result in a timeout as there are too many records to process. Setup of tables and views will 

require assistance from the help desk.

Base report

The report used to create the dataset.

Mapped outcome The outcomes (questions) specified in a dataset.
Data source The table(s) used in the dataset.
Crosswalk A way of completing a data transformation on an outcome so that a common coding can be defined. For example
if a column stored 'F' for female but another data source stored 'Female', you can create a crosswalk that will map
'F' to Female and 'M' to Male so that the dataset will report 'Female' from all table (where appropriate).


Steps to Make a Dataset


1. Determine the Synoptec template that contains the majority of your template questions from. Sometime you would like to join 2 or more templates together and there is one template that contains most of your questions. This template will be your base report and should be the template that you use to start your dataset. If it is the same number of questions from all templates then use the latest version of a template.


2. Determine the question’s that you want to include in your dataset. Unless your template is very short it is rare that you would include every column from a template. Copy the columns id’s to another document so that you can easily reference them. It is easiest to have the majority of you columns determined prior to creating a new dataset, so specifying this beforehand will make the process faster and easier.


3. To create a new dataset start in the Analyzer module under the Datasets menu option:



4. Choose what type of dataset you would like to create. See an the explanation for a Mapped dataset and a Static dataset in the terminology section above. For this example we are going to choose a mapped dataset.


5. Enter in the name of the dataset and the table name. The table name will be the table name in the database. Select the base report, this is the Synoptic template you decided to start your dataset with in step 1. 


6. Once you choose your base report you will be provided with the list of available columns from that template. Select one or more columns. Columns can be added on later but it is easier to add columns at this step. If you are unsure of a column name but know part of the column name try to search for the column by entering the partial name in the provided search box:



You can also view data in this reporting table by choosing the Preview button. This will take you to a new tab where can you choose specific columns to see the type of data that is in the column



To select columns for the dataset use the check box next to each column name. If the full column name is not displayed then hover over the column name to see the tooltip which will show you the full name. 




7. Add on a secondary data source, if there is one. This can be done by choosing the plus sign in the data source area. 



If the same column id's can be found in the new datasource the application will try to automatically map the columns. You can change or modify this as needed:




8. Go through each mapped outcome and apply modifications as needed. RENAME column names. The column name is assigned by default and comes from the reporting table, it can be more technical in nature. It may be difficult for someone to understand that is trying to do reporting. 






9. Preview output as this will show you if there are any errors in your dataset.




10. Generate the dataset. This creates your dataset table in a schema called dm (datamart) and inserts data from all data sources :




11. Go back and add on any other datasource, completes steps 7-10 again.