I was trying to create custom report for suppliers, But I could not find all the fields from suppliers. Is there any way where we can new columns from database to the report?
Discover More Here Solution:
Oracle BI supports querying the database directly using ‘Direct Database Request’ feature, but however this feature is generally disabled for end-user accounts.
BI shows only those columns in subject Area which have been exposed to presentation layer. So the columns you need as a part of data mapping exercise, if not shown in the subject area needs to be listed and should consider it as an issue. There are few solutions to solve this issue.
1) Technical consultant should sort it out by querying such data from database directly
2) Technical consultant can add the missing columns to the subject area, but however this needs the RPD needs to be deployed and BI servers restart
There is a list of missing fields that you are unable to map from Oracle R12 or Oracle BI during lets say, data migration?
The few options I can think of extracting data from Oracle are below:
1. Read-only access to Oracle R12 database
2. Enabling the ‘Direct Database Request’ feature in Oracle BI – which allows to run the SQL select queries against the database – provided all the columns are populated in the Oracle Data Warehouse
3. Use the Direct Database Request feature, which queries the database directly bypassing the Oracle BI security
When loading source data into SQL Server is to have Staging tables to run various scripts. ie to check for integers in Varchar columns / text in Integer column etc…, check the maximum entry in a column to avoid truncation errors. Then we can factor in Data Type lengths and data Types themselves making sure we are covering all errors with dataloads.
We should all sit together with a few examples so we get all the processes in the right order and that we understand them.
We need to know what tables & fields needs to be loaded in Horizon and NAV for intended functionality to work.
o Referring to the “NAV data migration templates”, we know what tables needs to be loaded for most of the business objects, but not for Open AR/AP Invoices, etc.,
We need inputs from SI indicating the tables that needs to be loaded for Open transactions.
o We have data dictionary, but we don’t know what tables needs to be populated for each business object
E.g. if we need to load more than one table and related data dependencies
o We need inputs from Horizon indicating the tables that needs to be loaded for each business object
Then we can finalize the mapping templates and identify the missing fields/gaps
• Identifying Gaps / Missing Columns
o Once we have the above information, we will be in a better position to ask client to include the missing columns/info.
There needs to be data migration strategy document detailing the approach of how the data will be staged and the transformations/validations that needs to be applied and run before data is loaded into Horizon & NAV.
In summary, we need the following process documents in place:
1. Data Migration Requirements & Strategy document covering –
a. Requirements Summary of all business objects
b. Data migration approach – Source files, Staging, Transformations/Validations, Loading
c. Reconciliation processes
2. Mapping Functional Design Document – one for each business object
3. Mapping Template – one for each business object for each Target
And the Mapping functional design document & Mapping template needs to be created for each business object by respective owner, thus will be useful if an object is assigned to other resource in future, as a reference to start with.