SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Data Savvy

Meagan Longoria is a business intelligence consultant with BlueGranite in Denver, CO where she shares her expertise in Microsoft BI and data warehousing through solution envisioning, architecture design, solution development, training, and blogging. She is a speaker at user groups and conferences across the country.

Installing the Microsoft.ACE.OLEDB.12.0 Provider for Both 64-bit and 32-bit Processing

I recently got a new laptop and had to go through the ritual of reinstalling all my programs and drivers. I sometimes work with SSIS locally to import data from Excel and occasionally do demos with Power BI where I read from an Access database so I needed to install… Read more

2 comments, 5 reads

Posted in Data Savvy on 20 July 2017

Updating a SharePoint List Item With Flow When You Don’t Have The ID

I recently worked on a project that used Flow to update a SharePoint list each time an item was updated in the Power Apps Common Data Service. In order to update a SharePoint list item, you must have the unique ID, even if there are other fields that are unique… Read more

0 comments, 154 reads

Posted in Data Savvy on 17 July 2017

Azure Data Factory and the Case of the Missing JRE That Wasn’t

On a recent project I used Azure Data Factory (ADF) to retrieve data from an on premises SQL Server 2014 instance and land them in Azure Data Lake Store (ADLS) as ORC files. This required the use of the Data Management Gateway (DMG). Setup was quick and easy in… Read more

0 comments, 405 reads

Posted in Data Savvy on 1 May 2017

I Like to Move It, Move It – But Azure Data Factory Doesn’t

I’ve spent the last couple of months working on a project that includes Azure Data Factory and Azure Data Warehouse. ADF has some nice capabilities for file management that never made it into SSIS such as zip/unzip files and copy from/to SFTP. But it also has some gaps I had… Read more

0 comments, 202 reads

Posted in Data Savvy on 11 April 2017

Insufficient Disk Space (T-SQL Tuesday #88)

This month’s T-SQL Tuesday – hosted by Kennie T Pontoppidan(@KennieNP) – is called “The daily (database-related) WTF“. He asked us to be inspired by the IT horror stories from http://thedailywtf.com, and tell our own daily WTF story.

Years ago in a previous job, I… Read more

2 comments, 2,401 reads

Posted in Data Savvy on 14 March 2017

Copying data from On Prem SQL to ADLS with ADF and Biml – Part 2

I showed in my previous post how we generated the datasets for our Azure Data Factory pipelines. In this post, I’ll show the BimlScript for our pipelines. Pipelines define the activities, identify the input and output datasets for those activities, and set an execution schedule. We were creating several pipelines… Read more

0 comments, 1,407 reads

Posted in Data Savvy on 10 March 2017

Copying data from On Prem SQL to ADLS with ADF and Biml – Part 1

Apologies for the overly acronym-laden title as I was trying to keep it concise but descriptive. And we all know that adding technologies to your repertoire means adding more acronyms.

My coworker Levi and I are working on a project where we copy data from an on-premises SQL Server 2014… Read more

1 comments, 262 reads

Posted in Data Savvy on 3 March 2017

Please Lend Me Your Vote for Documentation of TMSCHEMA DMVs

I spent a good bit of time looking for the definitions/descriptions of the TMSCHEMA DMVs that allow us to view metadata and monitor the health of SSAS 2016 tabular models. As far as I can tell there are no details about them on any Microsoft site. Many of the columns… Read more

2 comments, 475 reads

Posted in Data Savvy on 7 November 2016

Process Compatibility Level 1200 SSAS Tabular Model from SSIS 2014

A client wanted to upgrade their SSAS model to SSAS 2016 to take advantage of some of the features of the new level 1200 compatibility model. But they weren’t yet ready to upgrade their SSIS server from SQL 2014. This presented a problem because they had been using the Analysis… Read more

7 comments, 1,094 reads

Posted in Data Savvy on 14 October 2016

Documenting your Tabular or Power BI Model

If you were used to documenting your SSAS model using the MDSchema rowsets, you might have noticed that some of them do not work with the new tabular models. For example, the MDSCHEMA_MEASUREGROUP_DIMENSIONS DMV seems to just return one row per table rather than a list of the relationships… Read more

10 comments, 228 reads

Posted in Data Savvy on 5 October 2016

BYO Time Zone Conversion With SSAS DMVs

This is just a quick note (since I apparently forgot and was puzzled for a moment) that the MDSCHEMA_CUBES DMV shows you LAST_DATA_UPDATE (last processed date) in UTC, regardless of the timezone of your SSAS Server. Marco Russo has a great post about all the ways you can get your… Read more

0 comments, 153 reads

Posted in Data Savvy on 21 September 2016

DAX Date Dimension and Fun with Date Math

I was working on a SSAS Tabular 2016 solution for a project for which I had no data (an empty data model, but no data). I was not in control of the source data warehouse, so I couldn’t change what I had, but I needed to get started. So I… Read more

0 comments, 193 reads

Posted in Data Savvy on 3 September 2016

Create a Date Dimension in Azure SQL Data Warehouse

Most data warehouses and data marts require a date dimension or calendar table. Those of us that have been building data warehouses in SQL Server for a while have collected our favorite scripts to build out a date dimension. For a standard date dimension, I am a fan of Aaron… Read more

4 comments, 1,414 reads

Posted in Data Savvy on 6 August 2016

PolyBase Is A Picky Eater – Remove Carriage Returns Before Ingesting Text

Update: As Gerhard points out in the comments, switching to ORC files solves this issue nicely. It’s not human readable, but it is much less error-prone when reading in data. 

I’ve spent the last few weeks working on a project that used PolyBase to load data from Azure Blob Storage… Read more

5 comments, 1,491 reads

Posted in Data Savvy on 1 August 2016

Using Context To Traverse Hierarchies In DAX

My friend and coworker Melissa Coates (aka @sqlchick) messaged me the other day to see if I could help with a DAX formula. She had a Power BI dashboard in which she needed a very particular interaction to occur. She had slicers for geographic attributes such as Region and… Read more

0 comments, 311 reads

Posted in Data Savvy on 28 July 2016

BimlScript – Get to Know Your Control Nuggets

This is post #2 of my BimlScript – Get to Know Your Code Nuggets series. To learn about text nuggets, see my first post.  

The next type of BimlScript code nugget I’d like to discuss is the control nugget. Control nuggets allow you to insert control logic to determine… Read more

2 comments, 150 reads

Posted in Data Savvy on 1 July 2016

BimlScript – Get to Know Your Code Nuggets

In BimlScript, we embed nuggets of C# or VB code into our Biml (XML) in order to replace variables and automate the creation of our BI artifacts (databases, tables, SSIS packages, SSAS cubes, etc.). Code nuggets are a major ingredient in the magic sauce that is meta-data driven SSIS development… Read more

2 comments, 332 reads

Posted in Data Savvy on 30 June 2016

Colorblind Awareness and Power BI KPIs

Update: The ability to change the color of a KPI was delivered in August 2016!

Color blindness, or color vision deficiency (CVD) affects 1 in 12 men and 1 in 200 women in the world. The chances are good that you have met someone who is colorblind, but you may… Read more

2 comments, 182 reads

Posted in Data Savvy on 23 April 2016

Trekking through the DAX Jungle In Search of Lost Customers

I like to think I’m proficient at writing DAX and building SSAS tabular models. I enjoy a good challenge and appreciate requirements that cause me to stretch and learn. But sometimes I hit a point where I realize I must go for help because I’m not going to complete this… Read more

4 comments, 175 reads

Posted in Data Savvy on 1 March 2016

Creating a Matrix in Power BI With Multiple Values on Rows

This week I was asked to create a matrix in a Power BI report that looks like this:

Matrix with Values on Rows (numbers faked to protect the innocent)

To my surprise, Power BI only lets you put multiple values on columns in a matrix. You can’t stack metrics vertically.… Read more

6 comments, 259 reads

Posted in Data Savvy on 9 January 2016

Older posts