TX Dictionary Project

Gain valuable insights into your ‘Project Repository’ with TX Dictionary Project

The purpose of the TXDictionary project is to provide visibility into your project repository Meta Data across one or many projects.  The TXDictionary project was designed to assist developers, data architects and Business users to gain insights into their Discovery Hub projects.

It includes a Discovery Hub project designed to take advantage of the multi-layer platform, connection to a project repository, data consolidation, transformation and cleansing in the Data Staging Area, and a robust reportable data warehouse in the MDW.  The TXDictionary project is a great example of leveraging the power Discovery Hub® to simplify a very complex relational data model into a Dimensional Data Warehouse. It also includes a Qlik Model, Qlik application and several Qlik dashboards which provide easy visibility into all your projects in a given repository.

Once configured, the project will create a data warehouse containing up to date information about your Discovery Hub projects. For example, the Projects table contains a list of all current projects in your repository. The Databases table show all databases, with relevant details and so on. The factObjectCount table is a "Fact" table (or link table) that is relates all tables (Projects, Databases, Fields etc.) together enabling you to query and report on this useful data.  

Descriptions of the tables and fields within the project can be found in the attached file: TX Dictionary Table-Field Descriptions.xls

Download and Initializing the TX Dictionary Project

Download the TX Dictionary Project from the Cube Store

Once imported Select ‘Yes’ to run the Connection Manager

Create an ODX database

Connect to an Existing Project Repository and ‘Test Connection’

Create an MDW database

Create a DSA database

Once complete ‘Test Connections’ and validate the Connection States are all Green

Deploy and Execute the Project

Dashboards included with TX Dictionary

Setting Up the Excel Pivot Table Report

Ensure you have deployed and executed the OLAP cube included in the TX Dictionary Project. If the Cubes are guarded, right-click the cube and under advanced click Gaurd. 

Download and open the attached "Data Dictionary Pivot Table Report.xlsx" 

In the Analyze tab click Change data source. 

Point Excel to the OLAP server created by the TX Dictionary Project. If you are unsure what this is called, open the project and edit the OLAP server to see the proper name. It should be called "TXDictionary_OLAP"

Once this is done, in the Analyzed tab, click Refresh All. You may need to remove and re-add the pivot table "rows" to get the dashboard to update properly. 

Setting Up the Qlik Dashboards

Copy ‘TX Dictionary.qvf’ file into your Qlik Apps folder

Create QlikSense App from Discovery Hub

This should create the connection script required to connect QlikSense or QlikView to the Discovery Hub Qlik views

QlikSense

If this does not create the ‘MDW’ connection in QlikSense automatically then you need to manually create a ‘MDW’ connection in QlikSense that points to the correct MDW database in Discovery Hub

QlikView

Deploy QlikView script to a text file, make sure you choose QlikView syntax.

Use the include statement in Qlikview to point to the Qlik script file

Load data in Qlik to ensure connectivity

The Qlik dashboards provide easy access and reporting on one or many projects within your repository.  There are 4 Dashboards included as part of the ‘TX Dictionary’ project ‘TX Dictionary’, ‘Execution Time’, ‘Failed Executions’, and ‘Data Quality’.

The Included Qlik Sense Dashboards

TX Dictionary Dashboard

The TX Dictionary dashboard allows users to browse one or many projects to understand project data sources, databases, table and query information, all data fields including data types, transformations, the source or sources of each field and the population methods.  You may filter this information by project, database type, database, container, or by field.  This allows users to really zoom into vital project information at any desired level.

The following is a snapshot of the Dashboard:

Execution Time Dashboard

The Execution Time dashboard provides valuable insight into Execution package information within 1 or many projects.  Monitor execution packages, schedules, execution steps and average execution time by table.  Filter executions by project, scheduled package, and step type.

The following is a snapshot of the Execution Dashboard:

Failed Executions Dashboard

The failed executions dashboard is a companion of the execution dashboard with emphasis on understanding the status of each execution.  Users can view all execution packages post execution along with start and end time information, execution steps, status, total execution time and Execution message log information.  Filter executions by project, scheduled package, execution package and execution status.

The following is a snapshot of the Failed Executions Dashboard:

Data Quality Dashboard

The data quality dashboard gives users a graphical bar chart depicting the amount of good, warning and error information for each table within a project(s).  Filters can be applied by project, data quality status and by table.

The following is a snapshot of the Data Quality Dashboard: