Data & BI Summit Day 1 Keynote

The kick-off is by Rose Spitzer (@RoseSpitzer), General Manager of the Power BI User Group.

John Doyle Sr. Director Product marketing

Video: Heathrow Airport showcase

Ben Watt

Park run: Every saturday a 5K run
It started out small but now has 1427 different places and over 34 million people participating!

Amanda Cofsky (@MissAmandaN) Power BI Program Manager

She starts her story with a New York taxi demo and shows some interesting new features to come:

• Incremental refresh (Premium users only)
• Drillthrough with filters and parameters (this will be released in May!)

Q&A explorer

John Doyle enters the stage again and talks about the roadmap for 6-9 months

Some interesting things coming! #PowerBI #DataAnalytics #DataBISummit @pbiusergroup pic.twitter.com/fUBnP4yiAq

— Gavin Clark (@gavinclark66) 24 april 2018

Colin Murphy

Colin starts another set of demos, starting with the Common Data Service for Analytics (CDSA)

Public preview of #PowerBI #CommonDataService coming soon!! #DataBISummit pic.twitter.com/x75t23aeaT

— Joël CREST #DataBISummit (@JoelCrest) 24 april 2018

Show details for table data under the graphs

Show details in #PowerBI reports.. #DataBISummit pic.twitter.com/tBGuGVjWOx

— Wolfgang Strasser (@wstrasser) 24 april 2018

Datapools (preview): Entities from CDS
Datapools are also available as a Power BI connector in Power BI Desktop.
You can add entities from different data sources.
Use case: Combine promotional data with CRM using Power Query online

Drill-throughs will now take all the filters/context from the page you’re on (not just the point you drilled into!), there’s a ‘show details’ feature and data pools (Common Data service for Analytics) is coming soon and going to rock the world of #analytics #DataBISummit pic.twitter.com/KLlQmwqoS2

— Rishi Sapra (@rsaprano) April 24, 2018

Data & BI Summit

I have quite some good news: in April I’m going to the Data & BI Summit in Dublin, Ireland.
The Data & BI Summit is hosted by The Power BI User Group.
With tracks like Visualise, Model, Govern and Develop there’s content for a wide range of people, from Business analysts to IT admins.

I’m particularly interested in the Microsoft Power Series where members of the so-called “Power BI dream team” will give 2,5 hour long deep dive sessions on topics like Security, Story Telling and Advanced Data Visualisation with Power BI.
What can we expect at the summit? From the website:

• Exceptional, quality content: Learn how to bring your company through the digital transformation by gaining new understandings of your data and deepening your knowledge of the Microsoft Business Intelligence tools. Products will include: Power BI, PowerApps, Flow, SQL Server, Excel, Azure, D365 and more! 
• Answers to your questions: Network with the Microsoft Power BI team, dig-in onsite to find immediate answers with industry experts, Data MVPs, and User Group Leaders while taking advantage of the opportunity to engage in interactive sessions, workshops and labs.
• Network with your peers: Enjoy countless opportunities to create lasting relationships by connecting and networking with user group peers, partners and Microsoft team members.
• Stretch your skill set: Advance your career by learning the latest updates and how they can help you and your business.

If you’re also going, let me know in the comments or on Twitter, I hope to see you there!

@NickyvV

SSDT Error Message: Unable To Cast COM Object of Type 'X' To Interface Type 'Y'

Just a short blog post today explaining a problem (and solution) I ran into when working at a client a while ago. Hopefully it is helpful for anyone.

Problem

I got this error message at a client when I tried to open an SSIS solution in SSDT:

Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSObject100'

According to this source the reason could be a badly applied Service Pack or CU for example, but I’m not entirely sure about that in my case.

Solution

The solution for this is to reregister the dll that is referred to, in this case: dts.dll.
Before starting, make sure you close any open Visual Studio instance.
In a Command Prompt go to the following path (be sure to start the Command Prompt as Administrator):

C:\Program Files\Microsoft SQL Server\120\DTS\Binn\

where 120 is you SQL Server version number.
Reregister the dts.dll by executing the command:

regsvr32 dts.dll

If all goes well you should see the following message pop up:

I found info here.

Happy coding :)

@NickyvV

T-SQL Tuesday #97: Setting Learning Goals for 2018

This months T-SQL Tuesday is hosted by Malathi Mahadevan (@sqlmal | b).
Besides my strict learning goals I’ll also provide my retrospective for 2017.

Data Science Program

I recently started the Microsoft Professional Program for Data Science, of which I completed the first 3 courses already. I want to complete course 4 before the end of the year. That leaves me with 5 courses and the capstone project. Finishing this program in the Fall of 2018 seems doable, so I’ll put that in my calendar!

MCSE

Ow, already 3 years back I passed my final exam for my MCSA. Until recently the progress to my next goal (MCSE) kind of stopped. I already had one attempt at 70-464 this year, but I failed… I guess I underestimated that one a bit, so that’s one reason I’m not going to take my next exam too early. I just didn’t have too much time to work on it since my recent switch out of consultancy and to Van Lanschot Bankiers. Another reason is the fact I also started the Data Science Program, and the fact that this year is almost finished. I’d rather wait a few months and have my MCSE for 2018 already.

Podcasts

The fact that I made the switch to Van Lanschot also meant less commuting, which is a good thing of course, but not for my time listening to podcasts. I’m having trouble keeping up with all the feeds I’m in at the moment so I’ll have to either cut the feeds or find some other time (during running maybe?) to listen to the podcasts.

Books

As you can see on My Reading Journey page I made some steady progress on my reading goal. Although there have been days and weeks I didn’t read, since I started tracking my progress again in my spreadsheet I see I keep advancing more steadily. I still have some days I don’t read at all, but at least now this is visible and I can hold myself accountable for that. The books I read this year have been a good mix between technical, personal skills and process oriented topics. I’m going to switch my technical topics this year a little bit to more closely match my work at Van Lanschot. In the beginning of next year I’ll write a blog post about my first months here.

Running

As for my personal goals: I completed 2 half marathons this year in Venlo and Eindhoven, with a PR of 1:38:59 in Eindhoven this October! Now on to a PR on the 10 km, 40 minutes would be a great goal, as my PR is now 44:27.

@NickyvV

SSRS: The Hidden Expression For the Tablix Has a Scope Parameter That Is Not Valid For an Aggregate Function

A while ago I ran into an issue when creating an SSRS report. It was a bit of a cryptic error message for me at first, as you can see below. After struggling with it for a while I finally found the issue and I thought I could as well share it so you can find it faster than I did. :)

Problem

When running my report I encountered this error message. It talks about a Hidden expression of Tablix1, and that the scope parameter used is not valid. After finding the solution the message was kind of clear to me, but the thing is, when running the report at first I was a little lost.

Solution

It is very obvious the message is about Tablix1, so I start with looking at aggregate functions I am using in that tablix. Finally, I end up checking the visibility of the tablix and I notice the expression for the display option of the tablix.

This feature is used to show or hide the tablix based on an expression. You can see the expression I used below.

As the Expression dialog states, you have to Set the expression for: Hidden. This actually means the following: if the expression evaluates to true, the tablix will be hidden. So if the expression evaluates to false, the tablix will be shown.
The problem however, is in my scope parameter: “DataSet1”. This scope parameter is optional for an aggregate function that can evaluate the aggregate function over that parameter. It can be a Row or Column group, a data region or (as it is here) a dataset.

TL;DR

The problem is, when I started with the report I entered this expression, but afterwards I altered the name of the dataset. This makes the above Hidden expression for the tablix invalid because Dataset1 doesn’t exist anymore.
So after updating the name of my dataset in the expression for the tablix my report ran just fine again.

If you have any remarks or questions, feel free to contact me on Twitter:
@NickyvV

SSMS Status Bar Colors

I want to share this quick post for setting the color of the status bar in Management Studio (SSMS).

This post is also part of the #SQLNewBlogger challenge that Ed Leighton-Dick (@eleightondick | b) started back in April 2015:

TL;DR – In April, I’m challenging myself to write (and publish!) here regularly, and I’m extending an open challenge to other new bloggers to do the same.

You can read about his idea and the origin in his blog post, for my post on SSMS keep on reading here!

In SSMS, you can go to Tools > Options > Text Editor > Editor Tab and Status Bar.

In the Status Bar Layout and Colors part, you can set the default colors for Single server connections (the standard) and Group connections. The colors you set here are for all connections when you don’t explicitly set another color for the current connection

One-time Connection Color

When you want to set a different color for a one-time connection you can do so in the Connect to Server dialog:

Click on Options >> and go to the Connection Properties tab.

In the bottom, select User custom color: and click on Select… You can select your own color there
Just keep in mind that setting the property here will keep it until you deselect it the next time. So if you don’t want this color to be applied to all subsequent connections you make, you’ll have to uncheck the box the next time you connect to a database via this connection box.

Registered Server connection

Instead of connecting to a specific database via the Connect to Server dialog, you can “save” a server/connection as a registered server. You can do this via Registered Servers > New Server Registration. On the Connection Properties tab you can also apply a custom color here as you see in the picture below, so you don’t have to undo applying the color when you’re connecting to other servers.

If you like this type of basic posts be sure to check out the #SQLNewBlogger hashtag on twitter.

@NickyvV

Suggested Reading for October 20, 2017

I came across some nice articles and blog posts in the past weeks I wanted to share:

• Loading Data using R and PowerBI by Nick Burns at SQLServerCentral
  Nick explains why he isn't a fan of Power BI's R visuals. But the alternative is loading data via an R script and display it in a native Power BI visual. I specially liked his neat technique of unpivoting data with R to end up with a key-value pair. This gives him the possibility to easily slice the final scatterplot by the keys of the key-value pair, which were the attributes (columns) in the original data set before the unpivot action.
• Configuring “all” access with dynamic row-level security by Daniil Maslyuk (@DMaslyuk | xxlbi.com)
  Daniil has a nice solution for solving a problem with dynamic RLS for a security table with 2 attributes, e.g. Department and Region where several users have "ALL" configured for 1 or 2 attributes. For example, the CEO can see all departments and regions, but the CFO can see all regions but only the Finance department. He introduces an unrelated UserSecurity table and filters the facts and dimensions by this table.
• Epic Life Quest Task: Setting the Summit Pre-Con Attendance Record by Brent Ozar (@BrentO | Ozar.me)
  Brent and Erik put themselves to the test and passed gloriously: they sold out all the 360 seats in their pre-con. Brent gives a look behind the scenes how they pulled it off and what they did to get it sold out: write a blog post, online presentation, email announcements and give more value to the attendees with stretch goals.
• A Freecon Halloween at the PASS Summit at SQLServerCentral
  If you aren't attending any of the pre-cons for Pass Summit you can go to this FreeCon. It's held on Tuesday, October 31st, with 6 sessions from different speakers: Jason Brimhall (@sqlrnnr), Louis Davidson (@drsql), Andy Leonard (@AndyLeonard), Jimmy May (@aspiringgeek), Gail Shaw (@SQLintheWild) and Wayne Sheffield (@DBAWayne), an impressive lineup I'd say. Among the topics: SSIS Framework, SQL Injection and Parameter Sniffing. You can register here.
• Power BI Pro & Power BI Premium by Adam Wilson (@AdamDWilson) at PowerBI.com
  Adam, Group Program Manager Power BI, has put together a list of the most common questions they heard from customers to provide guidance that can help as a starting point when assessing licensing options.
• Book Release: Voices from the Data Platform by Melody Zacharias (@sqlmelody | sqlmelody.com)
  7 very talented people from the SQLFamily got together and wrote this book as a start of a series of books: Melody Zacharias (@sqlmelody), Cathrine Wilhelmsen (@cathrinew), Jennifer McCown (@JenniferMcCown), Mindy Curnutt (@sqlgirl), Rie Irish (@IrishSQL), Kathi Kellenberger (@auntkathi) and Meagan Longoria (@mmarie), with a foreword by Kevin Kline (@kekline). You can get the book on Amazon.

• The purpose of this series is to help others succeed.  The authors have all ridden the elevator of success to the top and received their MVP awards to reflect that fact.  Now it is our responsibility to send that elevator back down so others may benefit from our experience.  As an indication of our intent, all profits from the sale of this book are to be donated to charities.

I hope you can get something useful out of here, feel free to let me know if you find other useful links that I should include next time!

@NickyvV