Business Intelligence (BI)
A catchall term encompassing a variety of tools, applications and methodologies that enable organizations to collect data from internal systems and external sources. BI can be used to prepare data for analysis, develop and run queries, and create reports, dashboards and visualizations with the end goal of providing results to decision makers and end users.
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. Whether your data is a simple 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 (or discover) what’s important, and share that with anyone or everyone you want.
Power BI can be simple and fast – capable of creating quick insights from an Excel spreadsheet or a local database. But Power BI is also robust and enterprise-grade, ready for extensive modeling and real-time analytics, as well as custom development. So it can be your personal report and visualization tool, and can also serve as the analytics and decision engine behind group projects, divisions, or entire corporations
Consist of measurements, metrics, or facts of a business process. They are located at the center of a star schema or a snowflake schema surrounded by dimension tables. Fact tables provide the (usually) additive values that act as independent variables by which dimensional attributes are analyzed. Fact tables are often defined by their grain. The grain of a fact table represents the most atomic level by which the facts may be defined. For example, the grain of a sales fact table might be stated as “Sales volume by Day by Product by Store”.
A category that can be used to arrange data by facts and measures for data dicing (grouping) and slicing (filtering) purposes. Commonly used dimensions are people, products, places, and time.
A data structure that stores the values for a specific column in a table. Indexing is a way of sorting a number of records on multiple fields. For example, creating an index on a field in a table creates another data structure which holds the field value, and pointer to the record it relates to. This index structure is then sorted, allowing rapid Binary Searches to be performed on it.
Refers to the organization of data as a blueprint of how fact and dimension tables are arranged and constructed to form a relational database. A database schema specifies, based on the database administrator’s knowledge of possible applications, the facts that can enter the database, or those of interest to the possible business users.
- Snowflake Schema: An arrangement of tables in a multidimensional database such that the physical model resembles a snowflake shape. The snowflake schema consists of centralized fact tables which are connected to multiple dimensions.
- Star Schema: The simplest style of data mart schema and most common approach to developing data warehouses and dimensional data marts. The star schema gets its name from the physical model’s resemblance to a star shape with a fact table at its center and the dimension tables surrounding it representing the star’s points.
Contrasted with full load, incremental load is a means of data warehouse loading that involves only loading new or updated records. Incremental loads are useful because they run very efficiently when compared to full loads and allow for more frequent updating of the data warehouse and cubes, particularly with large data sets.
A dashboard is a style of report and analytic data visualization that brings important data to the surface for decision-making. Dashboard provides at-a-glance statistical and historical trends of an organization’s key performance indicators (KPI’s), presented in easily digestible, graphical representations. For example, a human resource dashboard may show numbers related to staff recruitment, retention and composition etc. Dashboards may contain links and actions allowing a user to drill-through to a detailed report outside of the dashboard.
A data set is a collection of data that you import or connect to. Power BI lets you connect to and import all sorts of datasets and bring all of it together in one place.
Data sets are associated with workspaces and a single data set can be part of many workspaces. When you open a workspace, the associated data sets are listed under the Data sets tab. Each listed data set represents a single source of data, for example, an Excel workbook on OneDrive, or an on-premises SSAS tabular data set, or a Sales force data set. There are many different data sources supported, and we’re adding new ones all the time.
Workspaces are containers for dashboards, reports, workbooks, and datasets in Power BI. There are two types of workspaces: My workspace and app workspaces. So what is an app? A Power BI app is a collection of dashboards and reports built to deliver key metrics for your organization. Apps are interactive but can’t be edited.
My workspace is the personal workspace for any Power BI customer to work with your own content. Only you have access to your My workspace. You can share dashboards and reports from your My Workspace. If you want to collaborate on dashboards and reports, or create an app, then you want to work in an app workspace.
App workspaces are used to collaborate and share content with colleagues. They are also the places where you create, publish, and manage apps for your organization. Think of them as staging areas and containers for the content that will make up a Power BI app. You can add colleagues to your app workspaces and collaborate on dashboards, reports, workbooks, and datasets. All app workspace members need Power BI Pro licenses, but app consumers (the colleagues who have access to the apps) don’t necessarily need Pro licenses.
A Power BI report is one or more pages of visualizations (charts and graphs like line charts, pie charts, treemaps, and many more). Visualizations are also called visuals. All of the visualizations in a report come from a single data set. Reports can be created from scratch within Power BI, can be imported with dashboards that colleagues share with you, or can be created when you connect to datasets from Excel, Power BI Desktop, databases, SaaS applications and apps. For example, when you connect to an Excel workbook that contains Power View sheets, Power BI creates a report based on those sheets. And when you connect to a SaaS application, Power BI imports a pre-built report.
When you open a workspace, the associated reports are listed under the Reports tab. Each listed report represents one or more pages of visualizations based on only one of the underlying data sets. To open a report, simply select it.
When you open an app, you’ll be presented with a dashboard. To access an underlying report, select a dashboard tile (more on these later) that was pinned from a report. Keep in mind that not all tiles are pinned from reports, so you may have to click a few tiles to find a report.
Most modern dashboard and self-service data analysis tools rely on a specialized semantic data model to optimize data for analysis. Microsoft’s BI semantic modelling platforms include Analysis Services and Power Pivot. A data model is different compared to a relational database (like Oracle or SQL Server). Common terms used to refer to these models are “cubes”, “tabular models” and “in-memory models”.
Model data is stored in hierarchical structures with pre-defined aggregated measures, calculations and KPIs. Reading data from a semantic model is typically faster and easier using tools designed for business analysts rather than application developers. Model data can also be used by developers to create more sophisticated reports and dashboards. In a well-designed BI solution, a relational database (called a data mart or dimensional model) is usually created as the foundation data source for the semantic model and then data from that database is processed into the model.
Workbooks are a special type of data sets. If you’ve read about the Data sets section above, you almost know all about workbooks. But you may be wondering why sometimes PowerBI classifies an excel workbook as a Dataset and other times as a Workbook.
When you use Get data with Excel files, you have the option to Import or Connect to the file. When you choose Connect, your workbook will appear in Power BI just like it would in Excel Online. But, unlike Excel Online, you’ll have some great features to help you pin elements from your worksheets right to your dashboards.
You can’t edit your workbook in Power BI. But if you need to make some changes, you can click Edit, and then choose to edit your workbook in Excel Online or open it in Excel on your computer. Any changes you make are saved to the workbook on OneDrive.
A drill-down action allows the user to view more details without leaving the dashboard. This is often done by expanding the levels of a hierarchy or double-clicking on a level to show members of the next level in the hierarchy. The key to enabling drill-down reporting is to understand hierarchical structures in the data and design them into a data model which is used to serve data to the dashboard.
A drill-through action allows a user to navigate from a dashboard or report to another report to see greater detail. The typical drill-through experience allows the user to see more details within the context of the item they selected.
A well-designed drill-through dashboard solution keeps the initial dashboard simple and high-performing, allowing users to navigate to different reports in different subject areas and levels of detail.
A data mart is a relational database created specifically to store analytic data that will be processed into a semantic model for reporting. There are different philosophies and approaches in the industry for preparing data for analysis but the essential concepts are the same. A data mart may either be a component of, or an alternative to, a larger data warehouse. Commonly referred to (in general terms) as “star schema” or “dimensional model”, a data mart holds a copy of data intended for reporting. In many modern Business Intelligence solutions, data moves through three (or more) different storage layers: operational database systems, data mart and the semantic model.
Power View is a self-service dashboard and analysis tool that is highly interactive and easy to use. Dashboards and reports can be created by a report designer and saved to SharePoint for users. Users with moderate data skills can create their own dashboards and reports. Power View requires Analysis Services cubes, tabular models, or Power Pivot models; and cannot be used with data in a relational database.
This tool emphasizes interactive data exploration, and slicing and filtering data in a semantic model but it doesn’t have the same level of sophistication to customize reports as Reporting Services. The built-in data slicing, interactive filtering and quick design experience are superior to SSRS.