100150 Optimizing UDCs for Reporting and Analytics 472019 Ben Harrison BI Solutions Architect Preferred Strategies Three Mile Island March 28 1979 Nuclear reactor coolant escape Stuck Open Valve ID: 772830
Download Presentation The PPT/PDF document "100150 Optimizing UDCs for Reporting and..." is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
100150 Optimizing UDCs for Reporting and Analytics 4/7/2019 Ben Harrison BI Solutions Architect Preferred Strategies
Three Mile Island March 28, 1979. Nuclear reactor coolant escape Stuck Open Valve Failure to recognize a “loss of coolant accident TrainingAmbiguous control room indicators.“In particular, a hidden indicator light led to an operator manually overriding the automatic emergency cooling system of the reactor because the operator mistakenly believed that there was too much coolant water present in the reactor and causing the steam pressure release. "Minutes to Meltdown: Three Mile Island". National Geographic. Archived from the original on April 29, 2011.am pressure release.
Agenda Preferred Strategies Overview Review of UDC design choices Example of using design choices in a reporting tool Lessons Learned
Preferred Strategies Overview Founded in 2002 Focus on market leading Reporting and BI Software JD Edwards Reporting / BI Accelerators Partnership STRATEGY PRODUCTS SERVICES STRATEGY QuickLaunch
Design, Process, Reports STRATEGY DESIGN DATA MODEL / REPORTING TOOL MAINTAIN AND VALIDATE PROCESS User Defined Codes
Design - Each category has one meaning Business Unit Division Public/Pvt Market Code Insurance Code 1000 North Pub Freeway OCIP 1001 North Pvt Subdivision 1002NorthPubSchoolOCIP 1003NorthPvtOffice2001WestPub Office3001SouthNAOverhead 9002WestPvtSchoolOCIP
Design – Values or Descriptions Division Description Division Code Public/Private Description Public/Private Code North 10 Public Pub North 10 Private Pvt North 10 PublicPub North10PrivatePvt West20PublicPub South30Not ApplicableNAWest20 PrivatePvt
Design - Each category code has levels of meaning Business Unit Division Public/Pvt and Insurance Code Market Code 1000 North PubOCIP Freeway-Big 1001 North PvtNone Subdivision-Small 1002 North PubNoneSchool 1003NorthPvtNoneOffice-OneFloor2001West PubOCIPOffice-MulitFloor3001South NAOverhead9002NorthPvtNoneSchool
Design: Putting the pieces together Accounts Work Code 100 - 999 Underground 1000 - 1999 Paving 2000 - 2999 Grading Business Unit Underground Supt Paving Supt Grading Supt Ace Hardware Bldg Bob Joe JanetFreeway ProjectSueAlexJuan Use a master account Business Unit Cat Codes Account Cat Codes
Design Choices : Reporting tool example
Lessons Learned Expanding your cat codes for enhanced reporting and speed. Product Material Type Cat Code Leather Glove L Leather Hat L Cotton Glove C Cotton Hat C Nylon Glove N Nylon Hat N Product Leather Cat CodeCotton Cat CodeNylon Cat CodeLeather Glove 100Leather Hat100 Cotton Glove010Cotton Hat 010Nylon Glove001 Nylon Hat001
Lessons Learned - Creating Hierarchies.
Lessons Learned - F100901.
Lessons Learned Creating Sort Order The data model and reporting tool matter, some tools require the sort to be unique. Expense Description Expense Code Sort Order Cat Code / Second Description Office 10 200 Snacks 20 600 Phones 30 400 Salaries 40100Travel 50300Energy60 500Computers70100
Lessons Learned Always require data validation – and blank should NOT be an option. Free form text is a nightmare. Consider the entire system, plan where your category codes will sit. Avoid too much granularity; know at what level of detail you will likely get meaningful data. Avoid creating cat codes for data elements that are already unique (i.e. Objects/Cost Types). You will need to modify your codes in the future; plan for it. Consider how to deal with slowly changing dimensions.
Preferred Strategies Reporting Tool: QuickLaunch for JD Edwards Load Tabular Step 1) Data Preparation Step 2) Data Modeling Step 3) Data Delivery Data Governance - Translation of data into business friendly format Real-time Analytics Direct Connect QuickViews Operational Data Store ETL Packages (SSIS) Enterprise Data Warehouse ETL Packages (SSIS) Metrics Governance – Single Version of Truth Microsoft SSAS Tabular Model Security 25 Perspectives 200 Relationships 2,500 Fields 1,500 Measures & KPI’s Financial Account Hierarchies Load Tabular Load Tabular Instant Analysis and Visualization with QuickLaunch Reports Build JDE reports in minutes 25+ Power BI Reports 25+ Power BI Templates Income Statements & Balance Sheets (SSRS) Excel Templates Direct ODS EDW
100150 bharrison@preferredstrategies.com