Dash-boarding

As process instances run in a Process Server environment, they can store tracked performance data. This data is stored as rows in a variety of tables in the Performance Data Warehouse database and can be used for subsequent reporting. Through Process Portal or other tools, reports can be generated that will visualize this performance information.

The performance data that is available is composed of KPIs, SLAs and automatically/explicitly recorded data.

Reporting as provided natively by IBPM is only one way to track business performance. An alternative is to utilize the power of the IBM Business Monitor product which seamlessly integrates with IBPM. See: IBM Business Monitor.

Architecture

As Business Processes run, over time we want feedback on how those processes are behaving. This feedback is generically termed a dashboard and usually consists of one or more charts displaying some data associated with the process.

Within IBPM, as processes execute, we can define that one or more variables in a BPD are to be recorded. This means that the historic values of these variables are stored and continue to be available past the end of the lifetime of the process. The way that this is achieved is that process data is written to one or more tables in the Performance Data Warehouse database. The data structure of these tables are publicly exposed and documented by IBM and database applications can be written to query the content of these tables to obtain data that can be used for reporting.

Tracking data

Generically, the data that a BPD exposes for reporting is called tracked data. There are a number of options provided to us to allow data to be tracked. The first of these is called auto-tracking.

Auto-tracking is the ability of IBPM to track performance data automatically without having to define additional data writing points explicitly within the solution. Auto-tracking is defined on a per BPD basis and it is disabled by default. With auto-tracking enabled, the start and end of each activity encountered in the BPD solution is written as a new table row into the Performance Data Warehouse database.

When we define variables in a BPD we can elect to define none, some or all of them as tracked. This means that when a BPD changes the values of these variables, these changes will be saved and later examined in reporting.

The settings for auto-tracking can be found in the Tracking definitions selected from the menu of the BPD editor.

The Autotracking Name attribute value becomes the name of a database view in the Performance Data Warehouse (PDW) database. Because of this, the Autotracking Name should be chosen carefully to ensure that a table view created with this name is permitted as a legal and valid table view name by the database provider.

There is one database table created per BPD and each table name has to be unique.

Variables within the process can be flagged as tracked. Only variables of certain types can be tracked. These include:

|| |Tracking Type|Allowed Data Types| |Auto-tracking|String, Integer, Decimal, Boolean, Date| |Tracking Groups|String, Number, Date|

To enable tracking either check the box in the Performance Tracking section or right click on the variable in the Variables list and select Track this Field. Variables which are tracked are flagged as such in the variables list.

There are some additional limitations on the types of variables that can be tracked. These include:

After having made definitions about the variables to be tracked, these definitions must be "pushed" out to the Performance Data Warehouse. The File menu in IBPM PD contains a menu item called "Update Tracking Definitions". Selecting this will cause IBPM PD to contact the Performance Data Warehouse database to inform it about the new or modified tracking definitions. It is the sending of the tracking definitions to the Performance Data Warehouse that causes it to dynamically create the tables and views it needs for recording the information. This capability will only work if the database userid defined to IBPM has sufficient authority to create tables on the database.

Once the request to push information to the Performance Data Warehouse has been made, a confirmation message box will be shown.

In the event of problems with tracking data, it is a good idea to use a table viewer tool and validate that the expected tables were created and that after some runs, they contain data.

The physical database structure for an auto-tracked view is as follows:

|| |Column name|Description| |NODE_ID|What is a "NODE_ID"?| |STEP|What is a "STEP_ID?"| |TRACKING_GROUP_ID|| |TRACKING_POINT_ID|| |TASK_ID|| |FUNCTIONAL_TASK_ID|| |TIME_STAMP|The date/time this record was written.| |SNAPSHOT|The name of the snapshot associated with this auto tracked table.| |ACRONYM|The name of the Process Application acronym associated with this auto tracked table.| |TIME_STAMP_YEARS|Time stamp with only "years" field varying.| |TIME_STAMP_QUARTERS|Time stamp rolled up to years/months(by quarters) changing| |TIME_STAMP_MONTHS|Time stamp with years/months changing| |TIME_STAMP_WEEKS|Time stamp with years/months/days changing with time grouped to weeks| |TIME_STAMP_DAYS|Time stamp with years/months/days changing only (hours/minutes/seconds dropped)| |KPICOST|KPI value for cost| |KPIEXECUTIONTIMECLOCK|KPI value for execution time| |KPILABORCOST|KPI value for labor cost| |KPIRESOURCECOST|KPI value for resource cost| |KPIREWORK|KPI value for rework| |KPITOTALTIMECLOCK|KPI value for total time| |KPIVALUEADD|KPI value for value add| |KPIWAITTIMECLOCK|KPI value for wait time| |Process Designer named tracked variables|A column for each variable flagged as "tracked"|

Let us imagine an example usage scenario. We want to see the list of activities that were executed on behalf of a process instance. We know the process instance ID (eg. 1061).

We start by executing this query:

SELECT FUNCTIONAL_TASK_ID FROM TASKS WHERE SYSTEM_TASK_ID = '1061'

This retrieves a value called the FUNCTIONAL_TASK_ID. This will act as a key into the rest of our data. Imagine this returns the value 1724. We can then query the Auto Tracking table that is associated with a BPD with:

SELECT * FROM AT1318439584743 WHERE FUNCTIONAL_TASK_ID=1724

More precisely, we are interested in the TRACKING_POINT_ID value:

SELECT TRACKING_POINT_ID FROM AT1318439584743 WHERE FUNCTIONAL_TASK_ID=1724

We can then use this as a key into the TRACKING_POINTS view to obtain the details of the steps executed.

Since the query from the TASKS table should result in a single FUNCTIONAL_TASK_ID we can combine these queries:

SELECT * FROM AT1318439584743 WHERE FUNCTIONAL_TASK_ID= (SELECT FUNCTIONAL_TASK_ID FROM TASKS WHERE SYSTEM_TASK_ID = '1061') AND NODE_ID IS NOT NULL

(The addition of the NODE_ID IS NOT NULL removes strange duplicates)

We can now get even fancier by building an INNER JOIN to perform our work:

SELECT * FROM AT1318439584743 INNER JOIN TRACKINGPOINTS ON AT1318439584743.TRACKING_POINT_ID = TRACKINGPOINTS.TRACKING_POINT_ID WHERE FUNCTIONAL_TASK_ID= (SELECT FUNCTIONAL_TASK_ID FROM TASKS WHERE SYSTEM_TASK_ID = '1061') AND NODE_ID IS NOT NULL ORDER BY AT1318439584743.TIME_STAMP

Finally we get to what may be the last query which is a cleaned up version of the previous:

SELECT AUTOT.TIME_STAMP, TRACKINGPOINTS.NAME FROM AT1318439584743 AS AUTOT INNER JOIN TRACKINGPOINTS ON AUTOT.TRACKING_POINT_ID = TRACKINGPOINTS.TRACKING_POINT_ID WHERE FUNCTIONAL_TASK_ID= (SELECT FUNCTIONAL_TASK_ID FROM TASKS WHERE SYSTEM_TASK_ID = '1061') AND NODE_ID IS NOT NULL ORDER BY AUTOT.TIME_STAMP

This variant adds an alias on the Auto Tracking table and returns only the time and name of the activity executed:

See also:

Tracking Groups Overview

The idea behind Tracking Groups is to define a set of values that are stored in Performance Data Warehouse tables under explicit control of the process application solution. The Tracking Intermediate Event primitive is used to achieve this goal:

A Tracking Group can be explicitly created in Process Designer under the Performance category.

Once selected for creation, a dialog is shown which allows the name of the Tracking Group to be entered.

The parameters of a Tracking Group definition include a list of field name that are to be considered a row in the tracking data table.

When an Intermediate Tracking Event (see: Intermediate Tracking Event) is created in a BPD, it provides a property page in its implementation where a Tracking Group can be named and the mapping between local data and the Tracking Group field names can be entered:

When a tracking point in a BPD is reached, if the field is checked, then its value is contributed to the new row otherwise there will be a NULL value for that column.

It is also important to realize that just adding a Tracking Point into the BPD does NOT cause that tracking information to be written to PDW. There is a flag in the Process App overview called "Enable Tracking" that must also be switched on.

Database Structure for a Tracking Group

The primary view of interest to us is a database view given the same name as the Tracking Group definition.

|| |Column name|Description| |Columns for each of the tracked fields in the Tracking Group.|There will be a column in the View for each of the named tracked fields in the Tracking Group definition.| |TRACKING_GROUP_ID|This is the primary key for the Tracking Group entry. This links to the TRACKINGGROUPS view| |TRACKING_POINT_ID|This is the primary key for the Tracking Point entry. This links to the TRACKINGPOINTS view.| |TASK_ID|This is the primary key into the TASKS view. Rows with the same TASK_ID were tracking items generated by the same process instance.| |FUNCTIONAL_TASK_ID|| |TIME_STAMP|The Time Stamp of the last update of this record.| |SNAPSHOT|The snapshot of the process app that caused the record to be written.| |ACRONYM|The Acronym of the Process Application| |TIME_STAMP_YEARS|The time stamp rounded to the year. Month and day become 1/1.| |TIME_STAMP_QUARTERS|Time stamp rounded to quarter.| |TIME_STAMP_WEEKS|The Time stamp rounded to the week.| |TIME_STAMP_DAYS|The Time stamp rounded to the day. The hours, minutes, seconds and milliseconds are zeroed.|

Database Structure for TRACKINGGROUPS view

|| |Column Name|Description| |TRACKING_GROUP_ID|The identity of this tracking group| |NAME|The name of this tracking group. This appears to be the PDW view name for records for this tracking group.| |DESCRIPTION|A description of this tracking group. This should also be considered the name of the BPD for autotracking.| |SNAPSHOT|The snapshot name (if any) of the Process Application. Apps running on the Process Center TIP appear to be NULL.| |ACRONYM|The Acronym Name of the Process Application|

Database Structure for TRACKINGPOINTS view

|| |Column Name|Description| |TRACKING_POINT_ID|This is the primary key for this table.| |TRACKING_GROUP_ID|This is a foreign key to TRACKINGGROUPS table.| |NAME|This appears to be the name of the activity that caused the data to be written.| |DESCRIPTION|A description.| |SNAPSHOT|The snapshot (if any) of this process application| |ACRONYM|The acronym of the Process Application that caused the event to be written.|

Database Structure for TASKS view

|| |Column Name|Description| |TASK_ID|Primary key for this table.| |FUNCTIONAL_TASK_ID|| |CREATION_TIME|The date/time at which the task was created.| |START_TIME|The date/time at which the task was started. This can be null.| |END_TIME|The date/time at which the task ended. This can be null.| |SYSTEM_USER_ID|| |USERNAME|| |BPD_NAME|| |STARTING_PROCESS_ID|This appears to be a GUIID of the template of a process (BPD).

| |ACTIVITY_NAME|| |SYSTEM_TASK_ID|| |SYSTEM_FUNCTIONAL_TASK_ID|This appears to be the Process Instance ID that owns this task.| |SNAPSHOT|| |ACRONYM|| |IS_INSTANCE|| |MAX_STEP||

Database Structure for PROCESSFLOWS view

This view has a record appended to it whenever a BPD process "navigates" a sequence line in a BPD diagram. Note that only the link navigation flows are logged. We will see entries for the ids of the links followed.

|| |Column Name|Description| |BPD_ID|The UUID of the BPD template/definition. There is currently no known correlation to this value.| |BPD_INSTANCE_ID|The BPD instance ID of this instance of the process (eg. 1061). This is the instance ID that shows up in Process Inspector and elsewhere.| |LOCAL_SNAPSHOT_ID|The UUID of the snapshot containing the sequence link being traversed. Links to the SNAPSHOTS view.| |SEQUENCE_FLOW_ID|The source UUID of the activity from which the sequence line came from.| |SOURCE_EUID|| |STEP_NUMBER|| |TRACKING_GROUP_ID|| |TRACKING_POINT_ID|| |TASK_ID|| |FUNCTIONAL_TASK_ID|| |TIME_STAMP|| |SNAPSHOT|| |ACRONYM|| |TIME_STAMP_YEARS|| |TIME_STAMP_QUARTERS|| |TIME_STAMP_MONTHS|| |TIME_STAMP_WEEKS|| |TIME_STAMP_DAYS||

See also:

Miscellaneous Tracking Data Notes

To disable tracking data emission at the product level, there is a setting in 100Custom.xml that can be added/changed:

<properties> <common merge="mergeChildren"> <monitor-event-emission> <enabled merge="replace">false</enabled> </monitor-event-emission> </common> </properties>

Tracking data is not immediately written to the database. Instead, the data is cached at the Process Server side and then is periodically flushed as a unit to the database. The interval between flushes is 30 seconds by default. The value can be found in the <...>/performance-data-warehouse/config/system/00Static.xml file.

An entry called:

<transfer-execution-interval>30</transfer-execution-interval>

defines the time between successive transfers of data to the database. A second parameters called:

<transfer-block-size>500</transfer-block-size>

defines how many records should be transferred during a single cycle.

Removing tracking data tables

There is no known recipe for removing tracking data tables and views. This feels like a gross omission but it is what it is. The best recipe currently known is to "reset" the performance data warehouse which will lose all previous data.

First drop all the tables in PDW that are related to Process Center. This will usually be all the tables owned by "db2admin".

Next drop all the views.

Now we can re-execute the script to recreate the dropped tables:

<Profile>/dbscripts/PerformanceDW/<DBType>/PDWDB/createTable_PerformanceDW

Minimizing Tracking Group entries

It is tempting to create a tracking group definition with all the possible entries that you may want. However, it may be simpler to create a generic tracking group with columns for keys and columns for values and use those as markers for the data you want extracted:

Timing Intervals

Consider the following example BPD:

Notice the three occurrences of Intermediate Tracking Events. These cause Performance Data to be generated. If we wish to determine the interval between the start of a section of the process and the conclusion of that section, we can generate a Tracking Event at the start and a corresponding tracking event at the end. Once done, we can create a Timing Interval definition to measure the elapsed time between them. The Timing Interval can be created from the Performance section:

When created, we give it a unique name.

Next we specify which tracking points will be used to flag the start if the interval and which the end.

At run-time, we now need to understand how this information exposes itself. The first thing to understand is a database view called TIMINGINTERVALS. This table has the following structure:

|| |Column name|Data type|Description| |TIMING_INTERVAL_ID|Decimal|A unique id for the timing interval itself| |NAME|Varchar|The name of the timing interval| |DESCRIPTION|Clob|A description|

For example, if we create a Timing Interval definition called "MyTimingInterval" then a search of the TIMINGINTERVALS view with "NAME='MyTimingInterval'" will take us to a TIMING_INTERVAL_ID value. There is a one-to-one relationship between TIMING_INTERVAL_IDs and their associated NAME values.

The next table we are interested in is called TIMINGINTERVALVALUE

|| |Column Name|Data type|Description| |TIMING_INTERVAL_ID|Decimal|The timing interval ID. This is a unique value that is system generated for each type of Timing Interval. See the TIMINGINTERVALS view for a mapping from a name to the TIMING_INTERVAL_ID value.| |START_TRACKING_POINT_ID|Decimal|| |END_TRACKING_POINT_ID|Decimal|| |START_TIME|Timestamp|The start time of the interval| |END_TIME|Timestamp|The end time of the interval| |DURATION|Decimal|The duration of the interval in milliseconds| |START_TASK_ID|Decimal|| |END_TASK_ID|Decimal|| |FUNCTIONAL_TASK_ID|Decimal|| |START_SNAPSHOT|Varchar|| |END_SNAPSHOT|Varchar||

Be careful when creating names of Timing Intervals. If we have two process apps both of which have the same named Timing Interval, it will be difficult to tell one from another because the "NAME" column in the TIMINGINTERVALS table will not be unique.

Performance Data Warehouse (PDW) SQL Snippets

There are common patterns that keep appearing when reports are asked for. Here we will discuss and describe these patterns as well as describing some simple solutions for incorporating these into the reports.

When using the built-in SQL processing services provided by the product, the JNDI entry for the PDW DB is "jdbc/PerformanceDB".

See also:

How many have started but not finished (in progress)

Consider a process which has work to be performed and in the process the work can start and at some time later be completed. A common question is how many have process parts have started but not yet completed?

One way to achieve this is to create a field in a tracking group used to track this information. Before the work item is started, set the field to "START" and write a record to the PDW. When work completes, set the field to "END" and write a second record to the PDW. The number of items thus started but not yet finished is the number of rows that have the value "START" minus the number of rows that have the value "END".

Here is a sample BPD that shows us writing two events.

The Start_TE event looks like:

while the End_TE event looks like:

Querying the view for the tracking group will now result in a column called "FIELD1" which has either "Started" or "Ended" values within it.

Now let us focus on the query.

We want to find the count of "Started" entries and subtract from this the number of "Ended" entries. A suitable query for this would be:

SELECT count(*)-(select count(*) FROM MYTRACKINGGROUP where field1='Ended') as inFlight from MYTRACKINGGROUP where field1='Started'

As you may see, if you can't speak SQL, you may be in trouble.

Number of items of different types/day

Consider a company that sells red, green and blue widgets. During the course of a period of time (say a week), they sell a certain amount of widgets in total on each day. We can imagine a bar chart with an X-Axis of days and a Y-Axis of widgets sold. To add more detail, we want to break the Y Axis down into counts of how many were red, how many green and how many blue. How can we do this?

One way is to expose a variable in a tracking group. The variable's value will either be "Red", "Green" or "Blue". A report data source can then be.

|| |Sales Rep|Color| |A|Red| |B|Green| |A|Green| |A|Red| |B|Green|

A query then may be:

|| |Sales Rep|#Red|#Green| |A|2|1| |B|0|2|

A suitable SQL query is:

SELECT minute(time_stamp),(select count(*) FROM test2 as t2 WHERE color='green' AND minute(t1.time_stamp) = minute(t2.time_stamp)) as green, (select count(*) from test2 as t2 where color='red' and minute(t1.time_stamp) = minute(t2.time_stamp)) as red from TEST2 as t1 group by minute(time_stamp)

Getting the last state of a process

As a process instance executes, it passes from one state (activity) to another. A common request is to be able to see which processes are in which states. As the process runs, we can imagine it generating Tracking Events as it enters a new state. This means that for a given process instance we will have a growing list of records of the general format:

|| |PIID|Time|State| |P1|T1|State A| |P1|T2|State B| |P1|T3|State C| |…|…|…| |P1|Tend|State End|

So if we want to find the current state of the process, what we want to do is find the most recent record written for a given process.

SELECT * FROM TABLE ORDER BY TIME DESC FETCH FIRST 1 ROW ONLY

Breakdown of how many in which step

Consider the following illustrative process which has three steps.

Now imagine that there are many instances of this process running. For simplicity, let us say that there are 100 instances running. Of these instances, some number of them will be in step A, some in step B and some in step C. Now imagine that we want to report on where are we as a business? We could imagine producing a report that might say:

My Process

Step A

25.00%

Step B

40.00%

Step C

35.00%

From this, and at a glance, we can now tell a lot more about the operation of our business. So how can we build such a thing?

Imagine now a Tracking Group called "APPSTATETG". This has a column/field called "appState" which will hold the state of a process.

SELECT task_id as t1,MAX(time_stamp) as t2 FROM DB2ADMIN."APPSTATETG" GROUP BY task_id SELECT appstate,count(appstate) from DB2ADMIN.APPSTATETG WHERE time_stamp in (SELECT max(time_stamp) FROM DB2ADMIN."APPSTATETG" group by task_id) group by appstate

Last Auto-tracked row

The following will return data for the LAST autotracked rows:

select * from <AutoTrackTable> as T1, (select TASK_ID, MAX(TIME_STAMP) AS TMAX from <AutoTrackTable> GROUP BY TASK_ID) as T2 where T1.TASK_ID = T2.TASK_ID and T1.TIME_STAMP = T2.TMAX and T1.KPICOST is not null

The last predicate in the query appears to be necessary because Task Ids and Timestamps don't appear to be unique.

Calculating duration from two timestamps

Consider a tracking group that contains two time stamps. One is the start time and a second is the end time. We may wish to report on the duration of all completed items.

The following is an example of how to achieve that:

select (ENDTIME – STARTTIME) as DURATION from T1 where ENDTIME is not null

Counts of processes started vs completed over an interval

Consider a process which writes a start event when it begins and an end event when it completes. What we wish to build is a list of how many process instances started and ended within a given time interval. When the process starts the "datekey1" TG field is written with 'startProcess'. When the process completes, the "datekey1" TG field is written with 'endProcess'. The following SQL will illustrate this concept:

-- Return a table of 3 columns. The columns are: -- o MINUTE - The interval over which the measurement is being calculated -- o STARTCNT - The number of process instances started in this interval -- o ENDCNT - The number of process instances ending in this interval select minute(time_stamp) as minute, (select count(*) from TGTABLE as t2 where datekey1 = 'startProcess' and minute(t1.time_stamp) = minute(t2.time_stamp)) as startcnt, (select count(*) from TGTABLE as t2 where datekey1 = 'endProcess' and minute(t1.time_stamp) = minute(t2.time_stamp)) as endcnt from TGTABLE as t1 group by minute(t1.time_stamp)

General DB/SQL useful functions for reports

Date formatting

A timestamp can be formatted into a string with the varchar_format() function. This takes both a timestamp and a format string and the result is a string complying to the format.

Mapping BPM date types to DB TIMESTAMP types

In the tables generated for PDW, there are many places where the TIMESTAMP DB data type is used. Within BPM, we use a Date data type. There are times when we want to build a SQL query string and what we have is a BPM Date object and what we need is a DB TIMESTAMP object. Here is the recipe to map from one to the other.

In a SQL String, we can create a TIMESTAMP object using:

TIMESTAMP('yyyy-mm-dd hh:mm:ss')

so what we need to do is take a BPM Date object and map that to a compatible string. Fortunately, the BPM Date object has a method called "format" that will format the data for us.

var mySQL = "select X from MYTABLE where" + "TIME_STAMP = TIMESTAMP('" + tw.local.myDate.format("yyyy-MM-dd HH:mm:ss") + "')"

See also:

Number of items in the current week

select count(*) from tg1 where week(TIME_STAMP) = week(current date)

Counting when a column equals a value

Consider the idea of wanting to count columns where a column value is A or B. What we want is something like:

select count(number of columnC = A values), count(number of columnC = B values) from myTable;

Unfortunately, this simply won't work. The trick for this is:

select sum(case columnC when 'A' then 1 else 0 end), sum(case columnC when 'B' then 1 else 0 end) from myTable;

Selecting records within a date range

Consider the notion of selecting records that fall within a particular day. If we have a Tracking Group table, then it contains a column called "TIME_STAMP". To select rows in a particular day, the following can be used:

SELECT * FROM "DB2ADMIN"."DEMOTG1" where TIME_STAMP between TIMESTAMP('2013-12-26 00:00:00') and TIMESTAMP('2013-12-26 23:59:59')

See also:

Reporting with Microsoft Excel

The Microsoft Excel spreadsheet package can perform powerful charting functions and if we can import the data to be charted, we can then graph from there.

Developing Custom Dash-boards

As we have seen there are a number of techniques for recording process data into the PDW tables. Here we now start to think about how to design dash-boards including data collection, SQL queries and visualization.

Let us start by thinking about what the dashboard is to contain. Think of it as a screen which should show business information to business users. The information to be shown should be requested by the end-users of that information. It is usually wrong for the process designer to make up metrics for display without consulting with the eventual consumers. It is those consumers who will have the best notion of what it is they wish to see. Once we have some understanding of what is desired, we will need to ensure that we have a plan to be able to obtain the data for those metrics that can be used to show the results. It is no use to hear that we want to show "average salary increase by department" when no-where in the process do we deal with the salary.

Once we have seen that the process is able to generate data to achieve the metrics, we must ensure that the data is actually generated. This will usually mean the injection of new tracking points into the solution.

Next we should experiment with building the SQL statements necessary to retrieve the data. It is strongly recommended to construct and test such queries using tools such as the free IBM Data Studio.

With the SQL statements built, now we can turn our attention to building out the Human Service that will execute that SQL.

With the data now in IBM BPM, we can populate charts and graphs with the data retrieved.

At a high level, the following steps are recommended:

A Sample Dashboard

Consider the story of a procurement process. In the process a user can request to buy hardware or software, specifies the price, the department to be charged and a description. Next a manager approves or declines the purchase. A simple process.

Now let us consider the metrics that may be calculable:


The tracking group is defined as:

Which results in a table that looks like:

Let us now take out first query. This is "The number of procurement requests per month". Suitable SQL for this would be:

SELECT COUNT(*) AS COUNT, MONTH(TIME_STAMP_MONTHS) AS MONTH FROM DB2ADMIN.PROCURETG WHERE PLACE = 'END' GROUP BY TIME_STAMP_MONTHS

Now, let us build a service to work with the data. The way I like to work is to build Human Service which, when executed will return a ChartData object instance (see: Chart Control). This is the data type used by a Chart control to draw a chart instance. While I am building the service, I insert a Coach in the flow simply to show a chart while building and testing. Once I am happy with the service, I skip the Coach.

The Service I build has three primary parts. See the following example:

Part 1 called "Build SQL Query" defines the SQL statement to be executed.

Part 2 called "Execute SQL" actually executes the SQL statement to retrieve the data from the PDW.

Part 3 called "Build Chart Data" takes the results from the SQL query and builds an instance of the ChartData object.

For Part 1, the SQL query is as shown above and assigned to a local private variable called "sql".

For Part 2, we use an Execute SQL nested service call defined as follows:

Note especially the use of the "Record" data type to hold the results of the query.

Finally for Part 3, here is the populate of the ChartData object:

tw.local.chartData = { plots: [ { series: [ { label: "Procurement Requests", data: [] } ] } ] }; var chartData = []; for (var i=0; i<tw.local.Results.listLength; i++) { chartData[i]={ "name": "" + tw.local.Results[i].MONTH, "value": tw.local.Results[i].COUNT }; } tw.local.chartData.plots[0].series[0].data = chartData;

And finally, the results of this work would be:

To be able to build your own monitor charts, I maintain that one needs to be able to read the above and fully understand all the parts involved. The reality is that it isn't as complex as it may at first appear … however … it does initially appear somewhat bewildering with lots of "parts". Take the time to understand each part by itself and then, only when ready, see how they integrate together to achieve the desired overall goal.

See also:

Generating sample data

When building out custom reports, we may wish to create sample data. Unfortunately, running processes to create the data, although ideal, takes too long so what we want is a way to have the data created for us without having to run all those processes. When we realize that when a process is run, its history is saved within the PDW, we can work towards injecting rows into the PDW to build our data for the system. Again, unfortunately, it isn't as easy as that. We run into the danger of corrupting our PDW database. In addition, many of the tables have foreign key relationships to other tables which results in a great deal of confusion on simply inserting rows.

Perhaps the easiest way is to cheat. What we can do is create a second table with the same columns used in our queries. When we build our queries, we can then use a SQL "UNION ALL" statement to select from the real PDW table as well as the test table.

Here is an example. In the following we see a Tracking Group table called "DEMOTG1".

Within this table, in our example, we care about a small set of columns:


We can now create a new table called "TESTDATA" that looks like:

See that the columns that we care about have the same names.

We can now think about how to populate the new "TESTDATA" table. We have a number of options including scripts and programs but there are some useful tools available on the Internet including:

These can generate comma separated value text files that can then be loaded into the database.

When we now wish to write our SQL query, instead of coding:

select BRANCH, PROCESSSTEP, STATETAG, TIME_STAMP from DEMOTG1

we can code:

select BRANCH, PROCESSSTEP, STATETAG, TIME_STAMP from (select BRANCH, PROCESSSTEP, STATETAG, TIME_STAMP from DEMOTG1 union all select BRANCH, PROCESSSTEP, STATETAG, TIME_STAMP from TESTDATA)

Page 21


Revision #1
Created 2 years ago by Admin
Updated 2 years ago by Admin