Objects and Windows
  
Objects and Windows
QAD Data Warehouse Designer makes use of an object concept when dealing with the different components that make up a data warehouse solution. The main object types are: Connections, Load Tables, Dimensions, Stage Tables, Fact Tables, OLAP Cubes, Aggregates, Procedures, Host Scripts, Indexes, Retros and Exports.
This chapter explains and provides an overview of each of these object types and how they can be managed and organized. The full functionality of each object is covered in the following chapters.
The various Windows, Panes and Views that form the QAD Data Warehouse Designer tool are also explained.
 
 
Object Types
QAD Data Warehouse Designer has a concept of objects which are combined to create real world data warehouses and data marts, fast. Each QAD Data Warehouse Designer object has properties that allow the data warehouse developer to change how the object is used.
Note: Some object types may not be available for certain types of QAD Data Warehouse Designer licenses.
QAD Data Warehouse Designer objects include:
Object Type
Purpose
Connection
Connections define the path to external objects such as source data. Examples of connection object types are databases, analysis services cubes, operating systems or ODBC sources. Connections isolate environments simplifying, for example, the promotion of code between development and production.
Load Table
Load tables are the first entry point of data into the data repository, and typically hold the latest set of change data. These objects contain their definition. Load tables can be defined as database link, odbc, external, file, script or XML. Based on their definition they will, for example, run a predefined script or create a load script at run time. Pre load actions (e.g. truncate) or post load procedures can be defined as part of a load object. In addition, transformations (either during or after the load) can be defined against columns in a load table.
Dimension
Dimension tables are the constraining elements in the star schema design, and are defined by this object type. QAD Data Warehouse Designer will automatically generate procedural code for the three standard types of slowly changing dimensions, as well as date ranged dimensions (where the current version is defined by an external system). QAD Data Warehouse Designer also ships with a standard time dimension which can of course be extended. Dimensions can also be defined as mapping or work tables which do not appear in the generated user documentation.
Dimension View
A dimension view is a database view of a dimension table. It may be a full or partial view. A common usage is to create views where multiple date dimensions exist for one fact table. Other types of views supported by QAD Data Warehouse Designer include fact views, other table views, work views and user defined views.
Stage Table
Stage tables are used in the transformation of raw data into model or star schema format. They typically hold only the latest set of change data. As well as custom procedures, QAD Data Warehouse Designer can generate different types of procedural code based on the complexity and size of the dataset and performance requirements. Examples of procedural types that can be generated are cursor, sorted cursor, set, set + cursor or set merge procedural code. A stage table can also be defined as a work table, which has the same properties as a stage table but does not appear in the generated user documentation.
Normalized Table
A normalized table is a data warehouse object used to build third normal form enterprise data warehouses. In QAD Data Warehouse Designer, normalized objects have many of the code generating attributes of stage, dimension and fact tables. Third normal form enterprise data warehouses can be thought of as a source system for star schema data marts. Alternatively, they may be reported off directly by users and reporting tools.
Data Store Table
A Data Store Table is a data warehouse object used to store any type of data for later processing. In QAD Data Warehouse Designer, Data Store objects have many of the code generating attributes of stage,dimension and fact tables. Data objects can be thought of as a source system for the data warehouse. Alternatively they may be reported off directly by users and reporting tools. Data Store Objects can be considered either reference or transactional in nature.
Fact Table
Fact tables are the central table in a star schema design. This object type allows the definition of fact tables. They support transactional, rollup, snapshot or partitioned (detail, rollup or exchange) fact tables. Changing a fact table's properties to partitioned will start a partitioning wizard that prompts for the required information.
Kpi Fact Table
This object type supports a special type of fact table. A mandatory KPI (Key Performance Indicator) dimension provides a set of KPIs which are stored and maintained by this object type.
Aggregate
The aggregate object type provides a means to speed up access by summarizing data to a higher grain. For dimensional models a rollup of the fact data will allow removal of dimensions that are no longer valid.
OLAP Cube
The OLAP Cube object type uses Analysis Services cubes to deliver OLAP functionality in QAD Data Warehouse Designer. A cube is a set of related measures and dimensions that is used to analyze data from a variety of different front end tools. OLAP Cubes are built from fact objects and aggregate objects in QAD Data Warehouse Designer.
OLAP Dimension
An OLAP Dimension is built by QAD Data Warehouse Designer for every dimension table associated with the fact (or aggregate) table the OLAP Cube is derived from. OLAP Dimensions are shared across one or more OLAP Cubes. In analysis services, a dimension is a group of attributes that represent an area of interest related to the measures in the cube and which are used to analyze the measures in the cube.
Procedure
The procedure object type is used to define and hold database stored procedures. As such it may contain functions, procedures and packages that are generated, modified or custom developed.
Host script
Host script objects are either Windows or UNIX scripts. These scripts are maintained within the QAD Data Warehouse Designer environment and can be scheduled to run in their host environments.
Index
This object type defines database indexes used to improve the access times on any of the table object types (i.e. Load, Stage, Dimension, Fact, Kpi Fact and Aggregate.
Export
Exports are used to manage exports from the data repository. In essence, exports are the reverse of load tables, taking data from a table to a flat file.
Retro
Retros are used to load predefined data models from modeling tools and to retrofit existing tables into the QAD Data Warehouse Designer metadata.
Retro Copy
Retro Copies are used to retrofit existing data warehouses into the QAD Data Warehouse Designer metadata and to transfer data from the existing data warehouse to the new data warehouse.
Template
Template objects are used in Version 4 of QAD Data Warehouse Designer to provide example code.
Connections are normally the first objects created. These connections are then used in the creation of load tables through the drag and drop functionality. Subsequent objects can also be created through the use of drag and drop.
It should be noted that although the object types have names that correspond with their primary usage, they can be used for other purposes. For example, the fact object type could be used to create persistent stage tables if required.
Some objects are not supported by all databases, and some advanced properties are specific to the different databases.
 
Organizing Objects
As mentioned in the previous section there are many object types in QAD Data Warehouse Designer.
The objects in the metadata repository are displayed in the left pane of the Builder window. They are displayed in a tree structure which can be expanded and closed as required. The tree can be refreshed by using the F5 or Ctrl/R key.