Localiza el Origen de tus Datos
It’s project kickoff time and team members are ready to get started. Stakeholders are excited about the new implementation that will reduce time-consuming spreadsheet reporting and create new analytics and visualizations that will provide better insight into the productivity of the business.
Many times, especially when working with a new ERP or another application where you are not yet familiar with the environment, locating the correct data can be challenging. With a handful of metrics, hundreds or thousands of tables and views to sift through, and no particular roadmap for guidance, the process can be daunting.
There are times when the implementation is garnered with data dictionaries and multiple subject matter experts and technical resources. However, when a new ERP implementation coincides with a ZAP BI implementation, data location can be more challenging at the beginning of the project.
On the bright side, this is an opportune time for the customer to become more familiar with the new ERP source data. Since ZAP brings experience working with the backend tables and views, this helps to bring exposure to the customer.
There are several approaches to finding the components necessary to build out ZAP requirements.
- Work with an ERP administrator to provide a look into the ERP front-end as to which components are required. Typically, information from AX forms can lead to other tables, views or methods housing the data.
- Create a spreadsheet with each data component, and capture the table/view and columns when they are located. This is very useful in keeping the consultant and customer on the same page when searching for the data.
For some difficult to find data using SQL Server, perhaps the quickest way is to search for it through the sys.columns views found in the Master database.
Below is a script for finding table and column names. Simply enter a partial or full string into the variable to search for columns containing the column name you are looking for. While this is not definitive, it can help narrow down your search window. Also, by entering a partial name in the table variable, such as “proj”, we can limit the number of results to tables that relate to projects.
| USE MicrosoftDynamicsAX |
| Declare @cName nvarchar(50) = ‘%cost%’ –enter column search |
| Declare @tName nvarchar(50) = ‘%proj%’ –enter table search |
| SELECT c.name as ColumnName,s.name+’.’+o.name AS ObjectName,o.type_desc AS ObjectType , t.name DataType, c.max_length |
| FROM sys.columns c |
| INNER JOIN sys.types t ON c.system_type_id=t.user_type_id and t.is_user_defined=0 |
| INNER JOIN sys.objects o ON c.object_id=o.object_id |
| INNER JOIN sys.schemas s ON o.schema_id=s.schema_id |
| WHERE c.name LIKE @cName and o.name LIKE @tName |
| ORDER BY DataType, ColumnName |
While you could use the INFORMATION_SCHEMA.COLUMNS for the column data, the INFORMATION_SCHEMA.VIEW_COLUMN_USAGE to obtain the View data does not include Data Type. Having the Data Type included for both Views and Tables is very useful in determining what objects may contain your missing data.
In the below screen capture, you will see a search against the Contoso Dynamics AX database for columns containing “ctc” in tables with the name containing “Proj”, thereby narrowing the results to tables likely related to Projects:
In this example, we are looking for columns that contain “CTC”, referencing “Costs to Complete” for tables related to projects.
A variation of this query can also be useful for grouping all of the Date columns in a particular group of tables. In the case below, we are looking for all Date columns that relate to Project tables:
This can be especially useful when sorting through date columns to look for when determining which dates are utilized and in creating date relationships.
To take dates a step further, we can determine which date columns are actually being utilized by doing a count of distinct records in the individual tables where transactions are being posted.
Date Contents:
| USE MicrosoftDynamicsAX | |||
| DECLARE @Table SYSNAME = ‘projtable’; | |||
| DECLARE @SQL NVARCHAR(MAX) = ‘ ‘ | |||
| SELECT @SQL = STUFF(( | |||
| SELECT ‘ UNION SELECT ‘ ‘ ‘ + c.name | |||
| + ‘ ‘ ‘ AS [DateColumn], ‘ | |||
| + ‘ COUNT(DISTINCT ‘ +QUOTENAME(c.Name) +’ ) AS [DistinctCount] FROM ‘ | |||
| + QUOTENAME(@Table) | |||
| FROM sys.columns c | |||
| join sys.types t on c.user_type_id = t.user_type_id | |||
| WHERE object_id = Object_id(@Table) and t.name like’%date%’ | |||
| –concatenate result strings with FOR XML PATH | |||
| FOR XML PATH ( ‘ ‘ ) ), 1, 7, ‘ ‘ ); | |||
| –Print @SQL | |||
| EXECUTE sp_executesql @SQL | |||
We can see that several of the dates in the Project table above are not currently being utilized as there is only 1 distinct date.
Locating the correct data to support business requirements is a critical and important early step in creating the foundation of any application. There are ways to streamline the effort, and with perseverance and a bit of assistance, the time allotted can be greatly reduced.
By Kevin Jobe
