Articles for the Month of December 2014

Dec 31 2014

Power BI’s First Year in Review

Author: Ginger Grant • Discussion: No Comments

Since 2014 was the year Power BI was released, a lot of changes have been made, that I thought it might be interesting to review. For all who saw this headline and thought, Well technically Power BI is not really a year old because it was released in February, allow me some creative license to perform some rounding. Listing the number of changes released points to the beauty of the iterative release schedule which allow changes to be rolled out whenever Microsoft is done with them, rather than at fixed intervals, which means changes happen often. There were more updates made than I listed here, but space and TL:DR limited me, and so only selected items are reviewed.

Timeline

January 6th – Microsoft releases pricing info for Power BI.  This is one of the final steps taken before the product was released

February 10th – Power BI is released. Since Power BI was revealed and available for preview on July 8, 2013, the release wasn’t a surprise.  Having a released product meant that the previews people had been playing with for free for a while would be going away soon.

March 3rd – There were a lot of little updates made in this release.  The one that I thought was had the most impact were the changes made to HTML5 rendering, which got a lot better.  Q&A features were also notably improved. Microsoft touted the new connectivity to Business Objects, for those people who wanted it, that is probably quite useful.

April 1st –SQL Server 2014 is released.  I realize that this isn’t directly related to Power BI, but the products are designed to work together, which is why I selected to include it.

April 18th – Microsoft announced that they were not going to remove Power Map from Excel in Office 365. In January, when Microsoft announced  that Power BI was going to be released, they also made this announcement “On May 30, 2014 if you have the Power Map Preview installed, it will no longer work in any non-Office 365 subscription version of Excel”.  As you might guess, this announcement made people unhappy, and they complained to Microsoft. These complaints were heard and the policy was changed.  There is a perception sometimes that when you complain, no one hears you.  At least this one time, complaints were heard and changes made.  Here’s the caveat for Power Map for Excel users, you don’t get any of the new Power Map upgrades. The cool features added later can only be used by Power BI users, like the new feature of the ability adding sound, a new Power Map feature in this release.

May 8th – Forecasting in PowerView was demo’d at the PASS Business Analytics Conference.  Forecasting added the Predictive Analytics to Power BI.  What was nice about the new algorithm, not data mining, is that it also included Hindcasting to validate the predictions.  More information on this feature can be found here.

May 29th  – The Power Query Updates here made it easier to Query information from the Azure Marketplace as well as remember what you queried recently, which was most helpful. Q&A was also updated to have a wider number of natural language queries too.

August 19th – Skipping over the summer release stuff, the data source changes included in this release were really cool.  Now you can schedule a refreshes from a number new sources.

September 9th – Custom maps were added in this update.  Now instead of maps being limited to the world, you can map inside of buildings.  I know of a casino operation that really wanted this earlier.  This is another cool feature not available to Office 365 excel users.

September 30th – The Power Query update can be summarized as Seeking and Spamming. Finally Microsoft made it possible to not get updates when you didn’t want them.  The data source settings can now be sorted and searched, which is really helpful when you have a lot of sources.

October 13th  – Microsoft is making nice with the juggernaut SalesForce by adding Power Query support for data within it  Since it seems SalesForce is everywhere, being able to query the data will definitely be handy.

November 13th – Updates to Power Map mean that you can now play individual scenes instead of playing through the entire tour. This is a most helpful feature when editing a PowerMap.

December 19th – New Dashboards and Native PowerBI for the iPad.  Microsoft now has an Apple app so that you can see your data visualizations on Apple, just in case you wanted to do that.  While still in their infancy, the new dashboards are the first break PowerBI is really making from Excel as you don’t need it anymore to create visualizations.  I wrote about that new feature here.

New Year, New Updates

Looking back at everything that has happened this year, I can only imaging what next year will bring.  Given the current preview release status of the new dashboarding feature, it would be logical to assume that more changes will be added to it.  To be sure, when new things are added to PowerBI, you will be able to find more about them here.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Dec 30 2014

Predictive Analytics in Power BI

Author: Ginger Grant • Discussion: No Comments

If you have ever heard a commercial for an investment product, you will hear the disclaimer “Past performance is not indicative of future response”.  That reminds me of a movie quote — You keep using that word. I do not think it means what you think it means. I disagree with the investment advisers. The past is a guide to the future as things tend to repeat in cycles, which is why when economic numbers are reported they are seasonally adjusted. For example Amazon hires people every year to help with the increases in sales in December. Fortunately there is a tool for that, in Power BI

Divining the Future

One manner which statisticians derive repeating seasonal patterns, or as statisticians prefer to call it periodicity, is via Exponential Moving Averages. I won’t bore you with the details, but this method is rather good at determining the future based upon the past, which is why Microsoft picked it as the method it is using in Power BI. There are of course a couple of caveats to keep in mind if you want to use Forecasting in Power BI. The first caveat is the visualizations only work on line charts. The second restriction is this feature works in the online version of Power View only, which means you can’t do this without having a Power BI license. While Predictive Analytics are often computed using Excel, this book will tell you how, the Power View feature I am referring to is called Forecasting, which is validated by the Hindcasting feature and needs HTML 5 rendering, which is only available online. Yes you can also use the App Microsoft has provided to view HTML 5, but you have to get that from the App Store which is still, on the internet, not within Excel.

Getting Forecasting to Work

The trickiest thing about getting the ability to see the Forecasting option is you need to have the date field, which more than likely will be on the X-Axis, formatted as a date the tool wants to use. This means you have to actually have a valid date. 201412, 201411, 201410… will not work as they are not dates. You will need instead to format these values as 12/1/2014, 11/1/2014, 10/1/2014 … or the forecasting feature will not be available. YYMMDD won’t work either. Unless your dates are formatted in this way the forecasting feature will not appear. Since this is a Power View feature, you may have to write some DAX code to format the data in this manner, or modify the data source.

Validation

To determine whether you thing the statistical model is valid, Hindcasting allows you to apply the same model to the past. Since you know what happened previously applying the same model to project the future can be overlaid on the data which really did occur in the past to determine how close the predictions applied to the future apply to the past. It has been said that Hindsight is the only accurate prediction, and overlaying the model will allow you to see the theory compared to the actual.

Putting it all Together

Using the statistical models available to predict the future can be a great tool, provided that you have the requisite tools and data formats necessary to accomplish this task. Having reviewed the details needed to get started, in a later post I will provide the DIY steps need to get started with Forecasting in Power BI.

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Dec 28 2014

Upcoming and Up and Coming Topics

Author: Ginger Grant • Discussion: No Comments

It’s funny the different meanings words have when you put them in different order, a point which anyone who has imitated the dialectic of Yoda can tell you. I find words fascinating as they are not static but have meanings which change over time. For example the Iron Maiden meant something totally different before there were electric guitars. Thinking of works and things changing, as one year closes and another year begins, I start to evaluate past and future topics. Earlier this year, I held an informal poll on twitter to find out how long people tend to talk on the same topic. The answers were quite varied. Some people keep on talking about the same topic as long as there seems to be interest in hearing about it. That way you can get to be a really good speaker on that topic. Another feels obligated to create a new topic each time out to provide him a challenge. The answer that personally I related to, was keep on talking about the topic until you are tired of hearing about it, which takes about a year.

SQL Saturday Albuquerque

sqlsat358_ABQMy first upcoming engagement for 2015 will be as SQL Server Albuquerque where I will be talking about SSIS. I generally talk about things I am interested in or presently working on, and having working on a lot of ETL recently, I thought that it would be an interesting topic which I think most people would find helpful. As a consultant, I see a lot of code and wonder why parts of it were written that way. One big reason is someone thought the design was a good one. Since that is an objective decision, I thought it might be helpful to clarify design decisions with facts so that that people would be able to employ good logic for their design decisions.

Technology changes and their Impact on Data Development

Another topic which really interests me is the changes that new technologies are having on the database world. With the increased implementation of Hadoop and cloud things are really changing in the way data is being both stored and used. Predictive Analytics, Machine Learning, Cloud implementations, Interactive Data visualizations are changing what people are expecting from the way their data is stored and used. Expectations for data professionals are increasing as the business is looking away from HIPPO and towards the knowledge that they have gathered or integrated data from public sources.

Modern Data Warehouse

I have the pleasure of assisting in a day-long session to talk about Architecting the Modern Data Warehouse . During this one day session we will be showing how to use new technology such as HD Insight and Machine Learning to implement a modern data warehouse. Instead of just talking about new technologies we will be putting them to use to show how they can be used today. I’m really looking forward to it.

If you are able to attend any of these or any upcoming sessions, please stop by and introduce yourself as I would love to meet readers of my blog in person.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Dec 26 2014

Creating Couture Dashboards with Power BI Designer

Author: Ginger Grant • Discussion: No Comments

On December 18, 2014 Microsoft release the preview of PowerBI Dashboards, as long as you live in the US. Since Power BI has an iterative deployment cycle, releases have been rapid and varying in detail.  This release is huge as takes a further step from separating Power BI from Excel.  With this release, creating data visualizations moves to a new tool, the Power BI Designer.  When this preview is fully released so countries outside of the US have the ability to play with it, Excel is no longer necessary. Of course the option to use Excel is still there, but creating visualizations, be they dashboards or not, can be done on the web with Power BI Designer.

Dashboards, the New Old feature

As anyone who has reviewed the marketing information from Microsoft knows, dashboards are not new in Power BI.  The ability to put multiple data representations on one report has been available since the product was first previewed.  The difference is Excel is no longer required.  Since the Power BI was released, it appeared to be just an expansion of Excel’s feature set.  The one power tool which was supposed to be only available to Power BI subscribers, Power Map, was released to existing Office 365 users after the preview version expired. It’s not exactly the same version of  PowerMap, as only Power BI users get updates but Excel users can still use the preview version. Power Query, Power Pivot and Power View have been included in Excel’s features for a while, which left many users thinking there was no reason to pay for Power BI, unless you wanted to distribute data visualizations in a Mobile Environment.  Being able to design reports completely in the cloud provides a more compelling reason to sign up for Power BI.

DIY – Implementing Dashboards on an Existing Power BI Account

The new dashboarding feature is still in preview, so I assume that a lot of the access methods for it are going to change, so keep in mind that these instructions may not work in the next release, which could be soon. Here’s what you see when logged into your PowerBI account.

PowerBIDashboard

The first time you click the try it now link, Microsoft will update your account, which means that you will need to wait until it loads to use it.  After they are done, this link will work to load the Power BI Dashboards.  Keep in mind since this is a US only preview, the new dashboards are not fully integrated into PowerBI, as the visualizations there don’t show up in your favorites, but it is logical to assume that it is coming.  Clicking on the Try It Now button brings up this screen.

PreviewDashboards

Designing Dashboards is meant to be simple, so the steps involved to create them are few.  First you need to select a dataset, on my screen you will see that I have picked an Excel spreadsheet called TabularReports.  Then you select the data that you wish to display, and Microsoft picks a display format, which you can change to any of the standard visualization types, like the bar chart I have here.  The reports you wish to see here are pinned.  If you don’t pin them they lurk inn the background. Save this and you are done.

This first version away from Excel offers a lot of interesting features and definitely moves Microsoft closer to the features offered by their competitors.  Dashboard also provides a real reason to use Power BI instead of Excel, a topic I will definitely be exploring more in the future.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Dec 23 2014

When the Process matters nearly as much as Results

Author: Ginger Grant • Discussion: No Comments

There are times when the results aren’t nearly as important as the process of doing as people learn things from figuring out what not to do. This applies if you are troubleshooting why your project isn’t working right or when your database crashes in the middle of the night. Knowing what not to waste your time with is the key to getting things back up and running. It’s the knowledge that you have gained doing the little things that add up to being the person who can fix things quickly. Often times, the little things add up to something big. It appears this has happened to me.

Finalist in Tribal Awards

I was most honored to be included in SQLServerCentral/Simple Talk Tribal Awards Finalist in the Best New Community Voice category. To be recognized among all the other people who speak and write about SQL Server is quite an honor. The only way that this could have happened is a myriad of people that I’ve met while speaking at various community events and reading this blog remembered my name when looking at a blank line on the Tribal Award. I was so surprised when I saw that my name was listed on the award I nearly fell out of my chair. To everyone who thought enough of me to enter my name in the Best New Community Voice thank you. I really appreciate it.

Nomination and Winning

Regardless of the outcome, especially given the other nominees in the category, I feel that I already won.  Being a finalist is a real honor. After all there are so many other people who are also really involved in the community. I’ve been fortunate to visit with many of you who I’ve met either online or at various events. Thank you so much for to the people who thought of me, as I really appreciate your kind thoughts.  This process mattered as much as the result, what ever it turns out to be.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Dec 17 2014

Presentation Follow up to Data Quality Issues? There is a Service for that

Author: Ginger Grant • Discussion: No Comments

PASSBIThank you to all of you who were able to attend my latest presentation. I received a number of great questions during the presentation and wanted to make sure that I was able to provide answers. I also wanted to say thank you for the people who provided feedback in the question and answer section as that was very thoughtful and most appreciated.

Questions and Answers

I assume DQS is included with the existing SQL Server license? Is DQS included with enterprise edition? Can you provide some idea of what the licensing costs are? What are the licensing cost for Master Data Services?

Because I don’t work for Microsoft, I hesitate to answer any but the most basic questions regarding licensing, but I can say DQS and MDS are included in the licensing costs for SQL Server. Neither are available in the standard edition either. For more information about what is included, see here http://technet.microsoft.com/en-us/library/cc645993#Other_Components or contact Microsoft directly.

Do you know of any testing of scalability that would provide some insight into the scalability of DQS?

The best place I can point you to for more information on DQS scalability is here http://www.microsoft.com/en-us/download/details.aspx?id=29075

It seems that DQS appears similar to Fuzzy Lookups and Fuzzy Grouping. Is that pretty much what DQS is using?

While the logic employed in the SSIS components Fuzzy Lookup and Fuzzy Grouping are similar, but not functionally equivalent. DQS uses logic much closer to that of Fuzzy Grouping, as both apply a rules to a set of data and set similarity thresholds to determine success. Where they differ is in DQS’ use and development of the knowledge base to continually improve the accuracy.

How do you see DQS and MDS working together? What gets done where? How does DQS integrate with MDS? Do you use MDS to fix the data?

One of Microsoft’s definition of Master Data Management is “we define Master Data Management (MDM) as the technology, tools, and processes required to create and maintain consistent and accurate lists of master data.” MDS contains a framework for identifying and implementing workflows, security, business rules and auditing to the business data. DQS is part of that process that can be used by the MDS product to accomplish these tasks. To best illustrate how they work together, I’d recommend downloading their DQS and MDS tutorial. At a high level MDS enforces rules for cleaning the data and DQS can be implemented as a part of that process. For example before adding data to MDS, you can use DQS to clean the data prior to evaluation, but it’s use isn’t required.

In your SSIS demo, what additional steps would you recommend to have the data steward process the invalid data so that it can be reprocessed under SSIS?

Ideally, when data fails a quality process, the data should be placed in a review table so that it can be reviewed by the data steward. Having a person resolve the issues and add them to the knowledgebase will continually improve the knowledgebase over time.

When building a knowledgebase within DQS, can you include multiple columns for evaluation within one table?

DQS is not limited to evaluating one column at a time. You can use combinations of columns within a table to determine what the correct values are.

 

If you missed my session, a recording is available here , so please feel free to review the video when you have a chance.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur