1. The presentation layer
This video will examine how users interact with the data within the data warehouse and some tools used within the presentation layer.
2. Presentation layer tools
To start our discussion, users interact with the warehouse data in the presentation layer, an area of constant development. The industry is constantly evolving to make the tools used in the presentation layer easier to use and provide more capabilities. Therefore, understand the information in this video as general trends and not as ridged rules.
We will group user interactions within the presentation layer into three categories to organize our discussion. The first are tools that can produce automated reports or are used for dashboarding. Next, we place business intelligence or BI, and data analytics tools into another group. Users use these tools to explore the data and find patterns. These tools may have a graphical drag-and-drop interface.
The final group is directly querying the data warehouse. Here users use their ability to write SQL queries to create custom queries to look at the data.
3. Automated reporting/dashboarding
One of the great benefits of having a data warehouse is the ability to automate reporting and create dashboards. The data warehouse creates a central repository of data for the organization that can be queried. Using tools to create reports automatically or update dashboards can save users such as analysts a significant amount of time and help keep the organization informed.
Examples of tools that would fall into this category include Tableau, Power BI, Google's Looker, and SAP BW. Analysts or citizen data scientists are often charged with setting up these reports. These tools tend to have graphical user interfaces with little coding required to use. This makes the data within the data warehouse available to users with a limited coding background.
4. BI/data analytics
BI and data mining tools are often used to explore the data and uncover patterns. Analysts or data scientists are likely to use these tools to convert data into actionable insights. For example, sales transactions can be considered data but identifying that sales totals are down in a particular region can be regarded as information.
These tools can vastly range in user interface complexity. Many still use graphical drag and drop, although they can also allow users to write code directly. The tools highlighted for reporting and dashboarding could also fit into this category. In addition to those, tools such as Oracle Data Mining, RapidMiner, Alteryx, and KNIME are focused on data analytics and mining.
5. Direct queries
In our final category, we have direct queries. SQL is often used to query from the data warehouse. Here users can compose their own queries of the data warehouse. A data analyst, data scientist, or data engineer with greater technical skills may want to use more advance tools.
Examples of tools in this category include SQL Server Management Studio, Azure Data Studio, and the popular programing tools of R and Python. These tools offer great flexibility in using the data warehouse to explore questions. In addition, R and Python offer additional packages to do other sophisticated analyses of the data.
As we continue to learn more about data warehousing, it is helpful to know the types of tools used by users and how they interact with the data warehouse.
6. Let's practice!
Okay, practice time!