Tag: OLAP

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

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

Categories
Business Intelligence SQLServerPedia Syndication

Office 2010: Excel 2010, What-If Analysis aka Microsoft Finally has Built in Cube/OLAP Writeback!

Digging into the blog post from earlier this summer I wanted to see what was new and exciting in Excel 2010.

Recently I have been working on an cube and we want to be able to budget right from the cube. There are also many other cubes/scenarios where the ability to writeback to the cube would be awesome. Some BI tools have had this for many years! Microsoft had something similar with a Excel 2002/2003 add-in, but it has been removed. Also, there are many 3rd party tools to allow this. You could also write your own macros or VBA/.NET code to do this as well, but what was always missing was the ability to do writeback directly from an Excel (OLAP) PivotTable. With Excel 2010, this functionally finally shows up.

First, you need a cube, and you need to enable writeback on a partition. This will automatically create a table in your database where you have your data warehouse. Lets say you have a table FactBudgets, and you enable writeback, SSAS will create a table WriteTable_Budgets. This is a trivial example I went through to test this functionality, but I just wanted to exhibit the feature of the writeback.

01_writeback

Turn on writeback, deploy and process your SSAS cube, and then open Excel 2010. Connect to your cube, and then in the PivotTable ribbon menu, on options, there is a button to turn on “What-If Analysis”. Turn it on. 🙂
02_whatif

Once you turn on the setting, you can then begin writing back values to your cube, right from Excel. If you click on a cell in your writeback value, you can just change it. See on the screen shot below, the value I changed, the little purple triangle, tells me it has a changed value from what is in the data source.

03_valuechanged
04_valuechangedmenu

You can see its telling me the value changed, I can Discard the change and other options. After you have your values set, you want to publish them, which you do back on the PivotTable ribbon menu:

05_publishchanges

As you can see, Microsoft has finally created a viable solution for writing back values into your OLAP cubes without the need for 3rd party software or coding. Finally!

One thing to note, if you try to write back to a value that is in a partition that doesn’t have writeback enabled, you will get an error.

06_error

Now, think of the possibilities with SSAS OLAP writeback and Excel, now that we can actually use it out of the box!

Categories
Business Intelligence SQLServerPedia Syndication

OLAP PivotTable Extensions on CodePlex

This weekend, I ran across this on CodePlex – OLAP PivotTable Extensions which got me thinking back to a post by the Excel blog about adding calculated measures and named sets in VBA (which is another blog post completely)

From CodePlex:

OLAP PivotTable Extensions is an Excel 2007 add-in which extends the functionality of PivotTables on Analysis Services cubes. The Excel 2007 API has certain PivotTable functionality which is not exposed in the UI. OLAP PivotTable Extensions provides an interface for some of this functionality.

What an awesome tool. I have been playing with it for a couple days and I have turned on some of the “power” users of the OLAP cubes to it as well. The first thing I thought of when running across this was “Woah, ok, when business users request calculated measures that might be more obscure, or just specific to them, they can add them! We don’t have to do a special release, maybe not even a release at al!”

The uses for this tool could be pretty extensive. You can import and export calculation libraries, you can also see the MDX that Excel is producing, which is another plus (I know there are other ways to get it, but this tool makes it easy!) – With the MDX you can just copy it and run it in SSMS to see the results there. You can see how Excel is doing things behind the scenes with your result set to make it look nice.

Another sweet feature, if you have a cube with tons of attributes, there is a search tab to search for the attributes you want.

I haven’t seen any issues yet. One user had to install the Visual Studio 2005 Tools for Office Second Edition Runtime which the CodePlex site says is required, so no big deal.

If you have tons of users using OLAP Cubes with Excel 2007, take a look at this free open source tool on CodePlex, you probably will get some good mileage out of it. I think Microsoft should put these features in the next version of Office!