BusinessObjects Board

Data profiling report

dataservices data quality
#1

Hi,

I need to send the data profiling report for each job to the users in the form of excel file.
I thought I could add a dataflow which would extract data from profile repository tables and make excel as target.
Please advise.

its_ranjan (BOB member since 2011-02-16)

#2

Is that for the profiling within DS Designer, or are you talking about the Profiling Results of Information Steward?

In Information Steward we have added the possibility to export your Profiling Results into an XLS document with Version 4.1.

May I ask about the use case scenario for why to export and share as an offline version the profiling results with users? What should they do when they get the exported XLS document? Is it just for FYI to read the statistics? Starting any un-coordinated data maintenance activity? …

nweigel :de: (BOB member since 2008-11-05)

#3

Yes, the profiling is within DS designer.

I am aware of Information Steward and the feature but we are using BODS 4.0 :slight_smile:

The reason for this requirement is that the analyst needs to check for all the best practices data migration jobs(BPFDM)the max,min,nulls etc.

its_ranjan (BOB member since 2011-02-16)

#4

Hi Ranjan,

In the case of BODS 4.0 you have to use the separate job using Information Steward metadata tables to pull the profiling data.

SELECT DISTINCT
dc.CTABLE_ID AS TABLE_ID,
SUBSTRING(dt.TABLE_NAME,CHARINDEX(’.’,dt.TABLE_NAME) + 1,LEN(dt.TABLE_NAME)-CHARINDEX(’.’,dt.TABLE_NAME)) AS TABLE_NAME,
dc.CCOLUMN_ID AS COLUMN_ID, dc.COLUMN_NAME AS COLUMN_NAME,
f.ATTRIBUTE_CATEGORY AS ATTRIBUTE_CATEGORY, f.ATTRIBUTE_NAME AS ATTRIBUTE_NAME,
f.ATTRIBUTE_VALUE AS ATTRIBUTE_VALUE
FROM DBO.MMT_EP_COL_MAP dc, DBO.MMT_EP_CTABLE_MAP dt, DBO.MMT_EP_ATTR_SUMMARY f
WHERE dc.CTABLE_ID = dt.CTABLE_ID
AND dt.TABLE_NAME = {$TABLE_NAME}
AND f.CTABLE_ID = dc.CTABLE_ID
AND f.CCOLUMN_ID = dc.CCOLUMN_ID
AND NOT f.ATTRIBUTE_CATEGORY IN(‘DISTINCT VALUE’, ‘PATTERN’, ‘WORD DISTRIBUTION VALUE’)
AND f.version IN
( SELECT MAX(v.VERSION)
FROM DBO.MMV_PROFILE_PROJECT p, DBO.MMT_EP_PROJ_VER_MAP v, DBO.MMT_EP_CTABLE_MAP dt
WHERE p.DATA_GROUP_ID = v.PROJECT_ID
AND v.CTABLE_ID = dt.CTABLE_ID
AND p.IS_CURRENT_VERSION = ‘Y’ AND v.IS_VALID = ‘Y’
AND v.TASK_TYPE = 1
AND p.TECHNICAL_NAME = {$PROJECT_NAME} AND dt.TABLE_NAME = {$TABLE_NAME} )
ORDER BY dc.CCOLUMN_ID

SELECT DISTINCT ATTRIBUTE_VALUE AS ROWS_PROCESSED
FROM DBO.MMT_EP_ATTR_SUMMARY f, DBO.MMT_EP_CTABLE_MAP dt
WHERE f.CTABLE_ID = dt.CTABLE_ID
AND dt.TABLE_NAME = {$TABLE_NAME}
AND f.CCOLUMN_ID = 0
AND f.version in
( SELECT MAX(v.VERSION)
FROM DBO.MMV_PROFILE_PROJECT p, DBO.MMT_EP_PROJ_VER_MAP v, DBO.MMT_EP_CTABLE_MAP dt
WHERE p.DATA_GROUP_ID = v.PROJECT_ID
AND v.CTABLE_ID = dt.CTABLE_ID
AND p.IS_CURRENT_VERSION = ‘Y’ AND v.IS_VALID = ‘Y’
AND v.TASK_TYPE = 1
AND p.TECHNICAL_NAME = {$PROJECT_NAME} AND dt.TABLE_NAME = {$TABLE_NAME} )

Thanks & Regards,
Ramana.

Ramana :india: (BOB member since 2009-04-30)