Workday Adaptive – Successfully use Table Group Data Source in Design Integrations to calculate Week numbers

Reporting and data analysis on week numbers is key for various business segments. Calculating week number from your date fields should be a simple process during your integration. With Table Group Data Source this can be easily accomplished when bringing your source data into Adaptive.

A table group is a data source that combines tables from many disparate sources. You can compare it to creating vlookup formulas in excel, where you match values of a column from two different tables, and then you are able to reference the values of other columns based on the matching column.  For example, you might have a spreadsheet with sales data, a couple of JDBC databases that have personnel or inventory information, manufacturing information from another division in NetSuite, and then want to use scripting on a table to do complex filtering and calcula­tions, with all these tables linked by SQL joins and additional filtering.

Use Case for using Table Group Data Source

Adaptive users want to calculate week numbers on the data source and bring it as a dimension in their loaders so data analysis/ reporting can be done based on week numbers (not utilizing “Time” to have week numbers).

Functionality Gap

  1. Configuring time to include week number is cumbersome in Adaptive as the same week number can fall in two different months. Adaptive does not allow the same week number to fall in two different months.
  2. Utilizing SQL Week functions in the Adaptive “Custom SQL Column” option is not available

Solution Description

There are three steps to this solution:

Step 1 : Create a Spreadsheet Data Source that contains each day of the year and the week number it represents (. Make sure that the column with day of the year matches the same format as the date field you are trying to extract the week number for). Other columns can be used to calculate other wanted values. One suggestion is to create this file to contain several years of data, so you don’t have to update it every year.

Adaptive- Design Integration-Create Spreadsheet data source to hold week number information

 

Step 2: Create a Table Group Data Source.

Adaptive - Design Integrations - Create Table Group

 

Step 3 : Configure the newly created Table Group to join the desired data sources previously created. During this step you can create as many “join tables” as needed, and link as many tables (from different data sources) as necessary. The key here is to make sure you have all your data sources created with the columns that you will use to join them.

Adaptive - Design Integration -Table Group Configuration

Make sure that in the Join Expressions, you are joining the correct columns in both sources. This is key in making sure you will be able to derive your week numbers from the date field.

Adaptive - Design Integration - Join Table settings

 

Once that configuration is finalized, make sure that all the fields that you need are available in your join table. Make sure you select the derived week number field at this point.

Manage Table Group Columns

 

Manage Columns

Once all fields are available in the new table, you can utilize the loader and reference the week number fields to match a dimension in your models in Adaptive. Once the data is in Adaptive you can utilize the normal HTML reports, dashboards and Office Connect reports to reference the week number dimension and be able to slice and dice the data according to your needs.

 

If you have questions or need assistance implementing Workday Adaptive Planning please feel to contact us!

About Abaci
Abaci is a premier NetSuite & Workday Adaptive Planning Solution Provider with deep knowledge in both business process and technology architecture. Our team is passionate about NetSuite & Adaptive and has an exclusive commitment to providing the highest level of system expertise on its full suite of cloud-based business management applications.