Jan 28 2015

What is Power BI? Microsoft just changed the answer to the question

Author: Ginger Grant • Discussion: 3 Comments

In reading the Power BI blog, it appears Microsoft has just changed what Power BI is. Since Power BI’s introduction last year, Power BI was a series of add-ins to Excel, which I liked to call the four powers, Power Pivot, Power Query, Power View and Power Map, and a web component. For people who weren’t interested in the web component, the most of the features listed in Power BI are available to anyone who has Office 2013 or Office 365. That now appears to have changed. Microsoft now says that Power BI is a Cloud Based service, and doesn’t mention Excel at all.

New Power BI

As I documented here, Microsoft released Power BI Designer as a web application and a stand alone application. It has been freely available since December 18, 2014 to anyone living in the US. This was the first step away from Excel as you no longer needed Excel to create dashboards. In the January 27 announcement, Microsoft has completely broken away from Excel. Now Power BI is new, and the new Power BI is the Designer of December. The old restrictions still apply. Sorry, if you are not in the US, you can’t use Power BI Designer aka the new Power BI. You can get an iPad version of the app in the Apple store and the Surface version in the Microsoft store, but phone support is not yet available for either iPhone or Android, but they are slated for release later this year.

New Product, New Price

It appears the mall is not the only place having sales in January. Microsoft just announced a major price reduction in Power BI too. Here’s the previous pricing model, which I saved from Microsoft’s website, just in case the Power BI webpage changed, which it did.

PowerBILicensing

Here’s the new pricing model ,which doesn’t fit very well on my webpage.  To save you having to click on it, I will cut to the chase. Power BI is now $9.99.   Now that they product is targeted to the masses, the price isn’t an even number, just like everything Not Sold In Stores. It stands to reason that dropping the price will help in the  wider adoption of Power BI. The previous pricing made Power BI much more expensive than Office 365, which was probably a tough sell to many IT managers.

What’s Next with Power BI ?

To be honest, I have no idea what Microsoft is planning next. This announcement marks a big break with the past, which I guess we could call Power BI 1.0. The new direction to a standalone and web product makes Microsoft look more like its competitors, which I am sure was the idea. Personally I thought the break with Excel was quite surprising as I thought the plan was to leverage the knowledge of the current user base, so I didn’t expect it. I wonder if they are going to rebrand the four powers in Excel?  Based on today’s announcement I wouldn’t be surprised, and I will be watching Microsoft closely to see what happens next.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Jan 17 2015

Running the local version of Power BI Designer Preview

Author: Ginger Grant • Discussion: No Comments

Power BI Designer is available both as a web application and as an desktop application which you can download to your PC. As you might have surmised, the two versions are mirrors to one another when it comes to functionality, with the biggest difference is the need to upload the results. The local version allows people who have an older version of Excel loaded to do Power View-like report functionality which they may not be able to do with the version of Excel they have.

Getting Started

If you want to get started with Power BI Preview and you live in the United States, click here www.powerbi.com/dashboards . You don’t need to be a current Power BI customer to download and use the preview. The best way to learn how to use it is through the videos which Microsoft has included on the application start up screen shown below. I highly recommend watching them as they succinctly describe how it works.PowerBIDesignerVidoes

Things to keep in Mind when using with Power BI Designer

This product is still in preview, so there are some things that you can’t do, like change the colors. It was also meant to be non-developer friendly, so Power BI Designer picks a lot of things for you, and then you can change them afterwards. This model may be a little disconcerting if you don’t care about line charts, which seem to be the default. Microsoft created this program with the intent that you would be uploading the finished product to a Power BI tenant, it isn’t designed to have any native security or data refresh. This is not Excel, as the files you save in Power BI Designer have the suffix PBIX, so you have to have Power BI Designer to open these files.

Generating Dashboards

The steps for creating a dashboard are identical to how the Power BI Designer works with the online preview. First you need to select a data set, which can be from nearly anywhere–Azure, HDFS, Facebook, SSAS, Sql Server, MySQL,Sales Force, csv, to name a few—and then it will attempt to visualize your data sets for you. If you don’t like the visualization, most likely a line chart, which was defualt selected you can select a different visualization format, such as a treemap, funnel chart or any of the different options. To complete the dashboard, merge your selections onto one screen by tiling smaller visualizations onto a dashboard. The data displayed on the dashboard is contextual, so if you change the value all of the corresponding visualizations will change along with the selection.

Wait, There’s More

Microsoft is just now rolling out with Power BI Designer, and like Ronco’s ginsu knives, there are a lot more things planned for the product in the future. Here’s a list of suggestions made so far. If you think the product really needs something, go tell Microsoft as it appears they are actively monitoring the feedback. When they do release something I find noteworthy, I’ll let you know in a later post.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Jan 11 2015

Presentation Follow Up to Data Analytics and Distribution with Power BI

Author: Ginger Grant • Discussion: No Comments

Thank you to all of the people who took the time to view my session on Data Analytics and Distribution with Power BI. I really enjoy getting the chance to decrease the confusion I hear regarding Power BI, and hope that you will find the question and answer section helpful if you are trying to learn more about the product.

Questions and Answers

 In Power BI Designer, is it possible to manipulate the colors of the charts?

While this may change as Power BI Designer is still in preview mode, currently the colors are assigned automatically. As you might guess, this is a feature that other people are interested in so it is on Microsoft’s list of things to add. If Power BI Designer color selections are implemented like they are in Power View, which is very similar to Power BI Designer, it is likely themes of colors will be available, rather than the ability to pick each color like in Report Designer, but we will have to wait and see.

Are Power BI designer/dashboard changes specific to each user?

If you have created a Power BI Designer Dashboard, you have the ability to share it with people in your organization. There are a couple of things that need to happen for this to work. The people that you share it with must have Power BI accounts, and they have to be in the same domain as you are. When you share the reports they are only able to read, not edit them. For more information regarding security and Power BI, see Microsoft’s guide here.

Can I grant access to users outside of our domain?

Power BI’s security model is a separate tenant from the security model for SharePoint in the Office365 cloud, but they are related as you can only grant access to Power BI if those users are able to access your version of Office 365 SharePoint. As stated in the previous question, for Power BI Designer Dashboard, the users must be part of the same domain.

How does Power BI perform predictions? Is it the same logic which is used in Data mining?

Power BI uses the Forecasting and Hindcasting features to perform predictive analytics. There are a number of different analytical categories, and the kind used in Power BI use Time Series. As the name suggests, Time Series models analyze a set of measurements performed over time to determine patterns in the past which can be used as guides going forward. Data Mining looks at variables, which may or may not include time, as it looks for patterns throughout the data. These underlying statistical models are not the same.

Does Power BI have Power Map feature?

Power BI definitely contains Power Map. In fact Power Map is only supported in preview mode if you do not have Power BI. This link can provide more information about the limits of Power Map in Excel. Power Map is designed to be run as a movie, and provide a directed look of the data on the map, rather than providing the interactive drill down mapping features which are available in Power View. You can share a Power Map by saving it as an mp4 video file and posting it anywhere. There are a number of Power Maps on You tube if you care to search there.

How does “R” play here?

The R language is completely agnostic as to what is the source of its data, you have the ability to use excel if you want to as a data source. If you want to use R within Excel, try the RExcel add-in, which is available here.

Is the PowerPivot where the data is stored for Power BI Designer?

No. Data can come from anywhere, not just from Power Pivot in Excel. For example, if you want to use a website as a data source, you could do that too as there are a number of different available data sources, and that is one of many.

Do you have to use Power BI Designer on the Web?

While Microsoft has designed the Power BI Designer as a web project, so that you can create Power BI Designer Dashboards as part of the preview, there is also an application available for download here. The desktop application works very similarly to the web version, with the exception of course that you will need to upload and Select Power BI Designer file as your data source. Should you wish to modify the dashboard once it is loaded, you can do so.

How would you determine anomalies or freak instances in data versus true trends?

The problem of determining anomalies is one which the practitioners of predictive analytics are constantly struggling. For trending to occur, the numbers of what was previously considered an anomaly need to increase. Forecasting within Power BI applies one of the more standard methods for accounting for anomalies, looking at the standard deviations and probability. The likelihood a number will fall within a certain range of numbers are based upon the number of times this has happened in the past, which is graphed as a bell curve. The values representing the far sides of the Bell curve are discarded, which is known as variance, which in Forecasting in Power BI is represented as 1δ .The number increases with the more variance you wish to represent.

Does SharePoint on premise support Power BI Designer?

No. Power BI Designer is currently in preview version in the US for Power BI subscribers. You can download the application to play with it.

Will Power BI be available in next version of SharePoint?

While I cannot speak for Microsoft, I can tell you that it isn’t there now. For more information on SharePoint, check out their website here.

Does this work in Office 365 SharePoint? And this replace the bi feature offered in SharePoint on the cloud?

Since I do not work for Microsoft, I am hesitant to talk much about how their licensing plans really work. For more information, please check out their website.

If we want to start learning Power BI, where do we start?

There are a number of great places to learn about Power BI, the best and most up to date being here. I have included some other places where you might want to go to learn more about Power BI

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Jan 07 2015

The Changing Forecast of Power BI

Author: Ginger Grant • Discussion: No Comments

On Thursday, I have the opportunity to talk about parts of Power BI which reside outside of the Excel. I’ll be showing the some of the web components of Power BI and how to use them. Because the install base of the Power BI tenant is not nearly as high as the number of people who have used the Power BI components within Excel, this webinar will highlight the more exotic features. When I signed up to do this webinar, I planned to showcase forecasting and hindcasting as they are neat ways of visually showing predictive data analytics. Having used them before, I thought that demoing their capabilities once again would be a good way to show some of the neat reasons that you might want to use Power BI. Unfortunately, I won’t be able to demo these features.

Forecast Maintenance

Imagine my surprise when I saw this announcement from Microsoft stating that the Power BI Forecasting feature was “offline for maintenance”. Since the announcement came in December, I originally thought that this would be no big deal. Surely the feature would be back online soon. I thought wrong. The forecasting feature is gone. When using Power BI, the option never appears. It’s like it never existed.   In the excitement to release Designer, the non-excel dash boarding feature, the disappearance of the previously released tool has not been highlighted, until now.

The Dark Cloud of Iterative Releases

When the Forecasting feature was released in May, there was a lot of buzz about the fact that Microsoft was able to release a great new feature in time to coincide with the PASS BA Conference. Well, the pulling of this feature is the Nimbostratus of the cloud release, bringing rain. There are a lot of features available for me to show in Power BI, so I still have a lot of demo material, but I find this development to be rather troubling. Here’s hoping that this giving and taking of features in Power BI doesn’t repeat itself.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

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