Visualize your TimeXtender metadata with TX Dictionary
The TX Dictionary project allows developers and business users alike to benefit from the valuable insight provided by TimeXtender's metadata. Once deployed & executed, this project extracts the raw data from TimeXtender's project repository and transforms it into a denormalized data warehouse format ready for reporting and analysis.
With this project you can:
- Provide an always up-to-date Data Dictionary to your business users in the reporting tool of your choice including custom object definitions & data lineage.
- Analyze execution packages in detail to improve performance & pinpoint problem areas.
- See detailed execution messages over time.
- Report on validation errors and warnings counts over time.
Below is an example dashboard analyzing execution performance.
To learn more about the tables and fields available in the TX Dictionary project you can download the 'TX Dictionary Table & Field Descriptions' excel file at the bottom of this article. It contains descriptions of all the tables and fields in the presentation layer as well as a simple data model.
Download and Configure the TX Dictionary Project
Download the TX Dictionary Project from the Cube Store
- In the File Menu, click CubeStore.
- Click TXDictionary in the list
- Click Request Trial
- Click Download
Once imported Select ‘Yes’ to run the Connection Manager
In the Connection manager, Click Run Wizard. The wizard will open the "edit" dialogue for each of the database connections in the project.
- Create an ODX database
- Connect to an existing MDW Database (Optional)
- Connect to an existing Project Repository and ‘Test Connection’
- Create an MDW database
- Create a DSA database
- Create an OLAP Server (Optional)
- *If you do not have an OLAP server, or plan to use an SSAS Multidimensional OLAP database for analysis, you should delete the OLAP server in the project later.
Once complete, click ‘Test Connections’ and validate the Connection States are all green.
Finally, be sure to Deploy and Execute the Project.
Dashboards included with TX Dictionary
Excel Pivot Table Report
The Excel Pivot Table Report works with the included SSAS Multidimensional OLAP Cube as a data source. 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.
The Qlik App provides easy access and reporting on your repository. There are 4 Dashboards included as part of the TX Dictionary, Execution Time, Failed Executions, and Data Quality. This App connects to the "TXDictionary" Semantic Model, with an added Qlik Endpoint.
Qlik App Installation Instructions:
- In the TX Dictionary TimeXtender project, Add a Qlik Endpoint to the TXDictionary Semantic Model & Deploy.
- Right Click on the Endpoint>Qlik Sens Scripts.
- Copy the Qlik Script from the Endpoint.
- Download the ‘TX Dictionary.qvf’ file at the end of this article and place into your Qlik Apps folder.
- Open the app in Qlik and navigate to the 'Data load editor'.
- In the section called TX DWA (auto-generated) delete any existing script.
- Paste in the script copied from the Qlik Endpoint & Load Data.
The Tableau Workbook provides 4 dashboards including Data Dictionary, Execution Time, Execution Status, and Data Health.
Tableau Workbook Installation Instructions:
- In the TX Dictionary TimeXtender project, Add a Tableau Endpoint to the FieldData and Executions Semantic Models & Deploy. Remember the file path where the TDS files are stored.
- Download The 'TXDictionary.twb' file at the end of this article and open with Tableau.
- Under the 'Data' menu, and under 'FieldData' click 'Replace Data Source...'
- Select the FieldData TDS file created earlier.
- Now repeat the last two steps for the 'Executions' Data Source.
The PowerBI report contains 4 sheets including a data dictionary, executions, failed executions, and data quality. This report operates from an SSAS Tabular model. *An SSAS Tabular instance is required.
- In the TX Dictionary TimeXtender project, Add a Tabular Endpoint to the TX Dictionary Semantic Model and Deploy & Execute. Remember the server and database name of your tabular model.
- Download the 'TXDictionary.pbix' file at the end of this article and open with PowerBI.
- Open the pbix file and you will receive an error message like this.
- Click Edit.
- Put in the server\instance and database name of the TX Dictionary tabular model you deployed in step 1.
- Now click refresh and the report should load with the TX Dictionary data.
Do you perhaps have a similar dashboard in the works for Power BI?
Hi Andri, We are working on PowerBI and Tableau dashboards for this as well.
We brought this project from cube store and the cube supports project data dictionary on a spreadsheet. Not sure why its not picking up any changes to our data model,. We did refresh the cube on database, Is there anything else that needs to happen?
I'm missing the Qlik Models in the Data Lineage. Besides that, I'm also waiting for a Power BI version of the TX Dictionary Dashboard.
There is a new version of the TX Dictionary project available in the cube store as well as Power BI and Tableau dashboards added to this article. Let me know how they work for you!
The PowerBI dashboard attached does not seem to work with the current state of the TX Dictionary project. I get the following error:
Connecting to the endpoint from a fresh PowerBI report does seem to work.
We have been using the TXDictionary Tableau workbook and are wondering to what extent this project is maintained as part of the TimeXtender release cycle. Will it be updated when the data model changes?
Also, is there a reason why schema is not included in Container/Field or Data Lineage? Schemaname in Container quite often incorrectly shows dbo.
As we are looking to further expand use of the TXdictionary it would be great to see this corrected.
Hi Rory, I encourage you to work with your SSP Alex Wijnen on this.
Hi Albert, we maintain this project as a convenience to our customers. We make no guarantees on the future of this project. Which leaves you with 2 options.
As for the schema issue. I will look into resolving this in a future release. Thanks for the heads up.
when used with a SSAS Tabular model, the processing of the model can fail when the PackageExecutionTime.ExecutionMessage exceeds 131072 characters (limit for strings in Tabular models)
You are correct, in tabular models the string limit is 131072.
An easy workaround for this is to add a custom transformation on PackageExecutionTime.ExecutionMessage in the MDW. The custom transformation should use the Left() function to return the first 131072 characters. Example below:
Hi, I am trying to configure the TX dictionary project, but when I do a deploy+execute I get the following error on the DSA:
Could not find server '[myserveradress]' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
I ran the connection manager and all the connections seem valid. What does the error mean and how do I solve it?
Edit: Found what the issue was: the project has Direct Reads enabled on the Datawarehouse settings of the DSA. Disable them and it works.
If you want to use only one DSA/MDW database, which can be sufficient for this purpose, make sure you set either the default database schema for the dsa or the mdw other then etl (so etl is only used once). Otherwhise you get conflicting issues (culumns) when running the project.
Can you provide us with information on how to add the mapped custom view fields to the lineage table ?
We've setup views in our project - using parameters and 'map custom view fields', but they do not appear in the reports.
We're also missing the mappings of Custom Table Inserts, but that's a common issue (also not available in Data Lineage in TX itself).
Is there some equivelant project for an Azure DB / Synapse environment?
When we try to deploy this solution within a Sql pool, we stumble into a lot of errors regarding things that are not available for Azure DB.
Is this work with Power BI now?