Tag: DQS

Categories
Business Intelligence

Day 1 Review #sqlpass #summit12

This SQL PASS Summit was my third, and it was good. Kind of crazy timing as we just had a baby 2+ weeks ago, so I am very lucky I got to go.

Day one was Wednesday Nov 7th. There is a kickoff thing the night before which is always good to see everyone again, etc. There are pre-cons two days before (5th, 6th). Myself, as with many I talked to, came out the 5th, thinking the conference started the 6th, which we were mistaken, so it was kind of a free day, but still things going on. The website said 6th-9th so we all assumed without digging into the detail. At least I wasn’t the only one.

The first day keynote was good, Ted Kummert from Microsoft which I have seen a few times now, and the same cast of characters, Amir Netz showing off more Power View and Movie data. The big things announced that made me perk up were SQL Server 2012 SP1 and Power View over OLAP (coming soon?). No big flashy giveaways like BUILD, but good keynote, then the fun starts.

I attended 4 sessions on Wednesday

1. BIA-303: What’s New in Analysis Services 2012? – Chris Webb

This was my first session of the day, and it was in 305-TCC. TCC was across the street, which maybe was like that years past, but I never had to go to any, so everyone seemed lost. We finally got there, but then Chris Webb told us that the abstract was wrong in some places and the talk would mostly be about tabular, not multidimensional. Oh well, good stuff anyways. There was one slide about OLAP stuff. The biggest thing I got out of this was xEvents for SSAS, and how to pull into PowerPivot. This is the first time I have seen Chris Webb present and it was good.

2. BIA-316-M: Enterprise Information Management: Bringing Together SSIS, DQS, and MDS

For the second session, it was two Microsoft employees. I like to try to hit many sessions by Microsoft Employees because well, they usually have worked on the products, and they get into details, and they sometimes let some juicy details slip.

Matt Masson and Matthew Roche are great presenters, funny and play off each other. They showed and telled SQL Server 2012 MDS and DQS and discussed how it could and should be used in orgs. Master Data is a huge issue in many businesses and the Microsoft solution looks really good. Using DQS along with SSIS to clean your data, or as a very smart “spell checker”, and then MDS to track changes, workflow, and send back data to source systems if you’d like. The big thing here I took out was how they see MDS fitting into businesses, and that a BI team should implement MDS/DQS to make sure their dimensional data is clean and the “golden master” they need for great BI reporting, and updating back to source systems is a secondary thing.

3. BID-212-S: Around the World with SharePoint BI Toolbelt

This was a typical Brian Knight session. Not as huge of a production as some of them I have seen. Just him and his employee/bi architect and a helper/demo person.

They showed quickly how to get SharePoint setup for Excel Services and Power View and then did some demos. Overall good stuff but seemed a bit rushed and some things didn’t work. They demo’d PerformancePoint, which who knows what future that has, but seems like the best tool for OLAP scorecards in SharePoint. Performance Point has been an enigma for us to do anything with, not sure we ever will. I always see it demo’d and see the benefits, and see what it can do, but we never get around to doing it. Maybe someday, or maybe it will just get replaced by something..

As I said he brings up a sales person from his team or someone new to show how easy it is for a non-techie to use Power View (or whatever tool they present) and go through a little demo.

4. BID-102: Mobile Business Intelligence for Everyone, Now!

Final presentation of the day was with Jen Stirrup, who also won the PASSion award on Thursday. I also chatted with her briefly Wednesday morning, which was good as I haven’t met her before this summit. The presentation was OK. It was a 100 level, but I wanted to see some Mobile BI. I have some high expectations as I saw Jen Underwood present on Mobile BI at TechEd, so was expecting more of the same. Jen Underwood was actually in the audience and answered some audience questions.

The presentation had some technical glitches, and also dug a little to deep into visualization discussion, which is good, but I wasn’t expecting it in this one, maybe a different session. Jen showed some stuff on her iPad, and talked about how she uses Azure and SSRS in Azure, and also HostedPowerPivot, which was good stuff, but nothing new that I didn’t see at TechEd.

I use MobiSSRS for SSRS reports on iOS and that works great, she didn’t mention it, but Mobile BI presentations can get into the “3rd party app here and there” instead of what you can do out of the box. With mobile BI though, the first question is, “do you run SharePoint?” and the second is, “It is Enterprise?” because that makes a big difference in what you might try to do

Wednesday was a good day, I didn’t do much in the evening besides just grab a bite to eat and hit the hay. Bummer this year was that I started getting a cold on the way out on the plane, and it ate at my voice all week. Nothing to serious but enough to not want to talk in a pub about BI much as you have to yell.

More to come about Day 2 and Day 3, and overall thoughts..

Categories
Business Intelligence SQLServerPedia Syndication

SQL Server 2012: Data Quality Services

With the release of SQL Server 2012, I am looking more into Master Data Services (MDS) and Data Quality Services (DQS). A brief overview of DQS.

You install DQS with SQL, and you have to configure it. The server configuration is a cmd line process that runs to create some databases on your server (DQS_MAIN, DQS_PROJECTS, DQS_STAGING_DATA).

I ran into one issue with the running of the configuration, not sure if this happens everywhere, I am running Windows 8, but nonetheless, I ran into. After running the tool and getting error after error, and trying as admin, etc. I dug deeper into the error message and found that I there were some security/permission issues I had to resolve. It ended up being that I had to change permissions on

C:WindowsMicrosoft.NETFramework64v4.0.30319Configmachine.config

to allow write access. Once I did that, the configuration tool worked and I could get into DQS.

DQS gives you a “Data Quality Services Client” to work with. When you open it, connect to the database where you configured the three databases I talk about above. Once you, you have 3 panes.

You have Knowledge Bases, Data Quality Projects, and Administration.

Knowledge Bases: datasets of known data that you can use in your Data Quality Projects. You get a default Knowledge Base – state names and some other data similar to that.

Data Quality Projects: Here is where the magic happens. You can choose some source data (Excel xls – xlsx wouldn’t work or SQL table) and then apply your knowledge base on it. Then you can reimport your data at the end back into SQL or export it, and update your Knowledge Base with learned values.

Administration: Not a ton of options, but you can set some thresholds, and also setup your Azure data market settings.

Azure Data Market https://datamarket.azure.com/browse/Data?Category=dqs – Lots of data you can use to combine with your Knowledge Bases. Much more here and I won’t go into detail – it could be its own post in itself.

As a test, I took an excel file, added a few records with columns first, last, city, state (I actually imported into a staging SQL table to work with it) – But in the state field I put different variations of state, WI, Wis, Wisconsin, MN, Minn, Minn., etc.

I then ran the file through creating a new data quality project and ran it against the default Knowledge Base, and it corrected the values it could. Got a weird error clicking next on the project, it seems the button is touchy. Hopefully they come out with a fix soon.

Once you build up and get your Knowledge Base stable, you can use from SSIS packages or in Master Data Services. I see many useful applications for DQS. Either around your corporate data or pulling in data from Azure data market to cleanse existing data you might have (think: looking up gender from first/last name).

This post is a brief look at DQS and how it works, but there is so much more. I hope to get more in depth in the near future.

Categories
Business Intelligence SQLServerPedia Syndication

Selling Management on SQL 2012

2012 is going to be a big year in the SQL world. No, the world isn’t going to end. SQL 2012 should get released by Microsoft, hopefully in the first half (cross your fingers for the first quarter!) of the year. Great! But many out there are now on SQL 2005, or 2008, or 2008 R2, some even on SQL 2000 (SP4 – still get support?) but you want to get to SQL 2012. What can you do to make that transition easier? You need to sell the features and benefits, just like anything else.

Clustering

If you have any kind of clustering environment, or mirroring, or are even thinking about doing clustering, then SQL 2012 is going to be what you want to do. With AlwaysOn, it makes it dead simple to create and manage clusters. If you look back over the versions of SQL, and think clustering, you might shutter. With 2012, things become much easier and management has to see this benefit, as with anything, to make your systems more available with the new AlwaysOn

Master Data Services and Data Quality Services

Microsoft came out with their first round of Master Data Services (MDS) in SQL 2008 R2, but it was lackluster. The interface is clunky, weird, and hard to use. Most “end users” of MDS aren’t going to be that technical. You need something simple, like SharePoint, or Excel. MDS is neither (even though its a weird version of SharePoint). With 2012, MDS is vastly improved and actually something viable where an Enterprise could use it for a Master Data Management (MDM) solution. Couple that with Data Quality Services (DQS) and you get tons of bang for your buck. with MDS and the excel add on, this will be just what the doctor ordered for MDM groups in businesses.

Business Intelligence

Near and dear to my heart of course, is Business Intelligence. What a huge release for BI folks in 2012. First off, a whole new analysis services type, Tabular. Columnar Vertipaq type cubes. Reverse engineer PowerPivots right into SSAS Tabular and then tweak to release out to the Enterprise.

Then the enhanced SSIS stuff, better IDE, better management of packages, and more. Of course the integration with the Visual Studio 2010 IDE is a welcome feature, especially for those of us that also need to work on C# and .NET 4.0 stuff!

But don’t forget the potential biggest thing yet out of the BI tools for 2012 – Power View (yes the space is intentional, not sure why.. but now we have PowerPoint, PowerPivot and Power View). Naming aside, Power View could be a HUGE analytics tool to get more BI out to the people in an Enterprise. First off, they plan on making it work on iOS! Power View works on tabular cubes, so you see the tie in there. The one big thing with Power View, is it just works inside of SharePoint. No stand alone editor. You better have SharePoint 2010 and a pretty good SharePoint admin along side your BI team to get all this stuff working. Some of the enhanced end user alerting in SSRS integrated mode looks nice as well. But once again, you need SharePoint! DON’T for get the SharePoint!

There is much more in SQL 2012 that will make DBA’s lives easier, and BI pros development streamlined. Too much to outline in just one post. But if you are trying to sell SQL 2012 upgrade to management, the “big three” things I outlined above are a good starting point. One thing to be aware of though is that the licensing model has changed in SQL 2012 to core based, so you would want to read up on that.

I’m excited for SQL 2012 bits to hit and I hope you are too!