JDEdwards ODS strategy for external reporting tool

earthdog

earthdog

Active Member
We will move to a centralized JDE 9.2 environment.

Present Situation: 4 separate ERP8.0 installations on AS/400
Future: 1 x ERP 9.2 on AS/400 OR Oracle stack

We are going to use also an external Reporting tool for operational reporting needs. this is NOT BI.

So in order not for this tool to hit on JDE native db i am thinking of creating a near real-time ODS for that.

An important aspect of this ODS will be to have julian dates and decimals translated for any tool that will access it.

What is your proposals/experiences for this?
 
Aa last two options in my mind:
1)create fucntions at db level (on the ODS instance) to handle dates and decimals (and udc values). I have done this in a vertical DWH system.
2)If someone in your team is skilled on java and you access the db with jdbc tech just implement a cusotm jdbc driver (you need to override only few methods) to handle the dates and the datadictionary in general.
 
This is fine for splitting off the performance hit for reporting if your budget can handle it.
Consider how you are dealing with data security when being accessed from outside of JDE.

Fixing the data during the copy is fairly standard data warehousing kind of stuff, though I would do tend to do a straight mirror of the data and create "fixed" views for particular purposes, rather than changing the data. Depending on "any tool that will access it", there are a number of reporting tools that will support the JDE DD and dates, that would then retain their functionality.
 
Aa last two options in my mind:
1)create fucntions at db level (on the ODS instance) to handle dates and decimals (and udc values). I have done this in a vertical DWH system.
2)If someone in your team is skilled on java and you access the db with jdbc tech just implement a cusotm jdbc driver (you need to override only few methods) to handle the dates and the datadictionary in general.

About number #1 , the only point here is how i would use them without performance impact, i suppose

I like the second idea a lot, i havent thought of it, thanks!

This is fine for splitting off the performance hit for reporting if your budget can handle it.
Consider how you are dealing with data security when being accessed from outside of JDE.

Fixing the data during the copy is fairly standard data warehousing kind of stuff, though I would do tend to do a straight mirror of the data and create "fixed" views for particular purposes, rather than changing the data. Depending on "any tool that will access it", there are a number of reporting tools that will support the JDE DD and dates, that would then retain their functionality.

Just to tell you that probably JDE db will be AS400 or Oracle and ODS DB will be SQL Server 2014. Also the external reporting tool will probably be IBM Cognos as we use it already for Corporate BI. I will be modelling the ODS there and head off to reports...

David, when you say "there are a number of reporting tools that will support the JDE DD and dates" to which you are referring to?
 
QlikView, Insight, ReportsNow are some. I can't recommend, as I don't use them, that would be a better question for financial gurus.
 
Earthdog,

when you say "operational reporting" are you referring primarily to financial reporting or "across the board" types of reporting - Manufacturing, Distribution, Finance, Sales, etc?

The second question is regarding the size/amount of data you'll have - do you really need a separate ODS database? If you are a high transaction volume business with many terabytes of data then absolutely separate the reporting. But there is another alternative with Oracle (and probably SQL Server as well). Consider a Oracle configuration that uses Oracle's "Data Guard" as both a High Availability Stand-By Database as well as a operational reporting database.

Regarding "normalizing" the dates and numbers with decimals. There's a negligible amount of overhead in setting up database views that use a standard function to convert dates and simple math to convert decimals. Plus you gain the opportunity to use meaningful names for fields - eg "Item_GL_Class" instead of "IBGLC".

There's my 2 cents
 
To follow on Larry's comments, if you go IBM i (AS/400) you can use one of the mirroring tools (iCluster, Mimix, etc). This is what we do. We use Cognos attached to our HA Server so the Ad hoc queries do not impact production.

Tom
 
@Larry and Tom

Yeap i am close to your point of view..Low level ODS transform with minimum effort.

Larry, No our data is close to 1TB but we are not of very high transaction rate..And yes i mean across the board reporting, not only financial.....

I setup a SSRS server from SQL Server 2014 and evaluating metadata in views and reporting in SSRS right now...
 
FYI all our operational reporting (Business Objects - Crystal Reports) hits the production database and we have no performance issues. Our reporting is 70/30 On-Demand vs Overnight batch jobs. ~ 400 reports. Production (excluding CRP and TEST) Database Data Only is around 250GB. F0911 > 10GB. Typical On-Demand reports turn around is 10 seconds or less.

I understand that there are "tipping points" where once you reach a certain size you may need to separate OLTP from Reporting. We haven't hit it yet.

Good Luck
 
FYI all our operational reporting (Business Objects - Crystal Reports) hits the production database and we have no performance issues. Our reporting is 70/30 On-Demand vs Overnight batch jobs. ~ 400 reports. Production (excluding CRP and TEST) Database Data Only is around 250GB. F0911 > 10GB. Typical On-Demand reports turn around is 10 seconds or less.

I understand that there are "tipping points" where once you reach a certain size you may need to separate OLTP from Reporting. We haven't hit it yet.

Good Luck

Larry, so if i guess correct you have your universe which implements the data dictionary logic or you do it with views? (decimals,julians).

And what about performance, i suppose you maintain extra indexes for your crystals in order to have short report turn around, correct?
 
We have a couple of Universes but 95% of reports go directly after Database Views.

No - no performance enhancers (indices) added :)
 
Larry, Our issue is the 'ad hoc' queries, where they join the F0911 with the F4111 and F4211 ;)

Tom
 
Back
Top