Tag Archives: BI

Microsoft Business Intelligence Now and Into The Future

10 years ago, it was SSIS/SSAS/SSRS

Then in 2007 SharePoint, PerformancePoint/SSRS

Then in 2010 Power Pivot in Excel/SharePoint, then Power View in SharePoint

Then in 2013 Power BI … Power Pivot, Power View, Power Query, Power Map.. In Excel and Office 365.

Now in 2015 Power BI Version 2. Not in Office 365, separate. Power BI Designer, or use the Power BI web site to set up your dashboards, mobile, etc.

All the while, the existing solutions that have been available previously are still there and available, making things… well, confusing to say the least.

Most shops .. It all depends on when they started going heavy BI with the Microsoft tools, on where they land. Also, how well they could move when things change, as well as how much they want to stay up to date with the tools.

If you started 10+ years ago, you probably have a good base of ETLs written in SSIS, as well as many multi-dimensional (MD) OLAP cubes in SSAS, and SSRS reports off your cubes and data warehouse, running in SSRS Native Mode. You started with SQL 2000 if you were lucky, with cubes and dts packages, but then SQL 2005, then 2008, 2008 R2, 2012, 2012 R2 and now are on 2014. You really liked 2005 SP2 and 2008 R2 for the BI features :). This setup is like the VB6 or .NET Winforms of BI. It will probably be around forever in some way shape or form but not a ton of updates and Microsoft has moved on.

If you started a little later you might have SSRS in SharePoint mode, and some Performance Point dashboards. You might have even used Performance Point for planning/budgeting (and loved it?) until Microsoft killed it. Then you had to look for alternatives for that, or use OLAP Cube Writeback. In my opinion, SSRS in SharePoint and Performance Point are dead. Not dead as in they don’t work or won’t be supported, but I see them as the wrong path, life supported direction. If you are still using these heavy I would look for alternatives.

Now it gets interesting. You started with Excel 2010 and PowerPivot (no space!) and had SharePoint 2010 setup and Power View in SharePoint. You created V1 Power Pivot models, they were limited, you could do some things, but still it was limited. You still needed to get data somewhere so SSIS ETL’s or something to get data in tables you can use. If you are using Power View in SharePoint, I would hurry up and look for alternatives, it is dead (my definition of dead like SSRS/PP in SharePoint). Excel 2010 is long past and V1 PowerPivot is dead too. Seems like this era was short lived and just a stepping stone.

Then, in 2013, Power BI. So they added a space to Power Pivot :) .. And made it better, v2. Added missing features, Tabular SSAS cubes even! And Power View could be used in Excel. They both came by default in Excel (depends on version) but turned off. Power Query came out of nowhere and is awesome and Power Map, while buggy, was better than nothing. But what do you do with all these solutions you build? Where to publish? Not SharePoint on prem? But Power BI in SharePoint Online.. So you need Office 365 and Power BI subscription. You set up Data Management Gateway so you can get to your on prem data sources. You can refresh once a day or manually. You can do some pretty cool things, create workbooks with pivots and Power Views.

But you are missing things. Missing things like the ability to schedule a report to run and email someone, like SSRS. You are missing awesome formatting abilities for every pixel like SSRS. You wonder when SSRS is going to come to Power BI or what your options are… you hope you see iterations and features released to Power BI as that is the path, but then..

New Power BI Preview comes out in 2015. It has a standalone Power BI Designer (reminiscent of the Performance Point designer) that lets you create reports, dashboards and save a file to publish to the NEW Power BI portal. So you have two Power BI portals.. New and old. They don’t overlap or talk to each other, the licensing is different, etc. The old Power BI lets you connect to SQL on prem with refresh with the DMG and other data sources, etc. The new one does not. The new one lets you connect to GitHub and SalesForce and Marketo, but not other data sources that the old Power BI did. The new Power BI lets you connect to on-prem TABULAR SSAS cubes with refresh, but not MD ones (yet). The new Power BI lets you connect to excel data in OneDrive/OneDrive for Business. So could one publish a data file out to ODFB to faux refresh? I have yet to try. The new Power BI lets you publish dashboards to the iOS Mobile apps and also  embed (up to 10 MB – which needs change to be bigger) on websites. New Power BI has an API that lets you create your own connectors / REST API for things. And the list goes on and on.

So where does that leave us? Well, of you invested time and money in BI the last 10 years, you might feel like Microsoft is abandoning you. It kind of seems that way. You need to change or get left behind. But what do you change to? Change your MD cubes to Tabular? Rethink your architecture? Sync data to Azure?  Power Pivot/Power Query? Abandon SharePoint as a BI tool? Move your reports from SSRS to something else or Power BI (if you can?) I am unsure. Still trying to figure it all out.

One thing for sure is, it will always keep evolving. Me, I would say, tabular first if you are on prem. Try to use Power BI where you can. Minimize SSRS reports. Use SSRS native instead of SharePoint. Stop using PerformancePoint if you are still using it or thinking about it. I bet at some point SSRS comes to the new Power BI – there is an item on the UserVoice forum already asking for it. Try the Power BI Designer and Website and see what you can do. Always be trying to get something going in the newest and latest technology/tools available.

Have Fun with Microsoft BI now and what is yet to come!

 

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.

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!

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?


Where does PowerPivot Fit?

Now that SQL Server 2008 R2 is out, and Excel 2010 is out. You can get PowerPivot (http://powerpivot.com/) and create your own in memory cubes!

…. Or something like that.

I still haven’t figure out where PowerPivot fits in a business scenario. Why?

Well first you have what are now being called the “old school” BI users, that use Excel to connect to an SSAS cube and create fancy pivots and reports, maybe convert to formulas and create some nice reports/dashboards.. analytics.

Then you have people who only consume canned/standardized reports, through SSRS mostly, or maybe Excel Web Services… but they don’t create. Just consume.

You might even have power users, who take Report Builder and create those SSRS reports for other users. Awesome.

But then, you have this tool, PowerPivot. What can you do? Hit databases (mostly… cubes and other sources as well), bring back data, relate it, and create pivot reports/graphs off of it.

But you better be pretty dang advanced as a business user to use PowerPivot. I could count on one hand the users (that I have dealt with over the last 10 years) I would feel comfortable giving it to and not ending up with more of a headache.

What do I think is still missing from the Microsoft BI toolset? Looking at Business Objects, the Web Intelligence. Universes. You create a Universe off of a datasource and expose it out to the user, they can create reports/ad-hoc whatever off of it.

Kind of like the ever elusive “Report Model” in the Microsoft stack that no one ever uses, ever will use, or has no reason to use. But in BO, they make it useful.

I don’t see PowerPivot taking the place of a Report Model/Universe, so where does it fit? IT Analysts making “pre” cubes before you actually make cubes for your users that just want to hit it with Excel and not care about anything else?

Or people who just want to create their own cubes in silos. Tell me how that lends itself to “one version of the truth”?

Either way, we will see how it evolves and hopefully find some good use for it. :)

Agile in Business Intelligence? Of Course!

About 3.5 years ago I was introduced to Agile at the Agile 2006 Conference. After that, and implementing it in a software dev environment, I found that it just works. Sprints, Scrums, Stories, Backlog, Velocity, all the pieces fit and work.

Now that I am managing a Business Intelligence group, which when I started wasn’t doing *anything* as far as a method, I had to ask myself if doing Agile would work (I knew it could, but it is different than software dev in many ways, similar in others).

Back in October, my group went Agile. We set up a board, got some index cards, and just started Agile. A big paradigm shift at work for IT, but we needed to do something.

With Business Intelligence, we really don’t have *code* to work on, but more “objects” (Cubes, Dimensions, Reports, etc). As a team we needed to figure out – what is a story? What is a feature/enhancement/task. What is an epic? How are we going to score things, etc.

The first few sprints (2 week sprints – Wednesday’s to Tuesdays) our velocity was lower and/or we just scored things a little weird. But since then we have learned our “zone” of scoring stories and we got into a groove of releasing our BIG cube every 2 weeks, and releasing the smaller changes when completed.

We do the daily *scrum* for 15 minutes, and track burndown on stories, which lets us make some cool burndown charts that we tack up on our board, and we have some other cool bullet charts to track velocity by sprint, to our original, and final goal, and more.

What has Agile brought our group? Confidence, Stability, Ability to Meet Expectations. Agility. Results. and more..

Do we run into issues yet? Of course. Can we adjust and handle them. You bet! Are we continuously learning and changing our process to make it better? Yep. Always room for improvements.

What else does Agile bring us? Visibility to our customers, and to our peers in IT. Eventually the “BI” stuff should just run, over and over, iteratively.

Trek BI Agile Story Board

Agile isn’t a silver bullet though. It isn’t easy. You still need to work to keep things organized and on track. You have to fight that organizational gravity that sucks teams back in and people in as well, and throws that scope creep back onto stories and projects. You also have to fight to get rid of your technical debt, which depending on how long things have been running before you started even thinking about Agile, might take you a while.

This post is more of a high level “Yes We Can” type post about Agile in BI. I haven’t decided yet, but my guess is I might have some more detailed posts on how I like to run an Agile project, and what we are doing as a team to handle situations that come up, and just how we do things.

In the end though, just remember, have fun!