Tag: Power View

Categories
Business Intelligence Technology

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!

 

Categories
Business Intelligence

Day 2 Review #sqlpass #summit12

To follow up on my first post about day one of this years PASS Summit, here is how day two played out

The “keynote” here was some PASS discussions, then Quentin Clark (MSFT exec) and Julie Strauss (wicked smart) doing an end to end demo on many things.. Hadoop, Azure, Data Explorer, Power View, Excel, etc. The blogger table was pretty annoying with their tweets during the demo calling it out as boring and not what DBA’s want, failing to remember that half the conference is BI people. I think the demo was “dry” but they showed many things and tied it together. I saw Julie at TechEd and she knows what she is doing. Of course every year the blogger table is going to say “zoom” on the presentations, which yes, they should be doing, or changing resolution, but to see the bantering back and forth on twitter is just bad overall for the people attending and watching and looking for info. The blogger/twitter table should be relaying information that people at home are clamoring for, not bad mouthing the presentation/presenters.

I hit up 4 sessions in all on Thursday Nov 8th..

1. BID-307-M: Using Power View with Multidimensional Models

As with day one, I mentioned I try to get to presentations by Microsoft employees, today was no different. The first one being with Bob Meyers and Sivakumar Harinath. This was a deep dive into the newly announced functionality yet to be released or given a date that will let us hit OLAP cubes with Power View. Honestly I wish Microsoft would have released this from the get go. One thing I don’t understand though is why Power View uses DAX to hit OLAP and TABULAR, while Excel uses MDX to hit OLAP and TABULAR. Seems split brained to me. Choose one and go. Many audience questions in this one, and one downfall of Microsoft Employee presentations is that they have a hard time saying “no” and get into discussions with audience members, many times taking too much time on some specific question.

Presentation was good, and we learned some things. New dimension properties for ImageUrl, Geography (for mapping), etc. And what will and won’t work with Power View and OLAP. Good stuff.

2. BIA-400-HD: Enterprise Data Mining with SQL Server

This was a double session, and I just stayed for the first half. Mark Tabladillo (marktab) is a PhD so that tells you something. Data Mining in SSAS/SQL Server has always been an enigma since day one. I don’t know of many using it in real life (besides the AdventureWorks Demo?) – it is kind of SSAS Cube Writeback, awesome, but not widely used. He showed how you can use the SSAS Data Mining cubes and Excel Add in to do forecasting, basket analysis and how to get into some of the options and get data out yourself to make your own visualizations, pretty cool stuff, but like I said, I left half way through…

3. BIA-309-M: Enriching Your BI Semantic Tabular Models with DAX

I left the Data Mining session early to get a good seat for this presentation. Kasper de Jonge from Microsoft is one I always try to get to as he is wicked smart as well, and usually the presentations are awesome, this one was no different. Getting into the details with DAX and just seeing someone like Kasper use PowerPivot, Excel .. it shows how “he” would use it, being a program manager, which is different than most. Great to pick up tips/tricks and just see how he goes about doing even the basics. He even showed off the trick on changing the DAX on an imported table to a DAX query to get whatever you want back from your tabular cube, he has a blog post that I went through a while ago to the same effect, which was cool.

4. BIA-206-M: BI Power Hour

Finally to end the day..Matt Masson and Matthew Roche again, with Patrick LeBlanc, Peter Myers, Sean Boon and Chuck Heinzelman.

This presentation reminded me of a Brian Knight spectacular.. throwing trinkets, books, etc to audience, goofy stuff. Pretty funny, and they go through SharePoint, SSIS, PowerView etc. Very lighthearted and a good way to end a 2nd day on non-stop technical things. Matt Masson is probably a stand up comedian at night, just funny stuff. I have seen Chuck present before and he is good, Sean showed us some PowerPivot with Olympic data and Shark bite data, Patrick with a Windows Phone app and Azure and SQL Data Sync, Matt with SSIS data app, and Peter Myers filled in at the end by capturing data from the audience over mobile and slicing/dicing it. I have seen Peter before as well and he is very methodical, it was his first “power hour” and it showed, but hopefully he does it again and is a bit more prepared.

Thursday night was the appreciation night, and gather at the EMP (music museum) in Seattle. They shuttle you over and back. Two free drinks, food (I think I had mac and cheese 3 nights in a row for some reason last week), and you can tour around the museum. There was #SQLKaraoke, but the sanctioned one, not the one at Busch Gardens. Live band and you get to sing, pretty cool stage and everything. Again, bummer, my voice was out or I would have sang a tune.

So to wrap up my 2nd full day, BI, BI, BI all day. More to come with the last day and overall thoughts for this year.

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
Geeky/Programming Life

Cool Things I Have Been Doing On the Computer Lately

In the past couple of months, weeks, whatever (time flies) I have been doing some pretty cool things on the computer, in a wide range of areas. Just want to get them down on paper (you know what I mean)…

1. Yammer – working hard on growing Yammer community, external networks, just getting engagement and showing the benefits. It’s fun.

2. Kinect – did some Kinect hacking. On my own then with the group, got some cool stuff to show. It is crazy how easy it is to get something up and running with Kinect and the SDK. I see this stuff taking off in the coming months/years.

3. Azure – dorking around with Azure, looking at what it can and can’t do, what it could do well, how it would fit in with everything.

4. SQL 2012/Power View – been playing around with SQL 2012 since “Denali”, but now its got an official launch date (March 7th) and things are getting real. Power View demos online, trying to figure out how SQL 2012 is going to fit into our infrastructure and just learning as much as I can about it.

5. Ruby – been getting into Ruby and Ruby on Rails on my Mac, git, sqlite3, heroku, etc. Trying to learn more things that just the .NET ecosystem.

6. Ubuntu – same here, set up a VM, been trying to use it consistently, trying to get the other viewpoints from Windows and Mac and where things are at. Keep up with the joneses so to speak.

7. Android – I picked up a Samsung Galaxy (Verizon 4G) a few weeks ago and have been using it. I still love my iPhone, but getting more into Android. Ice Cream Sandwich (ICS) is a pretty good OS, there are still quirks, but its better. Verizon sucks around where I live btw.

8. Google+/Picasa Web Albums – been getting this into my photo workflow, for sharing and backup. Liking it so far.

9. SharePoint 2010/FAST – been researching and reading FAST server like crazy trying to see how it will fit in with a potential project. I think it could be amazing. more to come.

10. SMS – been playing around with different frameworks, and seeing how they compare, trying things out. Using Voice and SMS is all the rage these days. (Hall and Oates thing anyone?)

Bonus: Nothing with computers, but I have been really getting into brewing beer/homebrewing. I think we have made 5 batches now, and the ones I have tasted so far are really good. It is a fun hobby and breaks up the constant technology I am involved in. More to come here too.

And much much more. Time is limited, time to post is limited. Getting out there and doing cool things is fun, and sharing them is fun too. Gotta find the right balance. I hope everyone is having a cool 2012 so far.

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!

Categories
Business Intelligence

#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.