Please see attached file: 12507_No_Data_or_Wrong_Data_in_a_Dashboard_Component.pdf
Step 1: Dates and Filters
In the View/Viewer or in Designer’s Preview mode, right-click on the component and select Information from popup menu.
Here you can verify:
- Metrics used
- Time interval applied
- Filters: dimension and value(s)
If time interval or filters are wrong:
- In Designer, check the settings for the component itself. If the settings are wrong, fix it.
- If the component settings are OK, the time interval or filters were changed by Filters or other components acting as a filter. Verify the settings for these filters or make sure the component is not listening to them by unchecking the names of the corresponding Groups in the Incoming Actions tab of the component’s Actions Wizard.
If everything looks good, proceed to Step 2.
Step 2: User Filters
User Filters that have been applied to the dashboard will not appear in the Information pop-up. You can verify your active User Filters with the Insight Admin tool. To further identify the User Filters and associated filter values being applied to a given component, you can analyze the queries and data as described in Step 3.10.
If everything looks good, proceed to Step 3.
Step 3: Queries and Data
To continue troubleshooting:
- Open the dashboard in Viewer, View or Preview. Navigate to a View with the problem component.
- On the web server, go to folder c:\temp (or location where logs are written to).
- Delete log files KPIReader.log (for Insight 4.X) (or DataProcessing.log for Insight 5.X) and WcfDataService.log.
- In dashboard, right-click on the component and select “Reset”.
- Open KPIReader.log (or DataProcessing.log).
- Verify the log file has no exceptions.
- If the log file is empty, make sure to enable logging for the component of interest. This can be done in Designer, or at runtime by right-clicking the component and selecting “Enable logging”. Return to Step 3.3.
- If exception exists, look at the nature of the exception.
- If the exception is related to “Cannot connect” or “Cannot login”, verify the connection string. In the log file, the connection looks like this:
[Data Source: Data Source=.\sqlexpress;Initial Catalog=Demo3_dara; User Id=sa;Password=********; Database name: MS SQL Server Provider name: sql]
- If the exception is related to SQL query execution, look at the type of the exception:
- If it says a database object (table or field) is missing, make sure you executed Apply Changes (publish) command in Studio after making changes.
- Sometimes an object may not be found if the use of schema is required by the database server but it is not checked in Data Source Editor in Studio:
- If the syntax of the query is wrong, find out in Studio how the query is defined.
- If Direct SQL entry is used make sure the syntax is correct.
- If the problem is a particular DB function, make sure this function is supported by your DBMS.
If there are no exceptions, the next part is looking into the queries. Depending on how many metrics are displayed in the component, the log file could contain multiple queries.
Note: If the dashboard view contains additional components, it can simply troubleshooting and reduce log entries to create a new view with just the component of interest. Similarly, if there are both base and derived metrics within the component, it can simplify troubleshooting to start off by testing just the base metric, and then add additional metrics from there.
In such cases the queries belong to three groups – create temp tables, populating them, and joining them.
- First you can check for applied User Filters and their values.
This will initially appear like:
UserFilters (1 item): Country_Filter: [Canada, USA]
The actual WHERE clause of the query with the User Filter applied will then look like:
WHERE a5.[ IsValidValue] <> 0 AND a5.[ IntervalStartTime] >= convert(datetime, '1998-04-07 00:00:00.000', 121) AND a5.[ IntervalStartTime] < convert(datetime, '1998-05-07 00:00:00.000', 121) AND a5.[Country] IN (N'Canada', N'USA')
- Creating temporary tables.
The query looks like:
CREATE TABLE [#Order Count MS_f528400968664e1ea04a77e4b8d74b83] (
[ Result] float, [Month] datetime)
Such query rarely causes any troubles.
- If you don’t see any queries in the log file:
- Maybe the cache is on.
Open the configuration file:
and make sure the key:
<setting name="CacheReadersResults" serializeAs="String">
has the value:
- Open log file c:\temp\WcfDataService.log and check for any exceptions there.
- Maybe the cache is on.
- Populating the temp tables with the metric data:
INSERT INTO [#Order Count MS_f528400968664e1ea04a77e4b8d74b83] ([ Result], [Month])
(SUM(a1.[ Result])) as "SUM0",
(DATEADD(month, DATEDIFF(month, 0, a1.[ IntervalStartTime]), 0)) as "MONTH_START0" FROM [Order Count MS] "a1"
WHERE a1.[ IsValidValue] <> 0 AND a1.[ IntervalStartTime] >= convert(datetime, '2012-02-02 00:00:00.000', 121) AND a1.[ IntervalStartTime] < convert(datetime, '2012-08-02 00:00:00.000', 121) ANDa1.[ShipVia] = 1
GROUP BY DATEADD(month, DATEDIFF(month, 0, a1.[ IntervalStartTime]), 0)
This query would be a focal point of investigation.
- Look into the dates in WHERE clause. Are the dates correct? If not, go back to Step 1 and verify which time filters affect the component.
- Look into any additional filters (in this example, we have a1.[ShipVia] = 1). If the condition is wrong, again – go to Step 1. Also look for User Filters as described in Step 2 and in Step 3.10 above.
- If the query looks right but the produced data is wrong, copy the SELECT part of the query into your favorite SQL tool (like MS SQL Server Management Studio) and execute.
- If you’re not getting the data you expect, analyze the data. You may simplify the query, for example, by removing JOIN if it exists in the query.
- Then the temp tables are joined together to produce the final result.
The query looks like:
(Select0.[COUNT0]) as "a0",
(Select0.[column0]) as "a1",
(Select0.[column1]) as "a2", (Select0.[COALESCE0]) as "a3"
(COUNT(1) OVER (PARTITION BY 1)) as "COUNT0",
(a4.[ Result]) as "column0",
(a5.[ Result]) as "column1",
(COALESCE(a6.[Month], a4.[Month], a5.[Month])) as "COALESCE0",
(ROW_NUMBER() OVER (ORDER BY (COALESCE(a6.[Month], a4.[Month], a5.[Month])) ASC)) as "ROW_NUMBER0"
FROM [#DD_aef04b04752a4a28bad5f16053d89a38] "a6"
LEFT OUTER JOIN [#Order Count MS_f528400968664e1ea04a77e4b8d74b83] "a4" ON (a6.[Month] = a4. [Month])
LEFT OUTER JOIN [#Order Count MS_55016e32bfd6465ba2a4e9a577f5aacc] "a5" ON (a6.[Month] = a5. [Month])
WHERE (a4.[ Result] IS NOT NULL OR a5.[ Result] IS NOT NULL)
WHERE (Select0.[ROW_NUMBER0] BETWEEN 1 AND 5000)
ORDER BY Select0.[COALESCE0] ASC
- Right after the query the log shows the actual results, like this:
2013-07-19 12:11:12,644  INFO - /* Execution time: 00:00:00.0090939 */
2013-07-19 12:11:12,645  INFO - /* 6 Rows. Reading time: 00:00:00.0002370 "a0", "a1", "a2", "a3"
6, 32, 41, 02/01/2012 00:00:00
6, 40, 35, 03/01/2012 00:00:00
6, 43, 40, 04/01/2012 00:00:00
6, 55, 43, 05/01/2012 00:00:00
6, 36, 55, 06/01/2012 00:00:00
6, 60, 36, 07/01/2012 00:00:00
- If the queries for temp tables have the right results but the final join doesn’t produce the result, the possible issue is the temp tables can’t be joined together. For example, the component shows the breakdown by dimension with the values different for each of the metrics shown.
Another possibility is that the queries are correct, but there is simply no data (or stale or incorrect data) in the source database(s).
- If you could not determine the cause of the problem, submit a support ticket using the Kofax Customer Portal.
Keywords: Altosoft, Insight, data, dashboard, component, user, filter, query, kpireader, dataprocessing, missing, wrong