Databases Reference
In-Depth Information
Data warehouse outbound
There are several recipients of data from the data warehouse, including end-user applications, data-
marts, and analytical databases.
End-user application
This includes the reporting, analytics, web, and desktop applications that directly interact with the
data warehouse. We include the web application since many reporting or analytic applications can be
used on a pure-play web browser or a mobile application.
Data outbound to users
●
Overheads—the end-user application layer adds security constraints to the data based on the user
who is accessing the application. This constraint cannot be managed as a database-level filter
in many situations, and the impact of this overhead is often data result sets that are larger than
needed and will be filtered and discarded at the end-user system level.
●
Dependencies—the processing capabilities of the application at the end user depend on the
amount of disk, memory, cache, and processor available to the application. Large volumes of data
mean multiple cycles of exchange between the application server and the end-user system, adding
network traffic and waits.
●
Issues—many a time, due to the volume of data and the amount of elapsed time when waiting on
the data, the end user loses connection to the application server due to timeouts, the application goes
into a sleep mode, or the priority of execution of the application is lowered on the end-user system.
●
Note—the situation is similar or even worse when you remove the application server and directly
connect to a datamart or data warehouse.
●
Workarounds—current workarounds include:
●
Special tables and/or views (at the database layer).
●
Semantic layer filters and data structures.
●
Adding security filters to queries.
●
Adding infrastructure to process data and move data faster.
●
Complexities—the data within the data warehouse is clearly articulated for usage by the
enterprise. This imposes restrictions on the amount of transformation or data granularity level for
the data warehouse. From an outbound perspective this is the biggest area of complexity, as many
processing iterations from a transformation requirement and different types of calculations are
worked upon to complete the query's requirement.
●
Goal—the goal for creating an effective workload strategy at this layer is to minimize the amount
of work to be done by this layer to manifest the dashboard, report, or metric.
Data inbound from users
End-user applications rarely send data directly to the data warehouse. The inbound data comes from
ETL processes.
Datamarts
The behavior characteristics of a datamart are similar to the data warehouse but not with the same
level of complexity.
