360 likes | 464 Vues
3D – Data Drill Down Framework. Nitin Kumar 11/3/2010. Agenda. What is 3D – Data Drill Down? Why do you need a drill down framework? Demo High Level architecture Key features Showcase examples from Newfield Q&A. What is 3D?.
E N D
3D – Data Drill Down Framework Nitin Kumar 11/3/2010
Agenda • What is 3D – Data Drill Down? • Why do you need a drill down framework? • Demo • High Level architecture • Key features • Showcase examples from Newfield • Q&A
What is 3D? • 3D or Data Drill Down is a flexible template driven web application framework that allows you to quickly build context driven drill-downs from a source data element to more detailed layers of data • It is a standalone application – can connect to multiple databases to pull information
Why do you need a drill down framework? • Applications are designed more for data capture and processing, not so much for query/reporting • Data relevant for end-users spread across many screens/modules/reports – difficult to track data flow • Existing reports – generally very specific • Users spend huge amount of time to track down information – sifting through multitude of application screens, reports, SQL queries etc.
Why do you need a drill down framework? • A single report/query screen will not provide all the answers an user is looking for • Interactive drill down reporting is essential to navigate from summary data and drill down to details both vertically and horizontally to get relevant information • To satisfy business needs and end-users, customers build and maintain a repository of custom reports • Skilled technical resources required to build and maintain custom reports
Why do you need a drill down framework? • Reporting tools could be expensive, require additional hardware resources and you may be paying a premium for features you never use • Over a period of time the repository of custom reports become large, unmanageable and very expensive to maintain
Why do you need a drill down framework? • 3D framework was specifically designed to address this need for interactive drill-down reporting • Completely metadata driven, no coding necessary • Easy to install, learn and be productive in a very short amount of time • Very lightweight footprint ~11mb, minimal computing resources
Demo Demo
High Level Architecture Source Configuration Presentation Enterprise Upstream Layout Config. Excalibur url?p1=&p2=&p3= E-Business Suite Object Mapping LOS Other JDBC URL/Connector 3D Framework External Data Sources DB1 DB 3 DB 2
Configuration • XML based configuration • XML Schema provided for validation • Use any XML editor or text editor • Configuration divided into 2 files • Layout Mapping • Object Mapping
Configuration – Layout Mapping • The layout mapping defines one or more reports and its layouts • A report layout is divided into 2 sections: • Header section – containing common information • Detail section – relevant data grouped into one or more tabs • No limit to the number of detail tabs • Data can be displayed in tabular grid format or as a chart • Only tabs containing data will be displayed
Configuration – Layout Mapping • Each layout section – header and detail tabs are mapped to a logical object • A logical object is a user defined name for the data source that provides data for the report • The logical object is described in the Object Mapping configuration
Configuration – Layout Mapping example <drillLayout> <report id="GL-GROSS"name="LOS GL Gross"> <object tabSeq="0" tabName="Report Center Details">rc_dtls</object> <object tabSeq="1"tabName="Payables">ap_gross_dtl</object> <object tabSeq="2"tabName="Receivables">ar_gross_dtl</object> <object tabSeq="3"tabName="General ledger">gl_gross_dtl</object> <object tabSeq="4"tabName="JIB">jib_gross_dtl</object> </report> <report id="ACT-GROSS"name="LOS Activity Gross"> <object tabSeq="0" tabName="Report Center Details">rc_dtls</object> <object tabSeq="1"tabName="Payables">ap_gross_dtl</object> <object tabSeq="2"tabName="Receivables">ar_gross_dtl</object> <object tabSeq="3"tabName="General ledger">gl_gross_dtl</object> <object tabSeq="4"tabName="JIB">jib_gross_dtl</object> </report> </drillLayout>
Configuration – Object Mapping • Object mapping is used to map the logical objects defined in the layout mapping to physical database objects – Tables, Logical Views, Materialized Views • Object mapping mimics how you would write a SQL statement
Configuration – Object Mapping SELECT column1, column2,… FROM table WHERE column3 = "criteria" ORDER by column1
Configuration – Object Mapping SELECT column1, column2,… FROM table WHERE column3 = "criteria" ORDER by column1 Physical Object – Table, Logical View, Materialized View etc.
Configuration – Object Mapping SELECT column1, column2,… FROM table WHERE column3 = "criteria" ORDER by column1 Select columns from physical object
Configuration – Object Mapping SELECT column1, column2,… FROM table WHERE column3 = "criteria" ORDER by column1 Filter records based on query criteria
Configuration – Object Mapping SELECT column1, column2,… FROM table WHERE column3 = "criteria" ORDER by column1 Specify sort order
Configuration – Object Mapping <drillObjects> <drillObject objectName="logical_name"dbObject="db_object_name"> <orderBy>col1,col2</orderBy> <filters> <filterColumn name="column_name"value=""/> </filters> <columns> <column> <columnName>column_name1</columnName> <displaySequence>10</displaySequence> <displayName>Display Name</displayName> </column> <column> <columnName>column_name1</columnName> <displaySequence>20</displaySequence> <displayName>Display Name</displayName> </column> </columns> </drillObject> </drillObjects>
Configuration – Object Mapping <drillObjects> <drillObject objectName="logical_name"dbObject="db_object_name"> <orderBy>col1,col2</orderBy> <filters> <filterColumn name="column_name"value=""/> </filters> <columns> <column> <columnName>column_name1</columnName> <displaySequence>10</displaySequence> <displayName>Display Name</displayName> </column> <column> <columnName>column_name1</columnName> <displaySequence>20</displaySequence> <displayName>Display Name</displayName> </column> </columns> </drillObject> </drillObjects> Logical object name specified in layout mapping
Configuration – Object Mapping <drillObjects> <drillObject objectName="logical_name"dbObject="db_object_name"> <orderBy>col1,col2</orderBy> <filters> <filterColumn name="column_name"value=""/> </filters> <columns> <column> <columnName>column_name1</columnName> <displaySequence>10</displaySequence> <displayName>Display Name</displayName> </column> <column> <columnName>column_name1</columnName> <displaySequence>20</displaySequence> <displayName>Display Name</displayName> </column> </columns> </drillObject> </drillObjects> Physical database object name – Table, Logical View, Materialized View
Configuration – Object Mapping <drillObjects> <drillObject objectName="logical_name"dbObject="db_object_name"> <orderBy>col1,col2</orderBy> <filters> <filterColumn name="column_name"value=""/> </filters> <columns> <column> <columnName>column_name1</columnName> <displaySequence>10</displaySequence> <displayName>Display Name</displayName> </column> <column> <columnName>column_name1</columnName> <displaySequence>20</displaySequence> <displayName>Display Name</displayName> </column> </columns> </drillObject> </drillObjects> Sort order
Configuration – Object Mapping <drillObjects> <drillObject objectName="logical_name"dbObject="db_object_name"> <orderBy>col1,col2</orderBy> <filters> <filterColumn name="column_name"value=""/> </filters> <columns> <column> <columnName>column_name1</columnName> <displaySequence>10</displaySequence> <displayName>Display Name</displayName> </column> <column> <columnName>column_name1</columnName> <displaySequence>20</displaySequence> <displayName>Display Name</displayName> </column> </columns> </drillObject> </drillObjects> Filter criteria
Configuration – Object Mapping <drillObjects> <drillObject objectName="logical_name"dbObject="db_object_name"> <orderBy>col1,col2</orderBy> <filters> <filterColumn name="column_name"value=""/> </filters> <columns> <column> <columnName>column_name1</columnName> <displaySequence>10</displaySequence> <displayName>Display Name</displayName> </column> <column> <columnName>column_name2</columnName> <displaySequence>20</displaySequence> <displayName>Display Name</displayName> </column> </columns> </drillObject> </drillObjects> Select columns from physical object
Configuration – Object Mapping • Additional features of Object Mapping • Option to specify custom SQL directly • Connect each object to a different database • Add multiple drill-downs from each column to other objects in the template passing parameters from source object to destination object • Additional means of retrieving data: • Pipelined Table Functions (Oracle database) • External URL
Lookups • One of the most powerful features of the drilldown framework is to take any of the columns displayed and use that as a context for additional drill-downs • This can be chained to form multi-level drill downs very easily
Key Features • A lightweight highly configurable framework • Total flexibility in configuring data objects and data elements to be displayed • Connect to multiple databases to retrieve data in the same report – each tab can connect to, retrieve and display data from a different database • Auto-generate SQL based on columns, filters and sort information defined in configuration • Specify custom SQL to retrieve data • Configure custom URLs as a data source for non-database related external data
Key Features • Automatic layout and presentation of retrieved data in tabs • Ability to render retrieved data as charts • Ability to export any data retrieved to excel without additional configuration • Ability to make any data element displayed as hot-links to provide additional multi-level drill-downs • Ability to configure hot-links and pass parameters to external URL's such as imaging system or custom reports
Data Drill Down Framework Newfield – Demo