Business-Intelligence

Power BI is amazing. And it’s starting to appear all over the place. Many enterprises don’t know what to make of it though. Some are scared that it’ll be the “next Access” where stores of uncontrolled data end up all over the organization. Power BI’s mantra of “5 minutes to Wow” is spot on. It’s easy to be impressed. But enterprises are often struggling with “what comes next after that 5 minutes?

2020-08-07

I was editing using the Power Query editor in an Analysis Services project, hosted in Visual Studio 2019. When I tried to use “Add column by example”, I received the error shown above: Unexpected Error Could not load file or assembly ‘Microsoft.DataIntegration.TransformDataByExample, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad34e35’ or one of its dependencies. The system cannot find the file specified. The problem is that the assembly had not been deployed with the Analysis Services Designer.

2020-07-16

I was working with Azure Analysis Services the other day, and was having an issue with the processing of a tabular data model. When I first tried to process, I was receiving an error that told me pretty clearly that the AS server couldn’t connect to the SQL Database. Fair enough. Wasn’t sure what had nuked those credentials but reset them. Next, I started to get this error: Failed to save modifications to the server.

2020-03-10

If you have worked with larger tables in either Power BI or Analysis Services, you’ll know that developing against them is painful. Any time the data needs to be refreshed (which happens often), you spend a lot of time sitting around waiting for it to happen. Now our buddy Chris Webb proposed a way of getting sampled data during development. That’s pretty good. And fellow MVP Marc Lelijveld also uses a similar method.

2020-02-28

I’ve been working a lot with ADF (Azure Data Factory) again lately. If you’ve started to work with ADF and aren’t using Git in conjunction with it, you need to change that now. ADF objects are also represented as JSON objects and lend themselves nicely to being stored in Git. Another key advantage is that if you don’t have Git connected, when you’re working with ADF, you don’t have a Save button.

2020-02-21

I was working with a client the other day and we had what we thought was a simple situation: A table in an SSAS tabular model project The table had two partitions We needed to remove 6 columns from the table So, we’d already removed the 6 columns from the SQL Server view that the tables were being loaded from. We just needed to update the tabular model. Now for an unpartitioned table, that’s easy.

2020-02-14

I’ll start this post by mentioning that there’s a general consensus that you should avoid columns in tabular data models that have both dates and times. That’s largely because they don’t compress well. However, sometimes you really do need to have both a date and a time in a single column. For example, if you want to sort one column by the date and time, we have no option to sort one column by two other columns.

2020-01-31

I have a client who’s publishing their tabular data models to Azure Analysis Services (AAS). They want to publish a table that’s visible, but only has a single column that’s hidden. You might wonder why he wanted to do that. He’s trying to have a table with no existing columns that’s an anchor point for report designers to attach their report-specific measures. There are measures and computed columns in the tabular data model in AAS.

2020-01-24

I wrote recently about menu items being missing from SSIS and other BI projects in VS2019, but another thing that’s gone MIA is the Integration Services Connector for Power Query. This is the connector that allows you to use Power Query (and the M language) to input data into SSIS. The SSIS designer for VS2019 recently went GA (general availability). It’s great that it’s released, but if you install it, you’ll find it removes the Power Query option for SSIS.

2020-01-01

If you’ve recently installed Visual Studio 2019 (VS2019), and then installed the SQL Server Reporting Services (SSRS), the SQL Server Analysis Services (SSAS), and/or SQL Server Integration Services (SSIS) extensions, when you open a project you might be wondering where your menus went. How it was For example, if you had a report open in the VS2017 report designer, this menu was present: And if you had an AS model open in the VS2017 tabular designer, these menus were present:

2020-01-01