Author Archives

Mark

Program Manager on the Power BI Customer Advisory Team at Microsoft.

Using Live Writer on High DPI Computers

I love Live Writer for blogging. I have used it for years. I will continue to use it until they pry it from my cold, dead hands. However, since using some high DPI (that Dots Per Inch) laptops over the past several months, I have had trouble with it. On very high DPI, the scale was way off and the usable writing surface was very narrow. Figure 1 shows what Live Writer looks like for me on default settings in Windows 8.1 on a Dell XPS with 3200×1800 resolution.

Figure 1

image

Yuck. The writing surface is only 66 characters wide. That makes laying out posts very difficult since the writing surface is just a bit more than half of what I am used to and the posts will display on my blog.

Figure 2

imageI played around with some settings in Live Writer and in Windows and nothing made a difference for me. I had seen folks mention that I could “Disable display scaling on high DPI settings” for Live Writer. That setting is shown in Figure 2.

Unfortunately, that did not help me at all.

 

So, I reached out on Twitter about blogging tools people use. I also tweeted to Microsoft’s mighty Scott Hanselman (Blog|Twitter). Scott has an interesting blog post on the topic: Living a High-DPI Desktop Lifestyle Can Be Painful.

 

 

 

 

 

 

 

He asked about my settings for DPI and scaling, etc. I checked my settings, which you can see in Figure 3.

Figure 3

image

Ah. So Windows was, by default, doubling the size of all items so they would be large enough to see on that high resolution. Scott said that he used 125% here and that worked pretty well for him. I made that change and kept my 3200×1800 resolution and everything was so small on my screen that I could not function effectively. Perhaps with a giant external monitor, it would be OK. But not on my laptop screen.

I changed the Size of All Items to 125% like Scott had recommended and then lowered my resolution to 1920×1080. Aha. Figure 4 shows Live Writer under these new conditions. Much better. I also repeated the same image from Figure 1 so they can be easily compared.

Figure 4

image

image

In this perspective, it is easy to see the difference. So, the settings that work just great for me are shown in Figures 5 and 6 respectively. I just make these changes when I am going to be blogging. Not that changing the Size of All Items setting does require you to log off and then back on to take effect.

Figure 5

image

Figure 6

image

I hope this helps. I went to shout out to Scott to say thanks not only for helping out with this issue, but also for championing Live Writer as an excellent blogging tool.

Power to the Power of Power

2886083112_68c27e8a60_oI love Power tools. My father was a carpenter in his spare time and used a table saw, drill press, band saw, etc to make furniture and novelty products like a pencil holder shaped like the head of E.T. (I drew that design when I was kid). He sold quite a few of those. Times change. My power tools are quite different from his. I use Power Query, Power Pivot, Power View, and Power BI. However, the names of those tools don’t relate as directly to their function as my dad’s tools did. For most people, when you mention a table saw, they have a pretty solid idea that it is used for cutting. And a drill press, well, that puts holes in things. That’s pretty clear, too. From talking with other people in the Data world, particularly some of my friends on the DBA side of the house, it is just not clear what the Power tools are and what each one is for. So, my goal with this post is to help those folks out.

In the Excel BI stack, sometimes called the Power BI stack, we have a number of power tools:

  • Power Query
  • Power Pivot
  • Power View
  • Power Map
  • Power BI
  • Power Azure (Just kidding… This one does not exist. I just couldn’t resist.)

Let’s take a brief look at each one.

Power Query

Power Query is an add-in for Excel (both 2010 and 2013) that provides the ability to not only bring data into Excel from a number of sources, but also the ability to shape it, clean it up, and do some modeling on the way. Many people (myself included) have defined it as a Self-Service ETL (Extract, Transform, and Load) tool, a much simpler to use SQL Server Integration Services. And it is that. But it is so much more. I feel like that description just does not capture the power (can’t help it) of this fabulous tool. When all is said and done, Power Query is like a machine that takes data in on one side, does stuff to it, and spits it out the other end into Excel.

Power Pivot

Power Pivot was first released as a free add-in for Excel 2010. It was baked right into Excel 2013. Power Pivot lets you build an Analysis Services style data model IN Excel. You can augment that model with DAX (Data Analysis Expressions) to do powerful (I’m at it again, aren’t I?) calculations. It is Power Pivot that lets you drag join columns together to create relationships between tables in your model, thereby eliminating the need for doing VLOOKUP expressions. Power Pivot uses the xVelocity column-store engine to hold the data in the model you design. The data in Power Pivot is extracted from several different data sources (or sent to the model via Power Query). Power Pivot is a container that you design and fill with data. In addition to providing a means of self-service BI, Power Pivot is a fantastic tool for creating prototypes of larger BI solutions. You can put something in front of users in a few hours and make sure you are on the right track with what they are looking for. Just awesome.

Power View

Power View is a data visualization tool that is meant for end users. It is VERY simple to use. It was first release as a web part in SharePoint 2010 and later baked directly into Excel 2013. A key design tenet of Power View was “Two click to ROI.” If it takes more than two clicks to use a feature, it’s probably not worth it. This is a huge contrast from tools like Report Designer (in Business Intelligence Development Studio and SQL Server Data Tools) and Report Builder. Both of those tools have lots of menus within menus within menus. It can take a dozen clicks or more to do one thing. These tools provide much more functionality than Power View, but that functionality adds complexity. Power View is designed to be simple. A key feature of Power View is the cross filtering that happens automatically. Sometimes Power View gets a bad wrap because it is not as robust are Reporting Services; but it is not meant to be. It was not meant to replace (and does not replace) existing tools like Reporting Services. It complements them and is a great tool when used appropriately.

Power Map

Power Map is a tool for creating visualizations of geo-spatial data. While Power View supports creating maps, Power Map supports creating 3-dimensional maps. You can record the use of your maps as short videos called Scenes and play them back. While Power Map looks really cool and provides great eye candy, I must confess I have not played with it all that much.

Power BI

Power BI is an add-on service for Office 365. It provides a specially configured SharePoint Online site that enables some very exciting functionality. Once you have created a data model in Excel with Power Pivot and optionally created Power View reports against it, you can upload that file to a Power BI site to share it with others within and outside of your organization. An exciting feature of Power BI is called Q & A. It lets you use natural language to query your data and get results formatted in a appropriate visualization. If you have sales data up in Power BI and ask for sales over time, if will put your results into a line chart in order to highlight the trend you are likely seeking. The licensing of Power BI is currently very confusing. I can assure you that Microsoft has heard the feedback on this.

So, to sum up VERY simply:

Power Query extracts, transforms, and loads data into Excel.

Power Pivot models and stores data in Excel.

Power View and Power Map visualize data in Excel (Power View is available in SharePoint as well, though).

Power BI shares what you created with the tools above and adds other cool functionality, too.

I hope this helps clarify things a bit. My goal is to do a blog series on each of these tools. I never thought I would say this, but, any day I get to work with data in Excel is a good day for me. These tools provide so much capability for helping people make better use of what they already have. These tools certainly don’t replace the Enterprise BI tools like SQL Server Analysis services. We will need that for quite some time to come. But they do offer an option for enabling better decisions with a much lower barrier to entry than learning to develop cubes and write MDX. Microsoft Senior Technical Fellow Amir Netz has said many times, “BI doesn’t just have to mean Business Intelligence, it can also mean Basic Intelligence.” So much of BI is about making things simpler. These tools do that and more.

PASS Summit 2014 PASS Board Q and A

At PASS Summit 2014 in Seattle, I attended a couple Q and A sessions with the PASS Board of Directors members. There were three such sessions. I missed the general Q and A as my Summit presentation was at that same time. Rather than try to capture the play by play of everything, I decided to distil all my notes into a single post that is more of an article. I have not really tried this type of journalistic article before, so I hope you will bear with me.

For the past two years, the PASS Board election process has been somewhat less than smooth from a Community perspective. Last year, there was uproar over the fact that people with multiple sqlpass.org accounts could vote from each account, thereby allowing them to vote multiple times. To try to rectify that situation prior to the 2014 election, PASS instituted a policy that required people to update their PASS profile in order to be eligible to vote. The plan was to also look for duplication in the accounts and have people with multiple accounts choose a single one to serve as their account. As listed in this post by PASS President Thomas LaRock (Blog|Twitter) on the PASS blog, PASS communicated this requirement many times on many different channels. Despite this, there were many members that did not receive the message and/or take necessary action to ensure their eligibility to vote in this year’s election. Once again, there was a massive outcry against PASS over this situation. When asked about this situation, PASS Executive Vice President, Finance and Governance, Adam Jorgensen (Blog|Twitter) replied, “A significant amount of work went into the communication plan on this. People didn’t get an email, but email was not the only thing.” Given the fact that PASS tried so many avenues of communication, Adam wants to ask PASS members the following: “What is the BEST way to make sure, when we communicate with folks, that they read it AND take action? What is the best vehicle? What channels are most effective for them?”

All of this outcry started once the election began and people discovered they were not eligible to vote. There was an understandable amount of frustration for people whose reactions suggested they felt they were being disenfranchised. The PASS Board responded to this outcry by extending the election to allow people more time to update their profiles. I asked what went into making that change. Adam explained that the PASS organization is governed by Bylaws as well as the laws of the state of Illinois. As such, it is not going to be able to turn on a dime. According to Adam, “We had to decide if wanted (and if so how) to change things and extend the election. This required 12 people in a very short time. HQ did a great job to help coordinate that. We had to talk to our legal counsel to assess the ramifications of changing things midstream.” Denise McInerney (Blog|Twitter), PASS Vice President, Marketing, added to this, “HQ did an amazing amount of work during that election change. A lot of credit goes to HQ for enabling us to do this as quickly as we did.” Adam went on to describe some of the thinking that was involved in this process. “Is it good governance or bad governance to extend or change the election in the middle? LOTS of conversation on that.” In the end, the Board made a conclusion that I agree with: “[It is] bad governance to change the election, BUT governance isn’t just about trying to err on the side of good or bad governance, but also about keeping a community what we want it to be.” The Board decided it was more important to keep the community whole than to perfectly adhere to policies they had enacted.

Another topic that has ben on people’s minds is the decision to commit to continuing to develop the Business Analytics (BA) Conference. At the Blogger Q and A, Thomas LeBlanc (Blog|Twitter) asked why this investment was going to continue given the challenges it has faced. Thomas LaRock gave the first response. “Three years ago, Microsoft came to us and said, ‘You guys have built something incredible. Can you go find the Business Analytics folks?” Basically, PASS has helped to build and foster an amazing community around Microsoft data technologies in terms of those who develop tools, and provided the services for others to do so. Microsoft asked PASS to try to do the same thing for the people who consume those services and that data. In the age of Self Service and tools like Power Pivot, there is certainly some overlap in terms of people enable analysis of data and those of perform that analysis. But largely, the audience for the BA Conference is one that PASS has not really targeted or served before.

Regarding the fact that the BA Conference has not really found its footing yet, Denise contributed, “You learn as you go. We didn’t quite hit the mark on getting the program and audience matched up. We tried to be too many things to too many people.” She added that PASS does Community very well. I have to agree on that. And that Community really helps people develop and learn. Attendees at PASS events often have great experiences of learning something cool and saying, “I’m going to try this at work on Monday.” According to Denise, “We want to create THAT experience for the business data user.”

Adam acknowledged that there have been questions around why PASS didn’t just do a BI (Business Intelligence) conference. “It’s not about BI. And if you do a BI conference, where does half of Summit go?” He then added what I feel is a great point, “We don’t want to split audiences up; we want to bring them together.” This is key as it reinforces that understanding that many of us have that the audience for the BA Conference is not really a subset of the existing PASS Summit audience; it is a different group of people that PASS has not served before. As Adam explained, “Are we taking something away? We’re not. We are building something new that is additive.”

Regarding BA Conference location, the first was in Chicago. Last year was held in San Jose, CA. Next year will be in Santa Clara CA. 2016 is back in San Jose. Thomas LeBlanc asked why the BA Conference is not as mobile as Summit. According to Denise, “Silicon Valley was strategic. Silicon Valley is on the leading edge with what is happening with Analytics. By locating there, we thought we would have access to a pool of speakers that would be local to the conference.” The idea is that this would make it easier to get speakers. Locating in Silicon Valley is not just about speakers, though. Denise continued, “Lots of the target audience lives there. Exhibitors would be nearby and it would be easy to get them.”

The topic of Speakers brought forth the topic of the decision NOT to have a Community Call For Speakers for the BA Conference this year. Instead, the speakers will be invited only. For many, this is seen as a problem. I, myself, being a speaker who has content appropriate for business users, was disappointed by this decision at first. But the more I thought about it, the more it made sense to me. In the existing PASS Community, we have a lot of presenters with content around BI. But we have very few with analytics topics. My own content around showing people how to use Power Pivot is still BI, even if it is aimed at the business user. It is still about enabling analytics as opposed to performing analytics. Adam pointed out that when Summit first started, there was not Community Call For Speakers. According to Adam, “We have looked at the [evaluation] results over the past few years. People liked it but the program was mis-targeted to them.” Some have asked why the board did not choose to do a community call for a percentage of the sessions. Adam indicated that the board discussed that and arrived at this though: “What percentage is acceptable to be NOT OK with attendees? That would be zero.” I have to agree with Adam, here. That is also why I totally understand why I did not receive an invitation to speak. I have not demonstrated that I have content appropriate to an Analytics audience.

At this point it is appropriate to bring in some comments from the separate Q and A about the BA Conference. Jen Stirrup (Blog|Twitter), Director-at-Large, Virtual Chapters, gave further explanation behind the decision to keep forging ahead. “We did a lot of research with the BA Conference. People are really excited in Microsoft products, so that will continue to be a base. But, Tableau, for example, has its own user conference. We don’t want to just create another Tableau conference. I have spoken at those. They were much more sales driven and about user stories. With PASS, the main emphasis is on practicality. Learn on one day and apply it on the next.” She then put a key difference between BI and Analytics that I had not heard before: “BI projects focus on deliverables. BA stuff focus on business value.” That makes sense to me. With BI, we work to produce some object, whether it be a data model, cube, report, dashboard… As Jen added about Business Analytics, “[it] is less time-boxed and more value oriented.”

Amy Lewis (Blog|Twitter), Director-at-Large, PASS Programs, made a fine point here as well: “One thing we did poorly before was focusing on the tools. We saw we need to stop focusing on the architect perspective and more on the data analyst standpoint.” When asked about the personas present in the intended audience, Denise added, “We have come to understand it is not about job title. It was about what they do all day. What they have in common is Find Data, Make Sense, Produce Something With Impact.” Denise went on to point out that there were marketing issues in the past. “As far as Marketing, last year people thought it was a SQL conference. It has its own site, now. All the messaging is consistent now to clarify that.” Head on over to the new website to learn more about the new and improved messaging.

I want to close with a direct request from the Board. Adam pointed out that the PASS Board are fairly reachable people and are happy to respond to questions they receive. He stressed, “I would request that if you want to know the answer to a question, ASK THE QUESTION. We are happy to have conversation, but the judge, jury, and executioner style is just NOT productive.” Adam gave a great example of a blogger who wanted some facts for a blog post and ended up having a great conference call with several board members to get the information he needed. Tom then added, “I have really enjoyed this blogger Q and A at Summit. We should find a way to do this more.” We discussed the idea of doing this quarterly or something and in a format like #DataChat on Twitter happens now. That sounds like a good plan and a good way to help people who may feel they are not being heard to have another avenue to speak out.

I hope you found this helpful. I certainly feel better about these topics having attended the Q and A sessions and put in the work to write this up.

My Remaining 2014 Presentation Schedule

Greetings. Since I do a fair amount of presenting, I like to keep people up to date on when and where I will present. Below are the presentations I have left in 2014.

SQL Saturday Winnipeg – November 22

In addition to serving on the committee that is organizing Winnipeg’s first SQL Saturday, I will be giving two presentations:

Analysis Services Terms and Concepts for the DBA

MDX Trek: First Contact

Minnesota BI User Group – December 2

I will be presenting a Case Study along with someone I worked with at a client. This is a make-up for September when a back spasm prevented me from delivering this presentation.

Case Study: Monitoring SSAS Usage with Extended Events

How do you measure the success of a Self Service Business Intelligence initiative? While the answer to that question has several components, a vital one is Adoption. Are people using the assets created for that initiative? Since Business Intelligence is using data to make better decisions, why not use data to help answer this question? At Capella University, we did just that.

This case study will describe how we used Analysis Services Extended Events to track the usage of Tabular Models that Capella built for Self Service BI users. We will demonstrate the following:

The business case for capturing asset usage

The particular goals Capella had in this case

How easy it is to get started with SSAS Extended Events (far easier than it looks)

The end-to-end solution created: from the event trace itself thru to the Power Pivot model created for analysis

Lessons learned (some learned the hard way)

We hope you will come away from this presentation with less trepidation around SSAS Extended Events as well as a foundation you can use for your own solution.

Minnesota SQL Server User Group (PASSMN) December Meeting – December 16

I will be participating in an Improv-style group presentation based on the format of the TV Show Whose Line is it Anyway? with other speakers from the Twin Cities.

 

Pragmatic Works Free Training Series – December 18

 

Analysis Services Terms and Concepts for the DBA

This is a make-up for September when a back spasm prevented me from delivering this presentation.

 

So, that’s it for the rest of 2014. Overall, it has been a pretty busy year as far as presenting goes. I hope to continue that in 2015.

PASS Voting Extension

Greetings. PASS announced over the weekend that they were extending the deadline for updating your PASS Profile in order to be eligible to vote in the current Board of Directors Election. See the announcement from PASS President Thomas LaRock.

It is important that we all have our chance to cast our vote for the open Board positions. In case people are having issues receiving emails about this from PASS, I have pasted the steps present in the email I received. Please take a moment to make sure your voice is heard! #WeArePASS

 

Extension to Voting Period for 2014 PASS Board of Directors Elections

To address issues around voting eligibility for the current PASS Board of Director elections, the PASS Board voted today to extend this year’s registration and voting period to ensure that as many members as possible have the ability to cast their ballot. Here are the key dates in the extension:

October 5: All PASS members as of June 1, 2014, who have not already registered to vote by updating their myProfile on myPASS will now have until October 5 to update their profile.

October 6: We will pull an updated list of registered voters and filter for duplicates.

October 7: We will send all newly registered voters an email with their ballot link.

October 14: All voting will close at noon PDT.

How does this decision affect you?

If you already voted

You don’t need to do anything. All votes currently cast will remain valid, there will be no need to recast any votes.

If you already received a ballot but haven’t voted yet

You can cast your vote any time until the deadline on October 14 at noon PDT.

If you established your PASS profile after June 1, 2014

You are not eligible to vote in the current election, but you are already eligible to vote in next year’s elections if you have an up-to-date profile.

If you had a profile prior to June 1, 2014, but have not yet updated your profile

Please follow these steps in order to register and received a ballot to vote in this year’s elections:

Step 1: Update your PASS membership profile by October 5, 2014, 11:59pm PDT.
New mandatory fields were added in January, 2014. Please log in to confirm your PASS profile information is correct and fill out the new mandatory fields, including: Job Function, Industry, Country, Region, City, Zip Code/Postal Code, and Time Zone.

Step 2: Click ‘Save’ and make sure you see the words ‘Profile Updated and Voting Eligibility Recorded’ appear underneath the ‘Save’ button.
If you update your profile by October 5, 2014, you will receive a ballot on October 7, 2014.

Thanks for your patience and feedback as we work through these voting changes, and we apologize for any inconvenience.

To help PASS communicate important news and reminders, and in order to receive future voting ballots, please take the following steps to ensure our messages can reach you:

  • Ensure the PASS IP address is whitelisted on your company or email address provider’s servers: 68.232.200.32.
  • Add hq@sqlpass.org to your safe sender list and as a contact.
  • Check spam folders regularly for important messages.
  • If you believe that you are not receiving PASS emails, please contact helpdesk@sqlpass.org to let us know!

We Are PASS

If you have been following the recent difficulties around the PASS election of Board Members, you will no doubt have noticed a climate of negativity and sometimes bashing. This has made me really sad to see in a community that I care a lot about. Rather than provide yet another blog post that goes into the details, I instead want to say Thank You to a lot of people.

Thank You to the people that have been raising issues and offering ideas. While I may not agree with the way in which these issues were raised, or the tone present, there were some good points made. You Are PASS.

Thank You to the PASS Board of Directors that worked through/around so much negativity to keep the Community in mind while striving to implement solutions. You took a lot of heat and, as far as what I saw, you handled yourselves with professionalism and patience. It was clear to me during this entire process that you care deeply about the PASS community and work hard to make it as strong as possible. You Are PASS.

Thank You to the people who have been understanding of the difficulties and stayed positive about the willingness of PASS BOD to work through these difficulties. You Are PASS.

PASS has been a huge asset to me in my career in working with SQL Server and related technologies. I feel that I owe a lot to the existence of this organization and the people within it who work so hard to keep it strong and help it grow. That includes the PASS members throughout the world. Thank you. You Are PASS.

My great hope with these recent events around the election and the events surrounding the PASS Summit selection process is that we, as a community, will learn something. The key idea that I think has been lacking on the part of some folk within the community is that everyone involved here, from the BOD to the various committees, to the PASS members ALL want this community to thrive. I would ask that, in the future, those of us who want to raise issues, those who want to make sure their voice is heard, etc, will base how we raise those issues by keeping one, very important idea in mind as we do so: WE ARE PASS.

Show Me The Data

ShowMeTheDataMEME

In the 1996 film, Jerry Maguire, sports agent Jerry, portrayed by Tom Cruise, is given a very clear demand by his sole remaining client, portrayed by Cuba Gooding Jr. (who won the Oscar for Best Supporting Actor for this role): SHOW ME THE MONEY. It does not take much time or effort to see that this is the essence of what businesses must do to survive. If they cannot show the money in some way, they will go away. The culture we have today, at least here in the United States, is very much driven by money: on Wall Street, on Main Street, in Politics, in the Legal system to an extent, in Journalism to an extent… That SHOW ME THE MONEY idea is everywhere. But we have been seeing a bit of a shift in HOW that money gets shown. It is not just about selling more widgets or getting higher ratings anymore. More and more organizations are realizing the untapped potential that is their DATA. Some have understood this for years. Facebook, for example, is in essence a data aggregator and reseller, using what we share, and the metadata around what we share, as a PRODUCT to sell. Have no illusions, my friends, we are NOT Facebook’s customers, we are the producers of their product: data. That data has been and will continue to be crucial to the strategic decisions of Facebook. They are not alone in relying heavily on data.

AssumptionsMEMEThink about how Netflix transformed how we watch movies (and TV). They rely immensely on data to drive their entire business model. They use data to drive how movies are displayed to their users. They don’t leave that up to people sitting in a board room playing politics over their own ideas. It is driven by data flowing through algorithms. And it WORKS. The rise of Netflix and the demise of Blockbuster can attest to that. Netflix prevailed, in my opinion, largely because they focused on staying ahead of their customers instead of trying to keep up with their competitors. How many companies spend so much time trying not to fall too far behind their competitors? Organizations that do this are, unconsciously, working to achieve mediocrity.

How often do people make decisions about their Business based on information they believe to be true? How often do we, as consumers, make purchases based on believing information provided to us by manufacturers or advertisers? On the whole, as consumers, we tend not use data all that well. Shouldn’t we demand more? Shouldn’t we demand to the people trying to sell us their products and their ideas: SHOW ME THE DATA? We can. We just have to choose to do so. And as business leaders, BetterDecisionsMEMEwe need to rely less on that gut feel and intuition and more on the reality that is before us. We, as global citizens today, need to stop looking to the Media or the Internet for information that makes us feel good about the assumptions we have already made and like. We need to, instead, seek information on the reality of our situation. That reality, for organizations, is often sitting in storage arrays and hard drives as flat files and databases. In some cases, showing the money even just boils down to putting your data to work. I have worked on projects for clients that held large amounts of data on their clients in their industry. The solutions I helped create allowed these organizations to provide their clients’ own data back to them, along with helpful analysis, as a premium service. These organizations turned their DATA into a revenue stream. They stopped just storing their data and started using it.

There is a presentation I have given before titled Keeping the Business in Business Intelligence. In that presentation, I boil down what Business Intelligence is in four simple, yet impactful words: BETTER DECISIONS THROUGH EVIDENCE. When all is said and done, the real point behind any data-driven application or solution, particularly BI, is to make better decisions. As an example, organizations measure performance in order to DECIDE what activities to stop, continue, start, increase, decrease, etc. The word EVIDENCE there is key. Making better decisions is not just about Data. It is about the RIGHT data. It is about accurate, timely data. It is about data that has been deemed trustworthy. It is not really about getting more data, either. To me, the promise of Big Data is not that it lets us use ever larger, more diverse sources of data AllTheDatasMEMEas a whole, but rather that those technologies help us comb through every more vast sets of data to find the bits that we need. It seems like a subtle difference, but I think it is an important one.

As the Subject Matter Expert in the development of the new BI offering we are rolling out at Digineer, the consulting firm I work for and adore, I was able to fold these ideas and philosophy into the foundation of our point of view on BI. I am proud of the way I have been able to drive the overall story. I will be co-presenting with a colleague on Wednesday, August 27 at 11am CDT on the concepts laid out here and more examples from the real world (some Digineer clients, some not). The presentation will focus on how organizations like Netflix have been able to use data effectively in driving their strategy. It will NOT be a big long sales pitch. It will be very much about the concepts above and hopefully inspire people to make better use of an asset that has huge potential be transformative for their organizations. If you would like to join in the discussion, I would encourage you to follow the link, Imagine What You Can Do With Data, and register. I am confident you will take away some valuable ideas on how you can prepare yourself for the next time someone makes this demand of you: SHOW ME THE DATA.

My First Pre-Con: SQL Saturday 332–Minnesota

It is with tremendous joy (and a little trepidation) that I announce that I will be doing my very first Pre-Con as part of SQL Saturday 332 in Minnesota in October. I have been presenting for several years now and feel that it is time to take this next big step. Since I love presenting so much, the idea of presenting for a whole day is just awesome. There is a also quite a bit more pressure in this scenario. But that is part of what makes this a great growth experience, stretching myself like I have never done before.

Over the past several months, I have done a lot of client work in Excel dealing with Power Pivot and Pivot Tables. I have also been doing a bit using Power View up in SharePoint, the vast majority of which carries to Excel 2013 as well. I have always been a fan of enabling users to do more with data and learn to be more self-sufficient. My experiences in Excel have reinforced the idea that Excel is a fantastic platform in the Self-Service BI movement. The past several months working with data in Excel have been some of the most fun in my career. So, when I needed a topic for a BI Pre-Con, the choice was easy.

You can find information on all the SQL Saturday 332 Minnesota Pre-cons here. It is an impressive line-up, to be sure. The abstract for mine is below.

Microsoft Excel: The Business Intelligence Platform For The Masses

From gathering and shaping source data through data modeling and visualizations, it is staggering how much you can accomplish in Excel. This Pre-Con will walk you through creating an interesting and powerful BI solution in Microsoft Excel 2013. Whether you are a business user or a technical developer, you will get good value from attending.

1. Power Query 

• Using Power Query to gather source data from various sources both on-premise and in the cloud. 

• Use various transformations on the Ribbon

• Travel back and forth through time via Query Steps

• The basics of Power Query Formula Language (M)

2. Modeling Data With Power Pivot

• Importing Data from various sources

• Linking Tables to data residing directly in Excel sheets

• The importance of Date Tables

• Best Practices

3. DAX 101

• Introduce DAX syntax beginning with Calculated Columns including the mighty Related function

• The basics of the Calculated Fields (Measures)

• Row context/filter context

• The power of the CALCULATE function

• More…

4. Power Pivot/DAX Design Patterns

• Solving real-word problems with Power Pivot

• Many to Many relationships

• Parent-Child Hierarchies

• Segmentation

• More…

5. Excel Pivot Tables/Charts

• Connecting Excel to data sources like SSAS Cubes, Tabular Models, and the internal Power Pivot model

• Pivot Table basics

• Filtering methods and Slicers

• Conditional formatting

• Pivot Charts

• More…

6. Power View

• The basic visualizations (Bars, Columns, Matrix, etc)

• Advanced visualizations (Multiples, Cards, Scatter/Bubble Charts, etc

• Filtering views or the entire report

• Design tips to take great advantage of Power View’s capabilities

 

I will be focusing on using Excel 2013 on my machine. There is so much to cover that trying to add in Power BI-specifics is just not in the cards. But I think that makes sense as SO many more people have Excel on their machines than are using Power BI right now. And SO few of those people are taking advantage of even a tiny subset of what Excel has to offer. A major goal of this Pre-Con is to help change that.