Tag Archives: 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..



Trek BI Agile Story Board

Business Intelligence – 3 Years of Agile

Last year at the PASS summit, I ran into someone and was discussing project management and Business Intelligence. They were so adamant that agile couldn’t work. And I had to correct them, as I have been doing agile now for three years at Trek in our BI group.

Yes, some things don’t exactly parallel to software development, but many things do. Sprints, Standups, estimations, stories, points, scrum master, releasing/delivering value on your iterations. And now even more with process like unit testing of SQL code and more, things are getting closer to software development in that regard.

I have an entire blog category dedicated to agile – more concepts but also talking about Business Intelligence teams in some of them.

Just remember, you can do BI and Agile, it works, and you can deliver a ton of value to the organization. Someone who might argue with you, well, they don’t know what they are doing in BI or in Agile, or the organization isn’t willing to change, which of course then it wouldn’t work.

Advice. Make sure you hold retrospectives, and make sure you make adjustments from whatever comes out of them.



data quality

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:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.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.



sqlserver

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!



pass

#sqlpass 2011 Summit Thoughts

Want to get a quick post out before I forget everything cool I have been thinking about the 2011 SQL Pass Summit in Seattle WA.

The conference started out great with a first timer reception (this was my second year), and I knew a few first timers so that would great.

The first day keynote was good, and then the sessions I hit were on a full range of things, new semantic search stuff with file table, and more.

I missed a couple of sessions on day 1 because I was in a Microsoft led focus group on “BI in the cloud” some very cool ideas thrown out and excited to see what is coming up in the future.

More good keynote on day two, a deeper dive into the stuff in SQL Server 2012 (formerly Denali). Went to some good sessions on Data Quality Services (DQS) and Vertipaq vs OLAP, and Power View (remember the space!) SharePoint and Power View integration, etc. Some very cool stuff coming soon with the 2012 release.

Tabular cubes vs Multidimensional cubes. OLAP vs Vertipaq, etc. Big debates and questions here, when to put things in Tabular vs MDX, etc. DAX solves some hard problems we run into with MDX.

Another thing I went to a couple sessions on was StreamInsight. This is some very cool technology for complex event processing, using .NET and LINQ. The StreamInsight guys are crazy smart. Also integrating StreamInsight with Azure, and doing some crazy processing and analysis of tons of data in near real time. I see this technology making a difference somewhere down the road.

Power View was another big winner from the summit. This integrates with SharePoint 2010 as a Silverlight (SL still isn’t dead?) report builder and viewer. Not even sure they are called “reports” anymore, but “views”. You create views and you can interact/edit them in real time. Export to PowerPoint too. These views work off “tabular” vertipaq BISM (BI semantic model) cubes. This technology is going to change how we deliver information to the business.

Being able to take a PowerPivot (no space!) and reverse engineer it into SQL Server Data Tools (formerly BIDS – in VS2010 shell) to create a BISM tabular cube and publish to SSAS Tabular instance looks great. Solving problems like crazy snapshotting and many to many and time analysis using DAX looks very useful.

Of course, GameWorks appreciation night was fun, and I found a Mcmenamins (Six Arms) up the road from the convention center, so I got get a taste of Ruby again. Good conversation and times at the Taphouse and around the Sheraton and Convention center was good too. Seattle is a nice city and the weather was pretty good I thought. Another great summit.



PowerPivot, Excel Services, SharePoint 2010 Farm, and You

The last few days I have spent an exorbitant amount of time (5-6 hours?) getting PowerPivot and Excel Services working on a SharePoint 2010 Farm. I just want to get out there some of the things I had to do to get it all working, and why (at least what I think is why).

First off, most dev setups are using one box for SharePoint, which, in my eyes, masks most every issue you will run into.

Most prod environments are multiple boxes, Web Front Ends, App Servers, etc. This leads to the most pain when setting up these services (PowerPivot, Excel Services) as there are hundreds of different configurations and setups. You need to get it juuuust right.

Anyways, I will explain as best I can.

First, assuming you have PowerPivot running on an app server and Excel Services running to. You upload your PowerPivot workbook to a PowerPivot gallery and you go home happy. But wait, does the data refresh correctly? Can you even open it?

1. If your site is running https, you have to tweak the Excel Services settings.

By default, only http:// shows up here (I think?) so you need to add https:// or you can’t even really get into your PowerPivot.

image

2. You probably are going to have to up the upload size limit

Some PowerPivot workbooks are big, like 80-100 MB big. Default in SharePoint 2010 for Excel Services is something like 10 MB, and SharePoint default is 50 MB, you need to change for both settings.

3. If you don’t have Kerberos set up, it is tougher.

PowerPivot refreshes data from some system somewhere to its own “cube”, then Excel Services refreshes data from that cube into Excel Web. You have to setup a way for the data to get refreshed into PowerPivot’s cube. No Kerberos? Then you need to use the Secure Store Service and set up a credential, and set that up as the unattended service account for PowerPivot refresh. Then, at least you can get data from somewhere else into PowerPivot’s “cube”

Second step is getting Excel Services to refresh from that cube into Excel web. What I had to end up doing was creating another credential in the Secure Store service for Excel Services Refresh (set up as farm account for now, it has the stroke it needs). And then set that up in Excel Services settings as well for Excel Services unattended refresh account. But also! – you need to change your workbooks before you upload or whatever.

In your workbook, go to your connection, properties, and get to the authentication, and change to “none” instead of “windows authentication”, then your data will refresh from the PowerPivot cube to Excel in SharePoint.

4. Same thing goes for the PowerPivot Management Dashboards

The management dashboards are set to “windows authentication” so they wont work either, you need to change to “none”, in the Management Dashboard site, goto all site content, PowerPivot Management, <some guid> folder, and then 1033 (US English), edit the two workbooks to use “none” and save, and your management dashboard will actually work!

I’m sure there is a ton more I go delve into here, but this is the high level. As Rob (@powerpivotpro) would say – “make sure to click on the slicers!”



Looking to Hire…

I have been managing two different groups @ Trek -  (Business Intelligence and .NET Software Development) for a while now, and we have some openings we are trying to file, so that is why I am putting this out here.

First role, looking to hire a Microsoft .NET Windows Forms developer, or someone with web experience looking to get into Windows Forms and eventually WPF/Silverlight, and also Windows Services. C# is the language.

Second role, looking for a Microsoft Business Intelligence Developer/DBA – SSAS/SSIS/SSRS, DBA experience preferred. Working on cubes, and ETL’s and reports and DBA stuff.

Shoot me an email at steve_novoselac@trekbikes.com with your resume and info.



App Store Pivot Viewer – ZoomAppy

Good ideas are hard to stop from happening. After dorking around with Pivot Viewer, I was thinking of things I could “pivot”, and the iTunes App Store was one of them. No real API though, there are some out there that have created APIs, or you could scrape the web, etc but nothing solid.

Looks like someone took that idea and ran with it, called ZoomAppy (http://zoomappy.com/). RIght now it looks like it is just the app store, but they have more in store.

I agree that Pivot Viewer is “Business Intelligence”, but it is a different way of thinking. Where in traditional BI, you think of looking at metric/measures like sales/inventory, etc. With PivotViewer, you are looking at “objects” and filtering them based on properties. Teams, Bikes, Apps, Cars, People. Also PivotViewer doesn’t give you any type of aggregations besides counts, so it is limited in that regard.

What else would be a good candidate for Pivot Viewer?




Blaming the Waitress

Most people have probably done this at least once in their lives.. You go to a restaurant and order a meal. The waitress is nice, comes over, gets your drinks, takes your order, checks up on you, refills, what not, brings out the meal, and woah, something is wrong, it is cold, bad, just something. But what do you end up doing? Getting mad and blaming the waitress. You might not give her any tip at the end of the meal, because you are mad about the mishap with the food.

But then you start to think about it, and you look back and realize that hey, it wasn’t the waitress who caused the issue, it was probably the cooks! But you are already driving home and don’t really do anything about it.

People like to cast blame, even that might not be the right phrase, but people like to *call out* the group on the front lines (note: in most cases. For instance in the military campaigns, when one side loses, people blame the generals and commanders, etc, not the privates on the front lines, and rightfully so).

In Business Intelligence, you are usually the “front line” to the business when it comes to reporting information and data. One of the roles of BI is to “deliver information to the business”. But then what happens when something is wrong with that information? People immediately blame the Business Intelligence group. It just seems to be human nature, just like blaming the waitress.

Now, don’t get me wrong, sometimes the problem might reside with the waitress, and then you would call her out on things, but you need to realize, your tip just doesn’t go to her. It gets split between her, the cook, the bus boys, the greeters, etc.

I hate playing any part of the “blame game”, but sometimes people should think through what they are actually “blaming” and make sure it just isn’t calling out the front lines because that is what is easiest.

I’m not sure that Business Intelligence will ever fully get around taking most of the up front hits when something goes wrong with data, or with a reporting server, or with anything that might be outside of its control, but what Business Intelligence groups should strive for is to be accountable for things within its control, such as finding problems before the business does, handling data integrity issues with ease, making ETL’s more fault tolerant, getting and handling server alerts for jobs and processes that it is in charge of, etc, etc.

Just try to remember the next time your food is cold at the restaurant, try not to blame the waitress :)




Microsoft Silverlight PivotViewer: Getting Started and Business Case

I have been reading about Microsoft’s PivotViewer lately, and decided to try to get it going for myself. What is PivotViewer? Think of it as visual data slicing through a web page.

What you do is take some data, and then tie records to images, and then publish out your “collection” and you can consume it via a webpage using the Silverlight PivotViewer control. One awesome example of this is here http://netflixpivot.cloudapp.net/. But what I have been really trying to wrap my head around is how to use the as a “business” tool. Because, it is easily technically doable, but you have to have a *reason* to do it.

Working with widgets and customers and locations – what do you do? There are two things I could think of quickly. One – peruse your “master data” very fast and visually. The other is looking at some kind of metrics for your widgets, or logos of your customers you might sell too, or ? ..Well, you could..

  • Master Data/Catalog
  • Show pictures of your widgets, and create filters (they call them facets) for things like size, color, weight, model, etc. You have “one” of each and you just want to see what you offer. Almost could be a pretty sweet online catalog browser

  • Sales/Metrics
  • Do the same as a master data catalog but allow filtering by some kind of metric. Shipped items over a given time or something.

  • Something Else I Haven’t Thought Of?

Anyways, the first thing you should do before anything is get some kind of data feed. Run a query, get some data from somewhere. Start small to test. 500-1000 records.

Then the fun begins. Starting from absolute scratch..

Ok, yeah, tons of setup. Biggest thing is in IIS you need to set some MIME types: .cxml, .dzi and .dzc need to be “text/xml”

Once you have all that setup, you can do 2 things.. create your collection, and create your app. Create a blank silverlight project first:

Once you have that, there isn’t a ton you have to do to get things going with PivotViewer.

  1. MainPage.xaml
  2. Add in your MainPage.xaml, a namespace line for Pivot, and add the control

    Your end MainPage.xaml should look like this:

    <UserControl x:Class="SilverlightApplication1.MainPage"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:System.Windows.Pivot;assembly=System.Windows.Pivot"
        mc:Ignorable="d" d:DesignHeight="300" d:DesignWidth="400">
     
        <Grid x:Name="LayoutRoot" Background="White">
            <local:PivotViewer x:Name="pvWidgets"/>
        </Grid>
     
     
    </UserControl>
  3. Reference Assemblies
  4. For good measure, just reference them all, located here: C:\Program Files\Microsoft SDKs\Silverlight\v4.0\PivotViewer\Jun10\Bin\

  5. Load Collection:
  6. pvWidgets.LoadCollection("http://localhost/SilverlightApplication1.Web/MyWidgets.cxml", null);

Note, you have to make your web part of your project IIS based instead of the build in web browser. Why? Because the .cxml HAS to be hosted on a web server, it just works that way.

Now, you need to create your collection.. you can use a cmd line tool they offer on the PivotViewer site, they also have a c# library for automating things, but it is best to first just do it manually. So I used the tool they have as an add on for excel. It adds a nice little “Pivot Collections” tab

You can use this to put some data in, you probably want to add more columns than what they give you by default. For my test I just used the same image for all records to get started. I have a feeling that the biggest barrier to entry to corporate BI teams and developers is going to be the imagery. You usually don’t have someone on your BI Team that knows how to use photoshop well and do all the high res imagery, so you are handcuffed there.

For testing sake, I Published my collection to the root of my website, with the name “MyWidgets”.

I loaded up my webpage, and I can slice and dice by all my columns I had in my collection, visually.. pretty dang awesome. (Note, I just made some fake data based on attributes I am used to seeing and with an image of a bike to see what it would look like – the goal was figuring out how would this work in conjunction with current BI offerings (cubes/Pivot Tables, SSRS, PowerPivot, etc))

Now, if you think where you could take this. Each “image” is clickable and brings up the image right in front of you. You could have all the specs of that widget there, and a link to “buy”, or deeper analytics for that widget.

Some other things I found out.. using the Excel tool for Pivot Collections is dog slow. Especially with a ton of records. It has to process the images for the “deep zoom” technology and it just takes a while. Like, hours.

There are tons of possibilities here with PivotViewer, both for an external website and also internal corporate business intelligence. It will give people another way to delve into the data and turn it into information.