Current Power BI Service Limits

23 September, 2015 (11:04) | Power BI | By: Mark V

Not long ago, Microsoft announced that the limits for Datasets and Reports per Dataset had been increased to 200. You can read their announcement here. That raised some additional questions for me. I asked Microsoft and have now gotten the answers I sought.

Please note that these answers are valid as of September 23, 2015, but are subject to change in the future.



A Dataset is a connection to a single source of data, a single file, a single database, etc. So, this means you can connect to up to 200 distinct sources from your Power BI service account. Note that a Power BI Desktop file, which can connect to many sources at once, counts as a single Dataset in the Power BI service. Therefore, effective use of Power BI Desktop raises your limit of data sources well above 200.

NOTE: I don’t, as of the time of this writing, have the limit on sources supported by a Power BI Desktop file. I will provide that info once I have it.

Reports per Dataset

A Report in Power BI connects to one and only one Dataset. If you think 200 Reports per dataset does not seem like much, remember that Reports can have multiple pages of visualizations. I don’t have a maximum on number of pages at the moment.

NOTE: I got confirmation from Microsoft that tiles created via Q&A DO NOT count against this 200-Report limit. So, with effective use of Q&A for creating Dashboard tiles, you can get a lot of mileage out of 200 Reports by only using them for things that a Q&A tile cannot do.


With 200 Datasets each supporting up to 200 Reports, some simple arithmetic leads to the conclusion that you can have up to 40,000 Reports in your Power BI account. I have not seen an Enterprise environment with anything close to that number of Reports. I would think they could be out there, but, still: That’s a LOT of Reports.


You can have up to 100 Dashboards in your Power BI account. To me, this is the most limiting out of all of these numbers. The reason is that there is no real wiggle-room here as there is no comparable alternative to Dashboards. While there is not a technical reason that dictates your Dashboard tiles have to all fit on a single screen without scrolling, it is definitely a best practice to keep them to a single screen. Still, if you remember that your dashboards should be for the MOST important information that needs to be seen at a glance, you can make good choices about what gets pinned to Dashboards and what stays on the underlying Report(s). Keep in mind, too, that Dashboards can support Tiles from multiple Reports and from multiple Datasets via Q&A. So, it is possible to to have multi-purpose Dashboards if you are approaching this limit. That said, I would recommend you avoid mixing unrelated data in the same Dashboard as it could create confusion and make the Dashboard less effective overall.


There you have it. I hope that provides some clarification. I have to say, for a service with the elegance and flexibility of Power BI, and price points of FREE and $10/Month, these limits are VERY generous.

PASS Board of Directors Election

17 September, 2015 (22:20) | PASS | By: Mark V

Greetings. It’s time to vote for the open PASS Board of Directors positions. I want to start by thanking some folks.

Thank you to the Nomination Committee for their time and effort on this process:

Bill Grazziano
Grant Fritchey
Robert Davis
Lori Edwards
Bob Pusateri

I would like to thank the four candidates, all of whom have shown a passion for this community, not just with their words, but with their actions.

Ryan Adams
Tim Ford
Argenis Fernandez
Jen Stirrup

This is never an easy decision to make. I know each of these great people and have no doubt that the outcome would be great for PASS for any of them to sit on the Board.

Here are my votes, with the rationale behind each.

Jen Stirrup

I know that Jen is the only candidate for EMEA and a vote here isn’t really necessary. I am voting for her anyway because I have tremendous respect for her and the work she has already done on the PASS Board. I love how great she has been in terms of transparency. I love how she has served as a champion for the data professionals out there that aren’t DBAs or Database Developers. While the core of PASS has long been, and continues to be, with our Database Administrators and Developers, it is vital, in my opinion, for PASS to also embrace the data analysts, data scientists, and consumers of data. One way is via the PASS Business Analytics Conference. Our DBA and Database Developer friends put a lot of time and effort into helping to store our vital data and keep it safe and secure. We keep and safeguard that data so we can USE it. Jen has served well in speaking for those people.

Tim Ford

Tim has shown excellent leadership in continuing the success of the SQL Saturday program, which I believe in very strongly. To me, between his direct involvement with SQL Saturdays, his efforts on SQL Cruise are also fantastic. I hope to do one some day. :) I think Tim has demonstrated his passion and ability to lead and I would like to see him continue those efforts. I do also appreciate his support for the PASS Business Analytics Conference and serving that community.

Ryan Adams

I met Ryan Adams when I was a PASS Regional Mentor. We chatted at a Regional Mentor’s meeting at PASS Summit and participated in discussions at the PASS Chapter Leaders meeting as well. I was amazed to hear how he performed that role. I was amazed at his organization and his passion for his chapters and for PASS. He made me want to be a better Regional Mentor myself. I chatted a bit with the Chapter Leaders in his region. They were in awe at how helpful he was for them and had nothing but praise and appreciation for Ryan. I knew during those conversations that it was only a matter of time before I would see his name on a ballot for the PASS Board. I also knew that I would vote for him when it happened. The time has come.


I mentioned that this is a very hard decision. Argenis is a brilliant SQL Server professional with a tremendous passion for this community. I think he is a great candidate for the PASS Board. I have every confidence that we will see his name on a ballot again. At this time, I feel that my three choices above are the ones I need to make.

My fellow PASS members, I implore you to vote. Please make your voice heard.

Power BI Weekly Update: Oh, The Places You’ll Go

16 September, 2015 (19:14) | Power BI | By: Mark V

Microsoft has released this week’s update to the service. You can find the details here. There are a few updates this week, but I want to focus on just one.

How many times have I added a Dataset and created a Report only to discover I did so in the context of the wrong dashboard? OMG so many times. I am a slow learner, apparently. Well, it no longer matters.

As of TODAY, when pinning a visualization to a Dashboard in the Power BI service (, you get to select which Dashboard to pin it to. What??? I know! So cool. Not only that, but you can ALSO choose to create NEW dashboard during this process instead of pinning to an existing one.

I have broken this post into two sections. The first section will be a straightforward blog post on this new feature of Power BI. The second section will cover this feature again, this time via poetry as I channel my own inner Dr. Seuss.


When you are choosing to pin a visualization (either in a report OR already on a dashboard) in the Power BI service, you now have some choices to make. Until today, those choices were made for you. Figure 1 shows a shiny new visualization I created against my Chocolate Sales dataset in a Report.

Figure 1


The Pin button is located in the upper-right, as shown by the red arrow in Figure 1. Yesterday, clicking that pinned that visualization to the last dashboard you visited. For me, that was often NOT the right one. And, since there was no way to change this after the fact, I had to delete that visualization (and sometimes entire Reports) and start again after going to the right dashboard, even just for a moment. Gr.

NOW, clicking that Pin button brings up the dialog shown in Figure 2

Figure 2


Bam! The dropdown list shown by the red arrow in Figure 2 contains the list of all my dashboards so I can select which one to pin my fabulous visualization to. OR, I can choose New Dashboard and have this be the first visualization on that dashboard.

The post from Microsoft referenced at the beginning also includes the ability to choose whether to use the theme of the dashboard or keep the formatting (colors) you have on the report. That option didn’t appear for me in this case. I’ll have to play with that a bit. For now, I wanted to focus on choosing the dashboard.

I am so very happy about this.

Dr. Seuss

Today is your day!
You have Power BI!
You’re off and away!

You have data to use.
And questions to ask.
You can do it yourself.
You’re up to the task.
You head to the cloud, with that login you know.
To Power BI; you even have Pro.

You have your dataset already there.
You click on Explore and take to the air!
You click a few clicks and a bar chart appears
With no help at all! Hey, Microsoft: Cheers!

Your dataviz done, you just have to share.
You click on the Pin, and pin it to… where?
It went to some dashboard. You’re not sure which one.
You hope it’s the right one… It isn’t… Son (of a…)

Has this happened to you? Or friends that you know?
It’s happened to me. It ends happy, though.
Do not despair. Do not get the blues.

Oh, the places you’ll go, now, when you pin your viz!
I hope I am clear on how awesome this is.

Power BI Weekly Update–Drill, Baby, Drill

8 September, 2015 (22:06) | Power BI | By: Mark V

In this week’s update to the Power BI service, Microsoft added the ability to drill down into hierarchies. You can read the announcement here. There are some other cool additions as well.

I thought I would take Drill for a spin and share my thoughts.

How to Use Drill

In my Free Power BI account (I have a Free one and a Pro one for testing, etc), I have a dataset related to students selling chocolate for a school fundraiser. I decided to be really cryptic and called it Chocolate Sales.

Figure 1 shows the empty palette.

Figure 1


To start, I’ll choose my visualization, the simple Column Chart, shown by the arrow in Figure 1. That leads to Figure 2.

Figure 2


Figure 2 shows the empty Column Chart. I’ll click the box for the Teacher field, shown by the arrow. This leads to Figure 3.

Figure 3


The Teacher column now occupies the Axis of the chart. The next step requires some accurate mouse-work. You may want to stretch out a little. You don’t want to pull something. I’ll wait.


Cool. We want the drill path to lead from Teacher to Student Name First Last. So, when we click on a Teacher column in the chart, we want to drill to a column chart of the students in that class. To do that, we need to drag the Student Name First Last column into the Axis box UNDERNEATH the Teacher, as shown in Figure 4.

Figure 4


If done correctly, both Teacher and Student Name First Last will be in the Axis box, with Teacher on the top, as in Figure 5.

Figure 5


The order of the fields here is important.  The field at the top of the list in the Axis box will serve as the top level of the hierarchy, ie the top level of the drill path. Each other field below it will serve as a deeper level.

EDIT: I just played around with this more and putting the Student Name First Last ABOVE Teacher in the Axis resulted in the proper behavior anyway. So, perhaps there is some cardinality estimation behind the scenes to ensure proper behavior.

You can add more than one, but we’ll just use one to keep it simple. Our next order of business is to add a measure. For that, we can just choose Sales Amount – Total from the Chocolate Sales table, shown by the Blue arrow in Figure 5. The result is shown in Figure 6.

Figure 6


Here we have a fabulous Column Chart. Feel free to take a moment and soak it all in.


Life is good. OK. The default behavior when click on one of the columns is to filter this page of the report according to the value in that column. For example, if we click on Mr. Roboto’s column, it will cross-filter all other visualizations on the page to just Mr. Roboto’s class, as shown in Figure 7.

Figure 7


You can see in Figure 7 that the columns for the other Teacher values have become transparent, making Mr. Roboto stand out. Thank you very much, Mr. Roboto, for helping me demonstrate just what I needed to. Thank you. If we had other visualizations on this page, they would be filtered for just Mr. Roboto’s class.

In order to make the new Drill feature work, we have to change this default behavior to something new. First, we can click on Mr. Roboto’s column again to clear that cross-filtering. This takes us back to Figure 8.

Figure 8


Notice the shiny new icon in the upper right of the chart, shown by the Red arrow in Figure 8. This is the Drill Down button. This changes the mode of the visualization to Drill Down mode instead of the default Cross-Filtering mode. I made those modes up. They are not official names or anything, they just seem to fit with the behavior.

In the upper left of the figure, shown by the Blue arrow, is the Drill Down Level button. We will come to that shortly.

For now, I’ll click the Drill Down button in the upper right, resulting in Figure 9.

Figure 9


Notice the Drill Down button in the Red rectangle. It has reversed itself: instead of a dark arrow on a light background, it is a light arrow on a dark background. This is the subtle indicator that the Drill Down mode of the chart has been enabled. Now, if we click on the column for Mr. Roboto, we get the result in Figure 10.

Figure 10


Instead of cross-filtering, we have drilled down one level from Teacher to Student Name First Last, showing the Sales Amount – Total for each student in Mr. Roboto’s class. Hazzah! We can then click the Drill Up button, shown by the Red arrow in Figure 10 to go back up to the Teacher level, as in Figure 11.

Figure 11


Now, the Drill Down Level button is shown by the Red arrow. This one will take us down to the next level, but apply no filtering. So, instead of getting to the Student Name First Last level for a particular Teacher, we get all of the students, as shown in Figure 12.

Figure 12


As you can see, there is a horizontal scroll bar on the chart now, indicating there are too many values to display in the space provided. This is because this chart now holds all students from all four classes.

That sums up the explanation of how to use this new Drill feature. You can turn off the Drill mode, and return to the default Cross-Filtering mode, by clicking the Drill Down button again.

My Thoughts

I am glad this feature is there, for sure. The ability to drill up and down through hierarchical levels is a really important bit of functionality. I also acknowledge that this is just the first pass at it. The fact that the Power BI team is able to keep going with WEEKLY releases here is outstanding. I must confess that I like the way this works in Power View better, where Drill is not a mode for the visualization, but an ability you use by double-clicking instead of single-clicking. That way, you do not have to choose between cross-filtering and Drill. As it is currently, you get one of the other, but not both. I do like, however, the Drill Down Level that lets you go down a level without having to filter. That can be quite handy at times. It will be great, at some point, to be able to guide the Drill to a new report that you have designed for the purpose of being the next level of Drill. But that is getting more structured, for sure, maybe a bit too structured for the current Power BI vision.

I hope you will try this out yourself. Power BI is such an exciting offering from Microsoft. Take it for a spin. If you haven’t already, head over to and get a FREE account.

Power BI and The Open Bar

3 September, 2015 (15:53) | Power BI | By: Mark V

Back in July, the mighty Adam Saxton (Blog|Twitter) blogged about there being a limit of 10 reports per Dataset in Power BI. On your attempt to create your 11th report, you would get an error. Adam did point out, though, that you could have many pages within a report. That helped. That post from Adam mentioned above is no longer there because… <pause for effect> that limit has been raised to 200! Hazzah! Bar

I saw some grumbling over the 10-report limit. Granted, 200 is still a limit, but I feel there should be a limit. Power BI, after all, is currently an offering with only two subscription levels: FREE and $10/month. SOME manner of limit only makes sense. And 200 is definitely more than 10. It’s true. I looked it up on the Internet.

When the initial limit of 10 was announced, I asked for info from Microsoft on the maximum number of pages a Report could handle so I could write up a post and help spread the word and make sure people knew they could still do quite a bit even within that limit. When I checked back on the answer recently, I was told (per my Non-Disclosure Agreement with Microsoft) of the impending announcement and the new limit. Grand. That post would not be necessary. Hazzah, again!

This got me thinking, though. I felt the limit of 10 was pretty low and limiting, but I liked the fact that there was a limit. What???? You WANT to be limited???? Yeah. To an extent, I think it is a good thing. Here is the way I see it.

Events I have been to often have bars where people can get liquid refreshments. I, personally, seldom drink alcohol. But the vast majority of my friends do. To them, whether there is an Open Bar or a Cash Bar makes a difference. With an Open Bar, all drinks are paid for by the event rather than drinkers having to pay for them as they get them. With a Cash Bar, the drinkers have to pay as they go. I have often heard it said that an Open Bar leads to a lot more waste as people put their drinks down and forget them and just go get another cuz, hey, it didn’t cost anything. With a Cash Bar, people tend to pay more attention to their drink since they have made an investment of their own. I have not dug around in the data on this, but it seems reasonable, so let’s go with it for now.

When it comes reporting, a lot of organizations I have seen view their reporting environment, like SQL Server Reporting Services, for example, as an Open Bar. There is not a hard limit on reports. So, when in doubt, make a new report. Hey, it doesn’t cost anything, right? Ug. It actually does. Between the dev time for that report, and the testing (sigh, PLEASE test your reports, k?), and then resources involved in maintaining that report and perhaps running it via a schedule and dealing with issues, etc, there is very definitely a cost. When there is a limit on a resource, organizations tend (not always) to think more strategically about that resource. So, if you have a limit on the number of Reports you can create, you will think more about when to create a NEW report and when use an existing one that already has 99% of what the users are looking for. I have worked for several clients in a serious report sprawl situation. They may have dozens of copies of the SAME REPORT with the only difference being a hard coded value referring to a particular customer. Someone needs to learn about Parameters. But, I digress.

Even with this limit being raised to 200 in Power BI, my hope is that people take the few extra moments NOW to consider whether they REALLY need that new report, or if they can accomplish the same thing with what is already there. This hope applies to Enterprise reporting solutions (like SSRS) just as much, if not more than with Power BI.

Still, I have to hand it to Microsoft, here. It is a great example of customer feedback driving a change in a meaningful way. If you have feedback to offer around Power BI, please head over to and make your voice heard. Whether it be an idea for a feature or even just a question, the Power BI Community site is a great place to go.

My Full-Day Power BI Pre-Con for SQL Saturday 453 (Minnesota)

27 July, 2015 (12:00) | Power BI, Pre-Cons, Presentations, SQLSaturday | By: Mark V

Greetings, friends! I am delighted to announce that I will be providing a full day pre-con on Power BI as part of SQL Saturday #453 in Minnesota in October. Woohoo! As you might guess, I am really excited about Power BI. Been there, done that, got the t-shirt. It’s the old branding, but that’s fine for now. Hey, Microsoft, if you want to send me one with the new branding, I wear an XL. Thanks.

Embedded image permalink

You have a account, right? Right? Get a FREE one here.

I love the way Microsoft has been enabling users with the self-service offerings over the past few years. With Power BI, they have not just created a new offering, they have established an ecosystem for growing the Data Culture in pretty much any organization. Below is the abstract for this pre-con that just barely scratches the surface of what is possible with this fantastic technology. With their plan to release updates to the service on a weekly basis and Power BI Desktop on monthly basis, it will be even better come October. :)

Click, Click WOW: The Exciting World of Power BI

With Power BI, Microsoft makes working with data even easier… AGAIN. After years of enabling IT and Power Users, they have released a new set of capabilities that truly enables EVERYONE. The NEW Power BI includes a cloud service,, as well as a FREE, standalone application that combines Power Query, Power Pivot, and Power View into a single environment completely independent of Excel called Power BI Desktop.


This Pre-Con will cover the entire Power BI user experience, from creating dashboards in the Service to using Power BI Desktop to create data models that help you get the answers you need.


Power BI Service

·         What is Power BI?

o   General offering overview

·         Datasets

o   Importing data from a number of sources, both on-premises and in the cloud

o   Scheduling data refresh

·         Reports

o   Visualization overview

o   Pinning Reports to a Dashboard

·         Dashboards

o   Putting it all together in Tiles

o   Using natural language search to create new Tiles

o   Sharing your Dashboard with others

·         Configuration

o   Setting up the Power BI Personal Gateway to access on-premises data

o   Managing Groups for sharing and collaboration


Power BI Desktop

·         Getting/Transforming Data (Power Query)

o   Importing data from a number of sources, both on-premises and in the cloud

o   Renaming, combining, splitting columns

o   Changing formatting like Capitalization and removing unwanted spaces

o   Creating new columns

o   Replacing invalid values

o   Brief introduction to the M language

·         Designing Your Data Model (Power Pivot)

o   Creating relationships between tables

o   Using DAX to bring your model to life

o   Modeling and DAX Best Practices

·         Visualizing Data (Power View)

o   Choosing from the vast array of visualizations

o   Configuring visualizations for color, formatting, etc

o   High-level data visualization best practices


Microsoft’s goal with Power BI has been, “Five minutes to WOW!” Imagine how many WOWs we can get in a full day pre-con!

General Availability of Power BI

23 July, 2015 (22:50) | Power BI | By: Mark V

As of this very night (actually, some on Tuesday and some tonight), the NEW Power BI released. As a Business Intelligence guy, and especially as one that believes strongly in hePowerBIAllTheThingslping people to help themselves, this release is a HUGE deal for me. Over the past few years I have become a huge fan of Power Query, Power Pivot, and Power View working in Excel. I barely got started with Power BI for Office 365. I was going to dig into it more when the news of the NEW Power BI came in and was follow by a preview. That clinched it for me.

I am ecstatic with the new direction of Power BI. While Power BI for Office 365 is still there for now, the future is definitely the new service at and the associated applications and mobile apps. The decoupling of this great functionality from Office/Excel and from SQL Server just provides so much flexibility and plays beautifully into the Mobile First Cloud First push from CEO Satya Nadella. This new direction for Microsoft is just so exciting.

I strongly encourage you go head over to and sign up for a FREE (wait…FREE? yes FREE) account. Look for more content here in the future on Power BI. For now, I just want to share that it has arrived.

I do have to share something way fun. I think I may be the first person to actually tell a corporate vice president at Microsoft to go to bed on Twitter.


The response was fantastic, and about what I expected. :)


I just had to share that because it shows the passion that these folks have for what they do. We could go on about features that are not there and hem and haw, but at the end of the day, there are people working hard to provide a great experience. And I, for one, think they rocked it. To James Phillips, Jen Underwood, and so many more people who worked to make the new Power BI a reality, I say:


TARDIS – Time And Relative Distribution In SSIS

16 June, 2015 (10:00) | SSIS | By: Mark V

TARDISFans of Dr. Who will immediately recognize the title of this post. In that classic science fiction series, the Doctor has a very special vessel that lets him travel anywhere in Time and Space, even across dimensions. That vessel is called the T.A.R.D.I.S. (Time And Relative Dimension In Space). As I was crafting the idea for this post, paying homage to Dr. Who just seemed like the perfect hook.

At my current client, I have been working on an ETL solution. It involves automating the execution of about 70 unique stored procedures, each of which populate their own output table, written by another team and then consolidating all of that data into a dimensional model in a data warehouse. Many of these stored procedures do quite a bit of work and take several minutes to execute. Some run more quickly. Each of these procedures is independent of the others, so I am able to to run any of them in parallel with each other without affecting the results. This, will, however, affect server resources, so running all 70 at once would be a great way tick off the DBA. However, running them serially pretty much guarantees that I will exceed the 3 hour window in which my process must execute.

I decided to start by assigning each procedure to one of five processing silos, which I called Process Groups. I made these assignments in a table to allow for total control and tuning for best results. For example, if one of the procedures took far longer than the others, I could give that a Process Group all to itself and divide the others evenly across the remaining Process Groups. I actually created a procedure to automate that, but I will keep that for another post. For this post, I chose to just do three silos instead of five.

Note: I have included the SSIS solution as well as script that creates all necessary database objects and rows right here. I will only show code that directly impacts what I am trying to show here. Also, I am using SSIS 2014 for this post. At my client, I used SSIS 2012. While I am using a task that was introduced in SSIS 2012, namely the Expression Task, you can accomplish the same thing using a Script Task in prior versions of SSIS. So, I see no reason this could not work on 2008 R2 (and possibly earlier).

The TARDIS Child Package

I created a table called StoredProcedure with the following rows shown in Figure .

Figure 1


The purpose of this table is to control the Process Group to which each stored procedure belongs. I also created each of those stored procedures. They each insert the Process Group value passed into them into their respective tables.

CREATE TABLE dbo.Table1 ( ProcessGroup tinyint , RecordCreateDate datetime2 DEFAULT CONVERT(datetime2,GetDate()) );

I created Table1 thru Table6 with this exact same pattern.

My child package is pretty simple, as shown in Figure 2.

Figure 2


Figure 3 show the Variables in the package.

Figure 3



The first step, marked with the 1 in Figure 2, is an Execute SQL Task that calls the GetStoredProcedureProcessGroup stored procedure which is shown below.

CREATE PROCEDURE dbo.GetStoredProcedureProcessGroup @ProcessGroup tinyint AS SELECT ProcedureName FROM dbo.StoredProcedure WHERE ProcessGroup = @ProcessGroup; GO

The task is configured to pass the value of the User::ProcessGroup variable as the @ProcessGroup parameter for the stored procedure, as shown in Figures 4 and 5.

Figure 4


Figure 5


Just to show how brilliantly this procedure works, let’s execute it.

EXEC dbo.GetStoredProcedureProcessGroup @ProcessGroup = 1

It produces the following results shown in Figure 6.

Figure 6


Hold your applause, please. Feel free to check this against Figure 1 to make sure these are the procedures configured for Process Group 1. We’ll wait.

We good? Cool.

When these results get returned to the package, they are stored in User::StoredProcedureList variable, which is of the System.Object data type.

The next step in the package, marked with the 2 in Figure 2, is a For Each Loop that iterates through each row residing in the User::StoredProcedureList variable. That configuration is shown in Figures 7 and 8.

Figure 7


Figure 8


The first time through the loop, the User::StoredProcedure package variable will get the value ‘dbo.InsTable1’ which is the first row shown in Figure 6.

Package execution then moves on to the “SQL Execute Stored Procedure For Process Group” Execute SQL Task, shown by the 3 in Figure 2. This task calls the ExecuteSingleStoredProcedure stored procedure, which is shown below.

CREATE PROCEDURE dbo.ExecuteSingleStoredProcedure @StoredProcedure nvarchar(256) , @ProcessGroup tinyint AS DECLARE @SQLString nvarchar(1000) SET @SQLString = N'EXEC ' + @StoredProcedure + ' @ProcessGroup = ' + CONVERT(nchar(1),@ProcessGroup) + ';' EXEC SP_EXECUTESQL @SQLString ; GO

The configuration of the Execute SQL Task is shown in Figures 9 and 10.

Figure 9


Figure 10


The User::StoreProcedure variable was populated by the For Each Loop. The User::ProcessGroup variable will be passed from the Parent Package. For now, if we assume that value is 1, we end up with the following stored procedure call passing to the database:

EXECUTE dbo.ExecuteSingleStoredProcedure @StoredProcedure = N'dbo.InsTable1' , @ProcessGroup = 1;

This will insert a single row into dbo.Table1.

Once that execution is complete, the For Each Loop starts over again for the next row in the User::StoredProcedureList variable, the record for dbo.InsTable4, and the following stored procedure call pass to the database:

EXECUTE dbo.ExecuteSingleStoredProcedure @StoredProcedure = N'dbo.InsTable4' , @ProcessGroup = 1;

Once that exection is complete, the For Each Loop detects there are no more records in the User::StoredProcedureList variable, and it finishes its work, reporting Success.

To see the resulting effect on the tables, we can run the following query:

SELECT 'Table1' AS TableName, ProcessGroup, RecordCreateDate FROM dbo.Table1 UNION ALL SELECT 'Table2'AS TableName, ProcessGroup, RecordCreateDate FROM dbo.Table2 UNION ALL SELECT 'Table3'AS TableName, ProcessGroup, RecordCreateDate FROM dbo.Table3 UNION ALL SELECT 'Table4'AS TableName, ProcessGroup, RecordCreateDate FROM dbo.Table4 UNION ALL SELECT 'Table5'AS TableName, ProcessGroup, RecordCreateDate FROM dbo.Table5 UNION ALL SELECT 'Table6'AS TableName, ProcessGroup, RecordCreateDate FROM dbo.Table6 ORDER BY RecordCreateDate

This produces the results shown in Figure 11.

Figure 11


Since the stored procedures dbo.InsTable1 and dbo.InsTable4 were run serially, with dbo.InsTable1 going first, the RecordCreateDate for Table1 is earlier than that of Table4. Later, when we run all process groups, the RecordCreateDate will be the proof that the TARDIS solution works. The reason is that of the stored procedures, InsTable1 thru InsTable6, four of them have some manner of delay in their processing. For example, InsTable1 has the following definition:

CREATE PROCEDURE dbo.InsTable1 @ProcessGroup tinyint AS WAITFOR DELAY'00:00:20'; INSERT INTO dbo.Table1 (ProcessGroup) VALUES(@ProcessGroup); GO

Here I use the WAITFOR to cause execution to simply pause. In the case of InsTable1, I am pausing for 20 seconds. I took this approach just for this post in order to prove this solution works. InsTable1 will actually be the first procedure to get called. I put in the delay to make sure it finishes AFTER some procedures that are called later. With serial processing, this would be impossible. 

I mentioned that the TARDIS Child package would get the value of the User::ProcessGroup variable from the TARDIS Parent package. This is accomplished via a Parent Package Variable configuration, as shown in Figure 12.

Figure 12


When the TARDIS Parent package tells the TARDIS Child package to execute, the TARDIS Child will look at the User::ProcessGroup variable on TARDIS Parent and take that value for its own User::ProcessGroup variable. The fact that the User::ProcessGroup variable of TARDIS Parent can only have a single value (at a time) meant I needed a way for each execution of TARDIS Child to get a different value. My solution ended up being pretty timey wimey.

The TARDIS Parent Package

This is where it gets really fun. First, I will just show you the TARDIS Parent package in Figure 13. Note that the numbers in Figure 13 denote the relative order in which I will discuss the tasks, not necessarily the order of execution.

Figure 13


Step 1 merely involves truncating all of the output tables, Table1 thru Table6. Nothing to see here.

In Step 2, I use the Expression Task to set the value of the User::ProcessGroup variable to 1. The configuration of that task is shown in Figure 14.

Figure 14


There isn’t really anything fancy about this. That said, I like the Expression Task. :)

Step 3 is the first Execute Package task calling TARDIS Child. Thanks to the Parent Package Variable configuration, TARDIS Child will get the value 1 for its User::ProcessGroup variable. The only setting I made here was in setting the connection manager to the TARDIS Child.dtsx file. That connection manager is shown in Figure 15.

Figure 15


The Execute Package Tasks in Steps 3, 6, and 9 all use the same connection manager. Therefore, they all execute the same child package.

Step 4 is where the coolness comes in. When I originally started trying to solve this problem, I was attempting to come up with a way to have a single variable pass three different values to the child package. Then it hit me. There is NO reason I have to call the child package multiple times at EXACTLY the same moment. Like the TARDIS, it can exist in multiple dimensions, or time signatures, simultaneously. Therefore, all I really had to do was wait a bit, then change the value of the User::ProcessGroup variable before calling the next incarnation of the child package. Boom. This is accomplished using a Script Task. I got the code I needed for it from Mike Davis (Blog | Twitter) in this post on BIDN. The code for it is very simple, as shown in Figure 16.

Figure 16


The Step A in the script adds the System.Threading to the references. This enables the use of the System.Threading.Thread.Sleep() method, which will essentially pause for the number of milliseconds provided. In this case, I am pausing 5000 milliseconds (5 seconds).

Once the wait is over, Step 5 of the TARDIS Parent package can change the value of the User::ProcessGroup variable to 2.

Step 6 then calls the TARDIS Child package a second time. But this time, then TARDIS Child gets a value of 2 for its User::ProcessGroup variable. As of that moment, TARDIS Child now essentially exists twice in Time and Space, just slightly offset.

Steps 7 thru 9 perform this same process, waiting 10 seconds instead of 5.

The wait is the key. What is also vital is that I do NOT have any precedence constraints between the separate calls of TARDIS Child. Thus, the second execution of TARDIS Child does not wait for the first. Nor does the third execution wait for the first or second. When the Sequence Container starts executing, the steps labeled 2, 4, and 7 all start at exactly the same time. This is shown in Figure 17, which took me several tries to capture properly.

Figure 17


The Tasks shown by the arrows all start at exactly the same time. Those waits in SCR Wait 5 Seconds and SCR Wait 10 Seconds cause my three executions of TARDIS Child to happen 5 seconds apart. Figure 18 was taken later in the execution.

Figure 18


Here you can see that, although the executions for Process Group 1 and Process Group 2 started before that of Process Group 3, the parallel nature of this solution meant that Process Group 3 could still finish first as it is independent of the other Process Groups. We can confirm this by querying the tables with the query we used earlier, ordering the results by RecordCreateDate.

SELECT 'Table1' AS TableName, ProcessGroup, RecordCreateDate FROM dbo.Table1 UNION ALL SELECT 'Table2'AS TableName, ProcessGroup, RecordCreateDate FROM dbo.Table2 UNION ALL SELECT 'Table3'AS TableName, ProcessGroup, RecordCreateDate FROM dbo.Table3 UNION ALL SELECT 'Table4'AS TableName, ProcessGroup, RecordCreateDate FROM dbo.Table4 UNION ALL SELECT 'Table5'AS TableName, ProcessGroup, RecordCreateDate FROM dbo.Table5 UNION ALL SELECT 'Table6'AS TableName, ProcessGroup, RecordCreateDate FROM dbo.Table6 ORDER BY RecordCreateDate

This returns the results shown in Figure 19.

Figure 19


Note the ProcessGroup column. This proves that, although the TARDIS Child package for Process Group 3 was executed last, it was able to finish first.

There you have it. I hope this can give you some ideas if you are ever in a similar situation. Bow ties are cool.

Power Query Free Training Webinar Follow-Up Questions

17 April, 2015 (10:00) | M, Power Query, Professional Development | By: Mark V

On April 7th, I gave my Power Query: Data Chemistry for the Masses presentation as part of Pragmatic Works Free Training on the T’s series. You can find the recording of that webinar here. I want to take a moment to thank the folks at Pragmatic Works for organizing and hosting this series that is such a great asset to the community.

At the end of the session, there were more questions than we had time to handle. So, they emailed me the questions in order for me to answer them in a post like this. Behold!

What is the maximum number of rows allowed in an Excel worksheet?

This question was likely in response to my recommendation of loading Power Query results to the Excel Data Model (Power Pivot) instead of to a worksheet. According to this info from Microsoft, the maximum row count in a worksheet is 1,048,576.

When combining files in a folder can the workbook have multiple tabs?

As far as combining files using the From Folder source, I have only succeeded with text files. However, there is a post here on the Dutch Data Dude blog that explains how to combine that source with a little custom M to make this happen.

When passing a parameter to an M function, can you select from a pick list of choices?

This question was in response to my converting a power query on Nobel Prize Laureates into a function that prompts for a prize category and then returns the Laureates for that category. Typically, instead of users interacting directly with the function, the parameter is passed to the function via a custom column. There is great post here on the Data Chix blog on how to do this.

Could we get a copy of the file being used in the demo?

You can get all of the materials for the presentation, including the files used, on the page for that presentation on this very blog.

Is the option to load to data model available for Excel 2010 or later versions?

I just learned over the weekend at SQL Saturday Madison that Excel 2010 does NOT have the option to load directly to the Data Model. Bummer. So, if you are using Excel 2010, you will need to load to a worksheet and then import to Power Pivot from that worksheet.

How do you know a web page is in JSON?

Well, web pages are not really in JSON. JSON is actually a “lightweight document interchange format.” I think of it more like a simpler XML. This question comes from my use of the Nobel Prize API to download Laureate data in the JSON format as part of this presentation. This is also detailed here as part of my Power Query Decathlon – Beginner blog series. The URL for consuming the API using JSON is You can also do it as a CSV using which will actively download a CSV file with all of the same data.

What do the different separators mean? [] i guess means column, but what is {} for?

I assume this question is related to the appearance of [] and {} in M query syntax. You can read about these in the Query Formula Language Specification document. This is a large resource and not recommended for “light” reading. :) According to the specification, “We can use the lookup operator ([]) to access the fields of a record by name.” Also, the brackets [] can be used for referring to Records by surrounding the elements within the Record. the {} curly braces are used to referring to a List. A great, and easy to follow, resource is Chris Webb’s great book Power Query for Power BI and Excel. I am still very much a student of M, so I will not go much deeper into this at the moment.

Any plan from MS to add the ability to export the resulting data to a place other than XL, Data Model, eg text file, db?

I would LOVE the ability to use Power Query to load data into a database or a file, etc. Note that Power Query is used outside of Excel for loading data in the new Power BI preview as well as the Power BI Designer, which is also currently in preview. I have yet to see public information regarding any detailed plans for using Power Query to load to a larger number of targets. However, given the investment Microsoft is making in Power Query, it seems likely to me that we will see it popping up in more places in the future.

Could we use an Itunes folder as a source and consolidate music metadata?


Here is the M code for a query I used to do just that:

    Source = Folder.Files("C:\Users\Mark\Music\iTunes\iTunes Media\Music"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Folder Path"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns","C:\Users\Mark\Music\iTunes\iTunes Media\Music\","",Replacer.ReplaceText,{"Folder Path"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value","Folder Path",Splitter.SplitTextByEachDelimiter({"\"}, null, false),{"Folder Path.1", "Folder Path.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Folder Path.1", type text}, {"Folder Path.2", type text}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Changed Type","\","",Replacer.ReplaceText,{"Folder Path.2"}),
    #"Split Column by Position" = Table.SplitColumn(#"Replaced Value1","Name",Splitter.SplitTextByPositions({0, 2}, false),{"Name.1", "Name.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Name.1", Int64.Type}, {"Name.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1","Name.2",Splitter.SplitTextByEachDelimiter({"."}, null, false),{"Name.2.1", "Name.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Name.2.1", type text}, {"Name.2.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Name.1", "Track"}, {"Name.2.1", "Song"}, {"Name.2.2", "File Type"}, {"Folder Path.1", "Artist"}, {"Folder Path.2", "Album"}})
    #"Renamed Columns"

In the Code above, I highlighted the path to my Itunes folder. Just replace that with yours and past into a blank Power Query window. Here is the query once you have done so.


There is more data in the Itunes Music Library.xml file, but I haven’t figured out how to use it effectively yet.

Alright. That wraps up the questions. Thanks to all who tuned in to the webinar and provided such great questions.

2015 Microsoft Most Valuable Professional (MVP) for SQL Server

1 April, 2015 (21:52) | Professional Development | By: Mark V


I am thrilled to announce that Microsoft has once again honored me with the MVP Award for SQL Server. Last year was my first award and I have to say that I was blown away by the brilliance and altruism of so many members of the MVP community. I attended my first Global MVP Summit this past November and the experience was even more amazing than I expected. I am so proud to be counted in this fine group of professionals.

Wow. Between starting a new job on Monday and getting renewed as an MVP on Wednesday, this is turning out to be quite the week.