What you'll learn:
- Describe the key elements of a data warehousing solution
- Describe the main hardware considerations for building a data warehouse
- Implement a logical design for a data warehouse
- Implement a physical design for a data warehouse
- Create columnstore indexes
- Implementing a MS SQL Server Data Warehouse
- Describe the key features of SSIS
- Implement a data flow by using SSIS
- Implement control flow by using tasks and precedence constraints
- Create dynamic packages that include variables and parameters
- Debug SSIS packages
- Describe the considerations for implement an ETL solution
- Implement Data Quality Services
- Deploy SSIS projects
- Implement a Master Data Services model
- Describe how you can use custom components to extend SSIS
- Describe BI and common BI scenarios
- Create a data warehouse with Microsoft SQL Server
- Implement ETL with SQL Server Integration Services
- validate and cleanse data with SQL Server Data Quality Services and SQL Server Master Data Services.
A data warehouse is a type of data management system that is designed to enable and support business intelligence (BI) activities, especially analytics. Data warehouses are intended to perform queries and analysis and often contain large amounts of historical data. The data within a data warehouse is usually derived from a wide range of sources such as application log files and transaction applications.
A data warehouse centralizes and consolidates large amounts of data from multiple sources. Its analytical capabilities allow organizations to derive valuable business insights from their data to improve decision-making. Over time, it builds a historical record that can be invaluable to data scientists and business analysts. Because of these capabilities, a data warehouse can be considered an organization’s “single source of truth.”
Data warehouses offer the overarching and unique benefit of allowing organizations to analyze large amounts of variant data and extract significant value from it, as well as to keep a historical record.
A typical data warehouse often includes the following elements:
A relational database to store and manage data
An extraction, loading, and transformation (ELT) solution for preparing the data for analysis
Statistical analysis, reporting, and data mining capabilities
Client analysis tools for visualizing and presenting data to business users
Other, more sophisticated analytical applications that generate actionable information by applying data science and artificial intelligence (AI) algorithms, or graph and spatial features that enable more kinds of analysis of data at scale.
Power BI is a collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights. Your data may be an Excel spreadsheet, or a collection of cloud-based and on-premises hybrid data warehouses. Power BI lets you easily connect to your data sources, visualize and discover what's important, and share that with anyone or everyone you want.
Power BI consists of several elements that all work together, starting with these three basics:
A Windows desktop application called Power BI Desktop.
An online SaaS (Software as a Service) service called the Power BI service.
Power BI mobile apps for Windows, iOS, and Android devices.
