SAP IS Option : for getting more than 500 rows in output

[former_member182007]

Folks, Can anyone tell me if there is a work around to get more than 5oo output/failed rows in IS for Data insight ( Data Quality & Profiling )  ??

Is there some setting we need to perform in CMC or probablly if some patch deployment needed  ?

any input is appericiable.

thx

deep

[Former Member · Accepted solution]

Hi Deepak,

We've encountered the same problem. The 500 records limit in Information Steward wasn't suitable for us and we didn't want to use the failed records database: the business users don't have any knowledge on how to extract the failed records from a database.

Our solution was to export the business rules to Data Services and use them in a validation transform. All validation transforms are put in a data flow in a batch job where we use the same sourcedata as we use in Information Steward. This batch job runs daily, so the failed data is being refreshed on a daily basis. The failed records from the validation transforms are exported to an Excel-file on a network share from where the business users can open then and perform their required actions.

Regards,

Marc

[NielsWeigel · Accepted solution]

Hi Mark,

the important question is about the use case for getting more than 500 failed records into the Information Steward UI?

• Would a business user really want to scroll down list of thousands of failed records interactively within the IS UI? And what then?

So this was the core idea of the Failed Record Database including ALL failed records and fully open and accessible for any application or ETL tool.

So beside exporting all Validation Rules from the Information Steward to Data Services and creating a new Batch Job with same source data, why didn't you create a new Data Services Job to read data from the Failed Record Database and store the data (after any transformation you do within the job) as 1-n XLS files for the Users? So it is also the effort for creating a new DS job, but you still have all the Validation Rules that are approved and valid for your organization in one single place, the IS Repository. Our SAP MDG-SAP IS integration demo video shows how an external application can directly embed the DQ Scorecard and the failed record database to initiate the change requests for bad data: http://www.youtube.com/watch?v=z3tw91WWFiw

With SAP Information Steward 4.1 we have extended the data and the database schema for the Failed Record Database based on customers feedback to allow next level of DQ Scorecard design with external reporting tools and set up multi-dimensional reporting.

Best regards,

Niels

[Former Member · Accepted solution]

Hi Niels,

The user doesn't want to have all thousands of records in IS UI: he does want to be able to export all the records to Excel without having to bother the IT department.

There two main reasons why we don't use the failed records database:

1) the reporting needs for the scorecard are based on customer level, not on record-level. We want to see which customers fail the validation on email-address, not how many email-addresses fail the validation. So we have to aggregate the data to determine which customers failes which business rule, less important is how often he failes the business rule. The lists of failed records are created for this purpose (they're not only exported to a network directory but also to a database). We create a dataset in DS which shows for every customer on which business rules he failes. This dataset is imported again in IS and is the basis for the scorecard.

2) we use the same set of business rules for real-time validation of customer data. To do this, you need a real-time job in Data Services with the business rules of IS in it. The failed records database can off course not be used for real-time validation purposes 🙂

Kind regards,

Marc

[NielsWeigel · Accepted solution]

Hi Marc,

fully understand now and great smart approach:

1) Use a pre-calculation of pass and fail on individual rules within the the Data Services jobs and then create the IS Validation Rules to be bound to these pre-calculated results for DQ Scorecard and Dashboarding purpose on that aggregated view (especially in your relational data model).

2) Correct, Failed Record Database is only populated by the Rule Task execution within SAP Information Steward. Using same validation rules within Data Services Real-time Jobs (e.g. for checking on entry new record creation) is a validation activity and if customers want to "collect all failed records" from the real-time job, they need to have the Job itself inserting the record within the job into a dedicated table.

Again, smart approach, I like it!

Niels

[former_member187310]

Hi,

Is it possible to pull complete records from a table? For failed data we can use failed records db, but what if we need the entire data for further analysis? The window will only show 500 rows.

Appreciate comments.

Thx,

[Former Member]

Hello All:

I just created an DB connection for Failed data rule. Our data team can review more than 500 records now.

However we experienced some issue after enabled the function "view more failed data" and by using the function.

Could some expert here give some suggestion?

By using the function, Our DS/IS system was crashed down due to /opt/sap 100% full.

I checked and found there were 7 huge files (each of them was around 6 GB) generated under /opt/sap/sap_bobj/enterprise_xi40/java/pjs/container/temp/bbcsapd007l.EIMAdaptiveProcessingServer. The file name like ISExportData_xxxxxxx.csv.

I deleteded the files and brought system back.

Could any expert here advice how and where to modify some configuration file to change the exporting destination location from /opt/sap ..//  to other shared directory to avoid the issue in future?

Thanks in advance.

[Former Member]

Hi Ke,

It sounds like you're writing your failed records to your CMS database, it's filling up and bringing down your whole application. Are you using BI Platform or IPS with IS? Have you configured a job server connection and provisioned a separate database where you can write your failed records?  Once that is setup then your exports should write to a separate staging database rather than your default cms database, which is risky.

Thanks,

Chris

[Former Member]

Hi Chris

Please make a change to your failed records database via CMC (change the description) and test connection. This refreshes the connection details to the failed records database. Recalculate the score and go to view more failed records. Try to export these records now to excel.

Alternatively, create a new failed records DB in the CMC and edit the rule calculation task to write the failed records to this DB.

I have found that the connection sometimes get corrupted.

Regards

Danie

[Former Member]

Danie,

Thank you this is useful information!

Chris

[Former Member]

We are running a Data Migration Project into SAP. Our project business process is to have our data analysts translate the business requirements into Rules in IS, create tasks and generate the failed records. The failed records are then provided back to the business in excel. Last year it was a very cumbersome process to provide the failed records in excel, involving writing sql etc. This year we’d like to have the business analysts run their own tasks and generate their own failed records in excel by using IS. We upgraded to IS 4.1 to obtain that functionality but it has so far eluded us. We are on 4.1 Sp2 which should contain the fix for exporting our failed record set into excel, but we continue to get the ‘failed data processing did not complete error’, when we try to ‘view more failed records’. If anyone has found a fix for this please share.

[Former Member]

Hi Chris,

I am also having the same problem. Could you please share how to solve this issue?

Thanks,

[Former Member]

Hi Erwan,

We upgraded to DS and IS 4.1 SP3 at the suggestion of SAP in order to fix some of our issues around viewing and exporting failed data.

From either the rule tab or the scorecard's 'show more' button, you should be able to display your rules, scores and if the score shows in Red you'll have some failed data. Double-clicking on the red score should populate your failed data in the lower right corner of the screen. From there you can click 'export data' to only export the 200 or 500 rows that are displayed. Instead, I always choose 'view more failed data' to display the failed records in a pop-up window. If you have alot of records it will take a few minutes. From there you can click 'export data' and choose 'export to csv' (the 1st choice) and click 'ok'. Another minute or two and then you can name and save the file.

If you leave IS open for another 20 minutes and click 'View More Failed Data' a 2nd time, you'll likely get a connection error, because there is a still a bug that needs to be fixed in IS 4.1 SP3. IS uses Data Services 'view data service' component to serve up the IS failed records. If your DBA's have set a short timeout on your Failed Records Database, The View Data Services looses the connection. It's supposed to continually ping it and reconnect if the connection is dropped. That will probably be corrected in the SP upgrade for DS 4.1  - just a guess.

Thanks,

Chris

[Former Member]

Information Steward 4.1 SP1 release now provides option to 'View More Failed Data' from failed data repository. If you have configured 'Failed Data Repository' to collect all failed records, you are able to access those from Information Steward UI and export those to Excel.

Note that as Marc has mentioned above, we do not expect business users to view all the failed records within Information Steward UI. They can apply filtering and sorting mechanisms to all the records and view top 500 records within Information Steward UI. They can export all the records to a spreadsheet and then view them in Excel.  

[Former Member]

So as per my understanding the maximum number of failed records that can be seen from the IS UI is 500 and the remaining should be accessed from the Failed Data Repository and those cannot be viewed from IS UI. Can you please confirm..

Regards

Chandra

[Former Member]

You can now access all the records from 'Failed Data Repository' from Information Steward UI. Yes, the view is limited to 500 records but filtering/sorting mechanisms allow you to accesss different sets of 500 failed records. Also you can export all failed records from 'Failed Data Repository' to excel from Information Steward UI without having to bother IT.

[Former Member]

Thank you very much

[paula_augedahl]

Deep,

Are you referring to the Max sample data size restriction in CMC > Applications > Information Steward Setting under Rules?  If so, the max setting you can define there is 500.

My understanding is that you can identify a 'For data that failed rules' data connection in CMC > Information Steward > Connections and select that connection in your Rule task under Save all failed data to:.  This can capture all failed data rows instead of just a sampling.

Thanks,
Paula

[former_member182007]

Puala, It seems we can't set more than 500 rows in max sample output size.

thx

deep

[paula_augedahl]

Deep,

The settings in CMC > Applications > Information Steward Settings are restricted to 500 records.  You cannot change that.

If you are truly trying to capture records that fail the rules, follow the steps I mentioned above to create a conneciton for failed data and then reference it when you run your rule task so that the failed records are all written to that database.

Thanks,
Paula

[former_member182007]

Thanks Paula.