There are two primary strategies for accessing Notes data using the Data Modeler for Notes. Direct Access is where data is accessed directly in the NSF file. Data Staging is where Notes data is written, or staged, to a relational database. Either strategy may be more appropriate for the specific reporting requirement at hand.
As an example, a call center may want to report on both open and closed call tickets. There are relatively few open call tickets, but they are updated constantly. Real time displays such as management dashboards require real-time access to the live data. This calls for a Direct Access strategy to the live Notes data.
There are many, many more closed call tickets, and they are rarely, if ever, updated. It may make more sense to stage closed call ticket data to a relational database, where more complex analytics such as OLAP can be efficiently done.
The call center selects different access strategies to report on the different types of call tickets. The Data Modeler provides all of the features the call center needs to optimize their data access to provide a seamless Business User experience.
Direct Access: Direct Access uses a driver to query the Notes data directly where it resides in the Notes database and make the data structure look like a relational data structure. This approach is fast, efficient, and allows reporting in real time on documents that are frequently edited.
The Data Modeler supports the legacy Lotus NotesSQL ODBC driver and also includes the new DomSQL JDBC driver for Domino. The ODBC driver suffers from performance limitations and is suitable for relatively small data sets. The DomSQL JDBC driver offers much faster performance as well as a much more capable feature set. JDBC should now be the option of choice for virtually all direct access needs.
Whether using ODBC or JDBC, query performance will begin to degrade when accessing larger sets of data. This is due to the inherent limitations of the Notes NSF database design. If you are accessing data sets in excess of 100,000 documents or performing complex analytics on the data, You should consider staging the data to a relational database.
External Staging: External Staging uses the Data Modeler DataBridge to stage an image of the data from the Notes database to a relational database. This snapshot of the Notes data is then accessed by BI applications. This method is ideal for large sets of data where the documents are not frequently edited. The data can be staged manually or on a schedule, but the staged data represents the content of the Notes database as of the last transfer event. For many business reporting needs, a snapshot as of the end of the last business day works fine.
External Staging is also used to integrate Notes data with a Data Warehouse.
Hybrid Solution: Both of the above options can be integrated into a hybrid solution that combines the strengths of both strategies. Direct Access models can be created for dynamic sets of data that require real time reporting on documents that are often edited. Larger, infrequently edited sets of data can be externally staged to a relational data store, with a separate model created to report directly on that data store.
In the BI application, the two models can be joined using common key values for seamless and optimized reporting on the entire set of data.