Qlikview application that capture the failure of SSIS package loading data in Data warehouse

[AH]

Hi,

Is the Govenrance application capable to capture the load failure of SSIS package in Data warehouse? If its cant then, could you please suggest me any application which can help me with that?

Thanks for your help.

Thanks,

Shan

[cspencer3]

You can build one using the data stored in your Data Warehouse. If the packages are run by a scheduler, all of the info about the jobs exists and is able to be extracted.

[AH]

Charles

I am just trying to find a way out in this issue from Qlikview side. How about, say for example, I have data in DW & QVD(Since its refreshing data from DW on a daily basis) until 1st of Feb 2015.

But today is 6th of Feb 201. Since my QVD is not showing data until today so there is some problem obviously happening in its source and source has problem pulling data from its source.

So, before knowing whats happening in the Ultimate data source i can draw some conclusion from qlikview side looking at the warning that i dont have todays data or couple of days data. If we can do something in qlikview to give us that warning.

Do you have any idea how we can do that while producing the QVD's from a single QVW? Can we do something in the QVW may be putting some date range so that it will raise some warning if the there is any data missing for couple of days/today then show that warning thorough Governance Dashboard for the QC check?

Please share your thoughts.

Appreciate your help!

Thanks,

Shan

[cspencer3]

Would it be something as simple as comparing the max(DATE_FIELD)  to Today() and if the difference is over a certain threshold (1 day, 2 days, etc.) then it would show you a nice error message in a text box or object? We have a dashboard that does this for several Tables in our Data Warehouse that are critical to the accuracy of certain dashboards. We also monitor SSIS jobs with a dashboard but it sounds like that is not what you need.

[AH]

Hi Charles,

I am actually looking for something like that. Just to Monitor Qlikview dashboards if there is no updated data even though the dashboard is refreshing data from DW on a daily basis. But in real there is no updated data in DW say for example.

Could you please share how can i do that?

Thanks,

Shan

[cspencer3]

If you had a select few tables that you wanted to monitor just load the Max(DATE) into your Dashboard for each table, and create a Little Text box that is geen and turns red when your field for Max(DATE) is too old. If you had hundreds of tables to monitor then this would not be ideal and would take a bit more work.I may be able to attach a sample QVW to show you what i mean.

[AH]

Hi Charles,

That would be great if you attach a sample QVW.

Thanks. I appreciate your help!

Shan

[cspencer3 · Accepted solution]

Ahmed100,

Attached is a sample QVW. Notice that i load three separate tables which i would like to monitor, and only one field per table. I then use the Text boxes to see if the data has not been updated. Basically saying if(DATE_FIELD - Today() >2, 'TROUBLE!', 'All is Good!'). Thus if the maximum date in the field i am monitoring is older than two days old, flag it as TROUBLE! (Also notice i change colors of the box to give a more visual warning). You can set whatever parameters you need and configure this to whatever will work for your environment. This is very simple and could certainly be built upon to a more robust system.

Does this answer your question?

[AH]

Hi Charles,

Thanks for the Example QVW. This is the sample i was looking for.

One Last Question: If I want to put a yellow sign if the data is 3 days old, more than 3 days old red sign and less than 3 days Green sign then how do i can combine these three condition in one if Satement?

='FactServiceOrderDetail:
'&

if(Today()-[DAX Service Order Created Date] >3, 'TROUBLE!', 'All is Good! ')

Appreciate your help!

Thanks,

Ahmed

[cspencer3]

in your color statement do this:

=if(Today()-[DAX Service Order Created Date] <3, vGreen,

     if(Today()-[DAX Service Order Created Date] =3, vYellow, vRed) )

**Note: I like to put my colors in the load script as variables using the rgb() function to set exact color codes. Thus if colors need to be tweaked then i dont need to change every place that color is used.