Press "Enter" to skip to content

Category: Business Intelligence

Historical Dimensions in a Kimball-Style Model

Vince Iacoboni takes a stab at improving the Kimball model:

We owe a lot to Ralph Kimball and friends. His practical warehouse design and conformed-dimension bus architecture are the industry standard. Business users can understand and query these warehouses directly and gain valuable insights into the business. Kimball’s practical approach focuses squarely on clarity and ease of use for the business users of the warehouse. Kudos to you and yours, Mr. Kimball.

That said, can the mainstay Type 2 slowly changing dimension be improved? I here present the concept of historical dimensions as a way to solve some issues with the basic Type 2 slowly changing dimension promoted by Kimball. As we will see, clearly distinguishing between current and past dimension values pays off in clarity of design, flexibility of presentation, and ease of ETL maintenance.

As I was reading this, I was thinking “This sounds like a type 4 SCD” and Vince walks us through the differences between the two ideas. I’m not absolutely sold on the idea, but it is certainly interesting.

Leave a Comment

Mistakes to Avoid in a BI Platform Migration

Chris Webb covers five things to consider when migrating your BI platform, using Power BI as an example:

Every report has a data source and getting source data in the right format for your BI platform is a substantial task – so much so, that you might be tempted to put Power BI on top of the data sources you have created for your previous BI platform with no changes. However different BI platforms need their data in different formats. Many BI platforms like their data munged together in one big table, sometimes even with data at different granularities in the same table. Power BI, on the other hand, likes its source data modelled as a star schema (you can find out what a star schema is and why it’s important here). If you don’t model your data as a star schema you may find that you see incorrect values in your reports, that report performance is poor, and that it’s a lot harder to write the DAX calculations that you need.

Four out of the five fit just as well with any other data platform technology.

Comments closed

Clarifying Nomenclature around Azure Synapse Analytics

James Serra clears a few things up:

I see a lot of confusion among many people on what features are available today in Azure Synapse Analytics (formally called Azure SQL Data Warehouse) and what features are coming in the future. Below is a picture (click to zoom) that I describe below that hopefully clears things up:

I tend to just say “Azure Synapse Analytics SQL Pools” for the product formerly known as Azure SQL Data Warehouse and save “Azure Synapse Analytics” to include Spark + hyperscale (James’s v3).

Comments closed

Visual Studio 2019 and SQL Server Extensions

Tomaz Kastrun shows how you can install support for SSIS, SSAS, and SSRS with Visual Studio 2019:

Visual Studio 2019 brings new installation of SQL Server Integration services and SQL Server Analysis Services and SQL Server Reporting Services.

There is no need to download SSDT (SQL Server Data Tools for Visual Studio) as used to do with Visual Studio 2017 or previous versions.

Installation is pretty easy once you know where to look.

Comments closed

Extracting Numerical Data Points From Images

Matt Allington visualizes changes in the Gartner magic quadrant for BI tools:

Today Gartner released the 2019 magic quadrant for Business Intelligence.  As expected (by me at least), Microsoft is continuing its trail blazing and now has a clear lead over Tableau in both ability to execute and completeness of vision.  I thought it would be interesting to see a trend over time for the last 5 years, as this is the time period that I have been a professional Power BI Consultant.  I needed some way to extract the numerical data points from the images I had collected.  This article shows you how to do that.  Here is the final output – a scatter chart with a play axis in Power BI of course.

I was just commenting the other day about how somebody should do this and Matt went and did it.

Comments closed

One More Data Gateway Is All You Need

Meagan Longoria explains when you might need data gateways when implementing an Azure BI architecture:

Let’s start with what services may require you to use a data gateway.

You will need a data gateway when you are using Power BI, Azure Analysis Services, PowerApps, Microsoft Flow, Azure Logic Apps, Azure Data Factory, or Azure ML with a data source/destination that is in a private network that isn’t connected to your Azure subscription with a VPN gateway. Note that a private network includes on-premises data sources and Azure Virtual Machines as well as Azure SQL Databases and Azure SQL Data Warehouses that require use of VNet service endpoints rather than public endpoints.  

There are a few of them so check out Meagan’s post and take notes.

Comments closed

The Forgotten Infrastructure Below Azure BI Architecture Diagrams

Meagan Longoria reminds us that there are several products which Azure BI projects need but which we tend to forget when building architectural diagrams:

Let’s start with Azure Active Directory (AAD). In order to provision the resources in the diagram, your Azure subscription must already be associated with an Active Directory. AAD is Microsoft’s cloud-based identity and access management service. Members of an organization have a user account that can sign in to various services. AAD is used to access Office 365, Power BI, and Dynamics 365, as well as the Azure portal. It can also be used to grant access and permissions to specific Azure resources.

Meagan has several of these, so check it out.

Comments closed

Integrating PowerApps With Power BI

Wolfgang Strasser continues a series on the PowerPlatform with a post showing how to integrate an existing PowerApp with Power BI:

When creating a new PowerApp using the Power BI integration, you get an additional data source – PowerBIIntegration that serves as the connection to the Power BI report. Whenever a filtering action occurs in the Power BI report, this information is available in this property.
During the PowerApps creation action I selected the action to add a new form which in the next step needs to get a connection to the Article table (which holds the additional article details).

Check out the entire series too.

Comments closed

Explaining Data Flows (And Dataflows)

Melissa Coates disambiguates “data flows” from “dataflows” because those are two totally different things:

It’s another terminology post! Earlier this week I was having a delightful lunch with Angela HenryKevin FeaselJavier Guillen, and Jason Thomas. We were chatting about various new things. Partway thru our conversation Jason stops me because he thought I was talking about Power BI Dataflows when I was really talking about Azure Data Factory Data Flows. It was kind of a funny moment actually but it did illustrate that we have some overlapping terminology coming into our world.

So, with that inspiration, let’s have a chat about some of the new data flow capabilities in the Microsoft world, shall we?

Melissa clarifies the term “data flow” (or “dataflow” as the case may be) across several products in Microsoft’s BI stack.  Worth the read.

Comments closed

Why You Should Read Gartner Critical Capabilities Reports

Jen Underwood explains the value behind Gartner Critical Capabilities reports, specifically the one for analytics and BI platforms:

Notably, the three Magic Quadrant Leaders except Tableau were ranked near the middle in all use cases. MicroStrategy, Birst, SisenseTIBCOYellowFin, Salesforce, SAS and a few other players excelled above the rest with high scores on this report. These results are a bit refreshing to see. Gartner Critical Capabilities scores seem to better align with Forrester’s rankings of Analytics and Business Intelligence Platforms and also my own understanding of several top offerings. I admit that I was surprised by these results. I was rarely – if ever – asked about several of the top scoring vendors over the past three years.

Read the whole thing, and then read the report.

Comments closed