Grant Paisley
SQL Down Under Show 53 - Guest: Grant Paisley - Published: 22 Jan 2013
This show features SQL Server MVP Grant Paisley discussing PowerPivot, Power View, SQL Server Analysis Services Tabular and Multidimensional Models.
Details About Our Guest
Grant Paisley is a SQL Server MVP, SSAS Maestro and founder of Angry Koala. He is a Microsoft Gold Business Intelligence partner based in Sydney Australia. Grant is the president of the SQL Server User Group in Sydney and created http://www.reportserver.com/ a community reporting site. His books include SQL Server 2008 Analysis Services with MDX, Microsoft SQL Server Reporting Services Recipes and SQL Server 2012 Reporting Services.
If you don’t see Grant on stage at a user group or Tech Ed in the US, you might see him on the water kite surfing Hawaii, Downhill Mountain biking in Whistler or playing tennis at home with his wife and three teenage girls. Grant is also recently joined the Rural Fire Service matching his infinity for adrenalin exploits with the community.
Show Notes And Links
Grant's website is here (http://www.angrykoala.com)
For a short video that shows many of Grant's adrenalin-related exploits, watch the first four minutes or so of his TechEd session (http://channel9.msdn.com/Events/TechEd/Australia/2012/DBI224)
Marco Russo's whitepaper on modelling many to many relationships is here (http://www.sqlbi.com/articles/many2many/)
Show Transcript
Greg Low: Introducing Show 53 with guest Grant Paisley.
Welcome, our guest today is Grant Paisley. Grant is a SQL Server MVP, SSAS Maestro and founder of Angry Koala. He is a Microsoft Gold Business Intelligence partner based in Sydney Australia. Grant is the president of the SQL Server User Group in Sydney and created http://www.reportserver.com/ a community reporting site. His books include SQL Server 2008 Analysis Services with MDX, Microsoft SQL Server Reporting Services Recipes and SQL Server 2012 Reporting Services.
If you don’t see Grant on stage at a user group or Tech Ed in the US, you might see him on the water kite surfing Hawaii, Downhill Mountain biking in Whistler or playing tennis at home with his wife and three teenage girls. Grant is also recently joined the Rural Fire Service matching his infinity for adrenalin exploits with the community. So welcome Grant!
Grant Paisley: Hi how are you going?
Greg Low: All good, and so what I get everyone to do when they first come onto the show is tell us how did you ever come to be involved in SQL Server?
Grant Paisley: I originally was using, I was building a data warehouse before I knew what a data warehouse was. Optus as it turned out a couple of years after they were instigated and a woman came along and said “I have got this great thing called Power Play which is a Cognos produc” and I said “I can work with that” and thought that was fantastic. Worked with them for about 6 months and wound up doing a bit of Power Play Cognos work and then I saw a presentation done on Analysis Services and said nah this is much better for an Enterprise instance when you do a product dimension. There is only one product dimension instead of several of them with Power Play.
Greg Low: Yes.
Grant Paisley: So basically I took one look at that and said that is the future and got stuck into that on a project. The first one was with Travelland and building a system for them using Analysis Services and reporting things. It was a lot of fun; unfortunately the arrows were red and pointing down. Not long after that Ansett went under and so did Travelland. It wasn’t for lack of information we were given them anyway.
Greg Low: So you knew why they were going down.
Grant Paisley: We helped them with the why?
So look, passionate areas at the moment, oh I should also mention you noted the Rural Fire Service and when I did get to visit your place recently I noticed you were doing the Rural Fire Service thing with the hoses and all that. Given the fire situation in Australia over the last few weeks, what is the situation in your area?
Grant Paisley: We are on pretty high alert twice I have gone down to the station and waited around during the day just in case something happened. In fact I have my first real experience because I have just recently joined last year. It was actually a trailer fire across the road and it was on Friday which was the hottest day ever recorded in Sydney.
Greg Low: Yes wasn’t it amazing.
Grant Paisley: 46 degrees.
Greg Low: 46 wow, so yeah. So what’s that in Fahrenheit for the US folk?
Grant Paisley: Uh, jesus, well over 110 or something. I don’t know.
Greg Low: Yes something like that. I do remember the worst day we had in Melbourne since I have been here was 46.4 and again that was like 117 or something. Just walking outside it felt like walking into an oven. It was just an incredible feeling.
Grant Paisley: It was also very dry as well, so it was literally very much like an oven. What was interesting was this was my first experience of full on responding to a fire. It was actually a trailer full of wood in a car port across the road and from the station we were there with the other guy that was there. The mission to respond and we are not able because we didn’t have an OIC and we were waiting to go. Then he said “you have permission”, so I raced across the road and rolled the hoses out and hat was falling over my eyes. It was a bit of a comedy of errors, I got the fire out. I was fun; it wasn’t hot at all even though it was the hottest day on record ever. Because I think with the adrenalin absolutely pumping when I came back to the station, I was dripping in sweat. When it really hits it will be pretty wild.
Over the last few years we have had a lot of rain so there is a lot of material in the bush and there are a lot of hazards.
Greg Low: People see the fire as some sort of unnatural thing. It is not, it is just part of the natural cycle.
Grant Paisley: Yes you want it to burn over 7 or 8 years for the Australian bush likes that. If you don’t do it longer then it burns too intensely and creates problems. So anyway….
Greg Low: You don’t get the fertilizer back into the soil from all the back burning. So that is wonderful, so in terms of SQL Server mostly what I have seen you presenting on in recent years has been around PowerPivot. Now a lot of the folk who listen to the show probably not totally across PowerPivot so maybe if you could just start with some details, so why does PowerPivot matter to SQL Server people?
Grant Paisley: Power Pivot matters because it is a tool that really gives your insight to use data very easily. Particularly from a SQL professional point of view you maybe haven’t come from a BI point of view. It is much closer particularly to cable and relationships like that. It is what it is. It is tabular the way it works is, it is a in-memory store when the data loads up, it loads it into the memory and compresses it right down. Literally from a SQL point of view, you are in data the log type data or performance data or hours worked or really anything, you can load it up straight into Excel. It is a free add in, you download from Microsoft and you use Excel 2010 or 2013 it is built in, it is already rolled in. Basically grab the add ins and connect to the data, suck the data, create the relationships and then the way it is expressed in Excel is through Pivot tables, so hopefully you will have experienced. Listeners would have played with Pivot tables. It is like having a Pivot table on steroids because when you slice and dice the Pivot table it is actually acting against the in memory tabular store. So it is very, very powerful.
I did some work, or in my demonstration I run through some clients. One of which was an organization that does a few million transactions, literally they took weeks to actually start at the beginning. Effectively the government in Victoria was going to take ATMs out of gambling establishments because gambling is in pubs is still very popular in Australia. It appears it might lead to gambling issues because I asked the people that look after the ATMs how much money are gamblers taking out per day on average?
In order to do that they had to load up all the data, aggregate it by day, card, location and all that sort of thing and it took them weeks to try and do all of this. It was difficult and they probably didn’t have SQL skills also. Literally pulled all the data in, 35 million rows, 7 months’ worth of data, every single transaction got straight into the system within a few hours we were able to give those sort of answers very quickly.
Greg Low: Time tool solution is probably a really big issue.
Grant Paisley: Yes I think where SQL professionals have a huge advantage, we understand data. The thing I like coming from BI background is Analysis Services is the traditional, multi-dimensional. It is probably more mature if you like, it has got some few other things you can do but you can get to the I guess the 80:20 rule. I think more like 90:10 or 90:55, very, very quickly and very easily and as good as the traditional multi-dimensional cube with a small amount of effort because the traditional stuff is quite fiddly. It is annoyingly fiddly, and you have experienced this Greg.
Greg Low: Oh yeah.
Grant Paisley: It gets in your way probably more than Power Pivot although Power Pivot when it is inside Excel I have always found it quite frustrating. I have actually now moved to doing a lot more with the Tabular database.
Greg Low: So designing in Visual Studio and deploying against Analysis Services tabular in the back end?
Grant Paisley: Correct. From a user interface point of view you are doing pretty well identical selection. So what you are doing in Power Pivot, the trouble with Power Pivot in Excel is that it is so chatty to Excel to itself and when you keep making modifications and changing things that says it goes to Excel. Oh I just made this change, so hey I better check all my sheets and see whether that screws all my Pivot tables up. Eventually it comes back via heh that is ok, you can do that. When you try and doing very quick iterative model changes it becomes very frustrating, very quickly.
Greg Low: Yes.
Grant Paisley: The first pick if you like is, if you are doing development work when you are developing the model in Excel that is fine but don’t have a bunch of Pivot tables actually hooked up to the model while you are developing just have one or two. Otherwise you just get a big, and a lot of frustration.
Greg Low: I did find that I managed to break the designer quite a lot more in Excel than in Visual Studio as well.
Grant Paisley: Yes, I really think it is Excel’s fault if you like.
Greg Low: Yes.
Grant Paisley: In defense of Excel, it is a very complex product and very complex relationships when you are changing a database in underneath the visual layer. I think the issue is really in that environment you are combining the visualization with the data and I think that you will have an issue.
Greg Low: Yes.
Grant Paisley: Well that is a two edged sword, one thing it is fantastic because you will get things up and running really very quickly with reports. The bad thing is the reports are in one place instead of separating them out.
Greg Low: Yes, no indeed. So the changes for Power Pivot itself in the latest versions?
Grant Paisley: Yes, the latest versions for instance in Excel 2013 is actually baked right into Excel 2013 which is really nice. One of the big ones of course is mapping, it is built into Excel. Literally you just need the name of the suburb in there or geo location. Long and lat basically and throw that in. We did some great work with one of the car manufacturers here in Australia. One of things that car manufacturers do here is that they give all their data back to a central organization and that central organization then feeds that back to all the other car companies. The car companies know exactly who sold what, where. Basically we grabbed that data, geo coded the locations and then we are doing market share analysis with pie graphs which I hate except unless it is just with two segments. With two segments it is good for example with Ford and with Holden and I can drill into New South Wales and I can do different regions in New South Wales and then start slicing.
Well how did we go in the small car market or the medium size or SUVs and just really look from a location point of view just what is going on. And it is very, very quick to knock together. In this case we built that in this case in Excel 2010 and we published that to SharePoint once it is in SharePoint there is a thing called Excel Services which is basically a thin browser version of Excel. They are now consuming that information directly on their intranet.
Greg Low: So we should probably talk about just the different flavors of this that are available. So first up we have Power Pivot which we are saying is the free add on for Excel.
Grant Paisley: Yes.
Greg Low: So what does that provide you?
Grant Paisley: So that provides the Tabular engine in background, when you are running it, really it’s a program running in parallel to Excel and you will notice that when you basically click on the Excel icon all that is happening is it is just going into the Excel window. It is jumping between the two environments.
Greg Low: Yes the two of them. I often see it like a parasite. It is sitting there running in Excel almost unaware that it is there almost.
Grant Paisley: Yes, you are caught unaware sometimes. Literally that is a free download and away we go. So that is the first version and that is the one that the lowest hanging fruit if you like because literally you just download it and as an add in and away you go.
Greg Low: Yes.
Grant Paisley: What other versions? We have talked about the baked in version in 2013 so that is really just.
Greg Low: Well I suppose in 2008R2, all we had was the Power Pivot one, but then if you wanted to share your workbook with somebody else the only option was really the available was either to send a workbook or to start using Excel Services so maybe just mention what that is?
Grant Paisley: Right, so basically. One other thing we haven’t mentioned so hopefully is obvious to you is this download package is free. Which is a nominal gift if you like, of course the reason they have gifted this wonderful tool is with a view that sharing. If I wanted to share with two other people, these workbooks will be a few hundred MB. It is a bit hard to share that.
Greg Low: Yes.
Grant Paisley: So what we do have is the version that runs on the server and what is the technical word for it? Greg you are probably better at this?
Greg Low: Well I was going to say the first version of that for 2008R2 was basically we had to push it up into Excel Services and they had basically an engine that ran inside SharePoint that was able to. Excel Services normally allows you to see Excel spreadsheets in a browser but it didn’t have the bits to deal with Power Pivot and I suppose what you are really buying SQL Server licenses for is the bits that allow you to deal with the Power Pivot parts of the workbook inside there. But yes I think a lot of people when they were doing this sharing didn’t necessarily want to go down the SharePoint path necessarily and I think the other big criticism was it really didn’t have a very good developer surface when sitting inside Excel.
Grant Paisley: Yes.
Greg Low: Yes so now we have in 2012, we are saying for Visual Studio or SQL Server Data Tools we now have got the tabular designer for that and how have you found that?
Grant Paisley: I was struggling because for a project I was doing and playing in the Power Pivot world and having the usual model change conflicts sort of blowing up half way. Not what we except in terms in experience if you like and my mates Glyn Llewellyn which you know as well and he is my general manager he has already gone tabular. He was actually jumping in tabular and doing all the stuff and putting it back into Power Pivot. Eventually I say the light and now been doing back into the tabular model.
Greg Low: Yes.
Grant Paisley: It is nice and touch closer to what we are used to when we are doing Analysis Services. You know it is very, not that different to SQL Power Pivot. Don’t be too scared by the fact that you are inside Visual Studio. The actual the way you are operating is pretty much the same.
Of course the next feature wants to do that as well, we get security. You can get roll back security which is pretty unique. There are other things which I can’t think of. What else? I know the
Greg Low: Oh yes things like partitions and so on and indeed.
Grant Paisley: Because basically in Power Pivot you needed to do a full refresh, one of the disadvantages is you are always pulling in every bit of data now. This day and age particular if it is on your server somewhere that is not often that big a problem, particularly if you do it overnight or something. Yes but having partitions allows you to partition by day or month or year or whatever it is.
Greg Low: That actually raises an interesting issue too. Even though the Power Pivot add on is free, you could have people all over the building running their own spreadsheet. The last thing you really want is everybody refreshing the spreadsheet with enormous amounts of data across the network.
Grant Paisley: Yes there are a few things I guess from the IT point of view. One of them is when you are setting up a SharePoint environment and you have got some of these Power Pivot workbooks you basically have a blessed library. The blessed library is where everything is checked and you know it is all running, basically scheduled overnight and all those sort of things. But then what you can do is have user play areas if you like. This is another library where users actually upload their Power Pivot spreadsheets and share it with others. There are tools within SharePoint which allow you to have a look at what’is being used by how many people querying it etc. If you keep an eye on that you can say hang on how did this user group do this? This one is becoming really popular, maybe we should bring it into the blessed area.
It is a bit like the user out there actually doing a bit of prototyping for you but I think coming back to. For SQL professionals I think our role is becoming more of data governance if you like or giving people nice clean data, you know here is the nice transactions.
Greg Low: Yes.
Grant Paisley: Here are the customers there ready to go. One of my things that I get on my horse about is users don’t actually want to create these complex models in general.
Greg Low: No.
Grant Paisley: They just want to do what I call decorations and decorations is I have got data for New South Wales and Queensland and Victoria I want to create something called Eastern seaboard and Western seaboard or something. In other words all they are doing is grouping things at higher levels. It is just decorations of little things they want to do and Power Pivot allows you to do that really, really easily. Literally you create a set of data, in fact I have done this myself where I have got the core data and some sort of customer information or something that appears. I pull that back into Excel using the Pivot table and I copy that into another sheet, say ProductIDs or whatever it may be and I create my own column and create my own grouping of those things and I push that back into Power Pivot again, link it back to Product table in this case and now I have got some news ways of grouping it together.
In fact we did some work for, what’s the retail chain called Sports. What is that called?
Greg Low: Not sure.
Grant Paisley: Anyway, basically they were bought by another company they needed to get rid of a whole lot of stock so we bought in all the inventory and how old the inventory was and then in Power Pivot they had a spreadsheet that listed the categories of products and discount schedules. So if it was over three months then 30% and if it was over 6 months then 60% and whatever so they could vary the schedule of percentages. All you did was back in using a DAX statement which I have spoken about. DAX is the language that you use to speak to the backend, very Excel like purposely tried to make it as Excel like as possible.
With a few exceptions in general that will port a whole lot of things across, so we used some DAX statements and all we did was look up. You know I have got this product and I have got 5 of them and I go ok it is in this category and you know it is 6 months old so therefore I apply a discount 60%. I now know the tally of that stock in terms of discounts regime and also I can bring in the sales over the last month to see how much of the stuff has been sold down because of these discounts and what money they were making or losing or whatever it must have been.
That is I guess a bit more sophisticated decoration but really I think our role is much more. Getting the core data in order for our users. Really enable them to.
Greg Low: Yes I tend to agree. A lot of the material I read from Microsoft first suggested that users would be the people who could easily build models and things and that is just not my experience at all. I find that they typical user doesn’t want to know about that. Even a fairly power user doesn’t want to know about that. Invariably they want us to build the model even for fairly simple things and then they will add to it. They will add additional tables maybe occasional; mostly they want to play around in the spreadsheet end not in the model end.
Grant Paisley: I absolutely agree with that. Some of their enhancements, I love to see in that space is the ability to build the base model and users not being able to change but being able to do decoration outside that base model. I think that would be like an Object Orientated sort of environment. I think that would be very powerful for instance you could have a tabular backend which has the model and everything and some sort of Power Pivot front end that it inherits the core stuff and then does the decoration of new tables and manipulation and things like that.
Greg Low: Yes, look I suppose you can kind of do that now, so the I suppose we should mention the idea of being able to design this in Visual Studio is that we now use Analysis Services backend so the server now does the refreshes and hold the golden copy of the data and all of that. Rather than the users and in that case it the tools at the front end like Excel just becomes sort of client tools. So Power Pivot we should mention also in Excel can connect to tabular models or even to Excel workbooks with Power Pivot sitting up in Excel. You can when you are running Excel at the front end connect to a source of a model at the backend.
Grant Paisley: Yes, so the beauty of that is that in fact for 99% how my users worked in Power Business Intelligence environment that we created belong to the multi-dimensional. That was the server which had the cube in it and we had Excel with a Pivot table and the Pivot table pointed off the server and gave you the data. In that case you can slice and dice the data. What we are talking about is you can do that directly against a Tabular model exactly the same way as users and same language as others but you also have the ability to use the Tabular as a data source to Power Pivot. You can actually bring in the data and do some of those more sophisticated decoration I guess is what they would do.
Greg Low: Yes, no indeed. I find as I said the other thing I do notice, as you said the thing about time to market. We have had projects, I remember one, two very, very similar ones where people had literally spent years trying to build websites to expose data to their core clients from within their organization and literally building. The first case we did Power Pivot workbooks and pushed them up into Excel services and literally the whole project took 2 or 3 weeks. What was presented to the users and they far preferred that to anything they ever had out of the website that they spent years trying to build.
I think I have similar thing at another site where there was a tabular model, again with exactly the same sort of thing applied. It was amazing how quickly a project came together and got beyond the point that they had previously achieved with a whole lot of effort.
Grant Paisley: Yes the path that I found that absolutely, we had a client that we have done very similar things where there are more, more situations now where the organizations try and share some of their data with their clients.
Greg Low: Yes.
Grant Paisley: We are getting better fits of that now. The one that got me and tantalizing close to what the sort of things I want to do is the new Office 365 environment.
Greg Low: Yes.
Grant Paisley: Literally like I was on the TAP program Excel 2013, I did my Power Pivot thing and that was all well and good. Then in 2000 environment it was saying in the shared environment do you want to share to your Office 365 setup? Save it against that, I said yes ok.
Greg Low: Yes.
Grant Paisley: It did the save and then it popped up and said oh did you want to share this with anyone. Give me a couple of email addresses. You got to be kidding me, oh right so I gave it to a couple of my guys and their email addresses. They received an email and when they opened the email and clicked on the link it takes them to Office 365. They logged in and not only can they open up that spreadsheet and download it onto their own spreadsheet and actually in the thin browser version which is the Excel Services type of thing in Office 365 they have got flexibility. They can slice and dice it was absolutely fantastic, that sort of good stuff the limitations I think. What is the size 100MB I think from memory?
Greg Low: Not sure on the size of.
Grant Paisley: Yes Office 365 limited it to 100MB, but 100MB compressed data is actually a fair bit of data.
Greg Low: Yes it is a lot of data.
Grant Paisley: Yes it is a lot of data, so
Greg Low: Yes I have found, it was interesting when Donald Farmer was running around doing the initial demonstrations of Power Pivot and he had his movie database that had about 110 million rows in it. It had a lot of data about each hiring and when he saved it. I mean it impressed me it was 186MB, I mean that is still a sizable spreadsheet but what was interesting it was one and half bytes per row average and given the fact it is Unicode data. We are talking less than one character average per row was storing the entire row of data, so the degree of compression is really something to behold.
Grant Paisley: Yes, I should point out that very much like all Microsoft products in this environment; it is very easy to add lots of columns of information in tables. I would like to point out that very loudly and clearly that don’t do that.
Greg Low: Yes only bring in the things you need. I think that is something that is hard for people to wrap their heads around too. Was that in other tools like for example if I declare a calculated column in SQL Server I can choose it is persisted or not but when I add a calculated column in Power Pivot or in the tabular model. That is alright, it is going to become persisted data inside that model.
Grant Paisley: That is certainly the case. There are advantages and disadvantages, the disadvantages are you actually got a bit of extra space taken out. The advantage through and this is one of few pieces of advice here is tabular environments is that often calculations which is very difficult in the multi-dimensional environment because by nature you are always slicing from the top down and grouping things together. Where as if you do a calculated column the only time that calculation is done is when you first doing a model because as we have just discussed we have actually stores the information physically in the model.
The disadvantage is it takes so little space; the advantage though is it is in the model so it is not having to calculate and so you get his phenomenal speed of things when you are doubling up. Which you have created it in the model.
Greg Low: That is right, you get that some compression occurring on the calculated values as well because it is stored as part of the data.
Grant Paisley: Calculating which I think as a general thing was a distance as bad as bringing in for instance the Product dimension or Product information and have a 150 columns. It is like if you can, and our nature is to help users and giving them helps and everything and they can do anything they like. You do screw them up because the whole model is fairly slow because everything is being stored column wise. And two it is going to be really hard to use because there is going to be so much stuff in there. It is going to be really confusing. The secret particularly when you are new to this stuff and the users are new to it is put in the apps is the absolute minimum. If you put in the absolute minimum it will be far easily to reload and the main thing is if you could use it. The users will say when they are using it and once they are confident and they start understanding how to do Pivot table in Excel and all those sort of things we are now in a situation where I can now do maybe a few more complicated things. I might point out about perspectives which exist in Power Pivot and also in the Tabular environment.
Greg Low: Also added in 2012.
Grant Paisley: Yes added in 2012. Basically it is a like a view on a table, literally when you are creating new perspectives it just gives you a list of all of the things that your model is used to. Tick the things that you want to expose, so we use that a lot. So we will create a Sales perspective which just has the name Sales measures and just main sales dimension attributes if you like. The beauty of that.
Greg Low: And not from a security point of view but just from an ease of navigation point of view.
Grant Paisley: Yes be careful everyone sort of thinks this is a correct way to secure my cube but it doesn’t work like that unfortunately. This from an ease of use, so if you have got financial data as well sales data, as well inventory data then you can get a source and not quite quickly and you start picking things from finance and slicing by something in Sales which has nothing to do with it. Then the numbers start repeating and it can get quite confusing.
Whereas if you create a perspective for example a P&L, something from sales then there are two things. One is it is easier to use because things make sense; you are also cutting down on the volume of data in terms of attributes and things and so again. It is easy to use, and so again it is easy to use. An easy way to do that way is for your really power user really does understand if you can actually open up the whole thing so that they can do combinations and they become a calculation like you know how much money they will make per employee?
Greg Low: Now in terms of we briefly mentioned before DAX, so maybe just defining what DAX is used for?
Grant Paisley: Ok so DAX is used in two ways, bottom up or top down. Bottom up is when we are talking about creating a new column. For instance if I had a sales cube and I had a how much somebody paid for something and I had very cleverly faked how much it cost in that particular row in my transaction and in this case an item detail row. I could do a profit by simply bringing in the tax calculation which had sales minus cost of goods sold.
Greg Low: Yes.
Grant Paisley: That is the simplest DAX calculation you could do.
Greg Low: Yes, this is creating a measure literally in that case and we mentioned calculated columns before as another possibility.
Grant Paisley: This is a good example I hope this will not be too confusing. In this case if I am doing it in a model in a column and created new column in item in the Sales table.
Greg Low: Yes that would be a calculated column.
Grant Paisley: Calculated column and then when you are in Power Pivot, if you brought DAX into the data area then it would do some of profit and that would give you a profit. That would become a measure so a measure is defined either on the fly when you drag something in that you can add up. For instance Account or Sales or profit or you could define your own. When you define your own, again that is using DAX so for instance if I want to create a Profit measure which is effectively Top Down then I will say Profit equals sum of profit column and that would give you that information. Now something to keep in mind, if you do a Tabular model and that is sitting off on the server and you connect to that using Excel then Pivot table it can’t create a measure on the fly. So you actually have to preempt it by creating measures for the users. So you create a Sales measure which is a sum of the Sales column and things like that. You actually have to prepare that. Which I don’t think is a bad thing anyway. I think I like doing that.
Greg Low: Yes I think it is a good idea to have how those things are done and defined at the server whenever you can. Otherwise you can easily end up with everybody in the company has their own formula for working out profitability or something like that. You don’t want that.
Grant Paisley: Then you can get into more and more complex calculations because the environment is in memory. You have got the advantage, it can ripple through nearly every row and do calculations on the fly and double them up on those sorts of things. One example which is a medium context if you like is something I used quite a lot now. That is why when you are trying to work out what is happening at a point in time. For instance this client rented out equipment, computer equipment inside that and software and all sorts of things. So they had rent start date and they had a end date where they finished renting and handed it back. So basically start date and end date, what you could do is you could create a measure which basically says the count at that new point in time. So your inventory if you like or what you are renting out and that just says give me everything, add up every single row also count the number of rows where the date that I happen to be slicing by in my date dimension is greater than the start date and less the end date.
That is actually a bit tricky in the multi-dimensional environment and it takes all that reasonable quickness of superfast. That can be applied to project and what projects I have got running at the moment. Servers what is actually running at the moment or not running at the moment with the customers and all that sort of stuff and it is pretty cool.
Greg Low: It is interesting some of the things that are different in terms of the speed. I noticed for example distinct counts in multi-dimensional models are always part of the pain level. They are the sort of thing in the Tabular model so fast.
Grant Paisley: Yes we have to clarify that a little bit. The underlying structure for the distinct count is absolutely brilliant in Tabular because effectively it is calling the storage and calling the storage is basically finding the distinct values.
Greg Low: Yes that is right.
Grant Paisley: That is what it does!
Greg Low: You have already got the answer.
Grant Paisley: Yes, you have already got the answer. So the engine is phenomenal at doing distinct counts of very large numbers of dimensional and things. The challenge is the model doesn’t support multi-values natively and that means if I set up a set of relationships between a bank account and several and the customers and my wife. Both customers that have that bank account, so if we put customers down the side you want to see my balance and Sue’s balance and both appear to have the same number. The bottom of those two things are added together, now the model that support that it does in multi-dimensional you can log all that just works.
What you have to do in Tabular is you have to create a specific measure and that measure traverses relationship the gives you the distinct count.
Greg Low: The modeling in particular will give you many.
Grant Paisley: What I am talking about is many-to-many not distinct.
Greg Low: In fact Marco Russo has a wonderful article called the Many-to-Many revolution and two of that out. It took specifically about how to model a whole lot of things in Tabular models. I might put a link to that in the show notes as well.
Grant Paisley: In fact one technique that gives you another way of looking at is if you like because it is so good at distinct count is what we have found with a few of our clients is we have created a table which is a cross join and the values. For instance you have got teachers and students and classes, if I do a cross join of those things and create a big table with all the combinations and run distinct counts on teachers and students and classes and I can do any combination and I can create a teacher and see how many students that have or what classes or group of classes teachers are looking up etc.
One of the these things that I guess is a little bit, is in Tabular we tend to use a little bit over the years. We will sometimes basically do cross product, cross tables and then use a distinct count to give you information you need. It is quite cool.
Greg Low: One other little change I should mention too, is in DAX 2012 it became a query language as well where up to 2008R2 it really was just defining measures and calculated columns. But now also become the Evaluate verb and we can now issue queries which is what some of the tools do, so that is leading us to perhaps Power View is the next one. If I look at front-ends to Tabular models, Excel is the obvious client.
Grant Paisley: And Pivot tables in Excels.
Greg Low: Power View was originally a way of analyzing that in Excel services in SharePoint and Silverlight based. One of the big changes in Excel 2013 is the inclusion of Power View within Excel.
Grant Paisley: Yes, so basically Power View is the wonderful visualization tool. Very easy to put information together. Having said that, it takes a little while to get your head around some of the relationships that is going on when you are on the canvasing doing things. Because you have got the context, am I filtering this chart or am I filtering the whole page if that makes sense. There are a few things but you get the idea by just playing around which you sort of get at. Really it is a wonderful environment to users. We get huge uptake when users tailor this to exactly what they want to do. They want to just click on a slicer and see all the information changing. This wonderful environment where if you set up bar graphs for instance, if you click on bar graph for I don’t know customers and you have got all these products, then if you click on the customer you can see what influence that customer had on the product information.
Greg Low: Yes.
Grant Paisley: Now, this is one of the ticks if you like. If users measure which ratios rather than absolute values then you can see things over, and under indexing.
Greg Low: Yes.
Grant Paisley: As ratios and we have done that a bit now and very, very powerful. In fact I must publish something we did with public information which is the data from all the universities about the quality of the courses and now every few years and now rated 1-5 about quality of that particular course. This is all public information as well. Awful when you are trying to extract it, we felt we actually brought that into Mark Fitzpatrick one of our guys brought that together in Power Pivot and we must actually publish that somewhere because people can have a look because even from my door it was estimating.
Greg Low: Yes useful information.
Grant Paisley: The point of that is that we have used indexing things, they can click on a particular university over or under performing and in particular areas. Very, very cool.
Greg Low: Yes I think Power View is an interesting visualization tool and it is kind of interesting people always like working with the live data. Again the big thing worth mentioning though too, is the experience somebody has in say Power View also goes back to how well you done work in the model. They added a whole lot of reporting properties and things to the model that make life better in Power View.
Grant Paisley: Yes it is a very, very good point. Especially when you see these particular and something you eject something on and all of a sudden a photo there and it is all formatted in the right way. With the main of the product.
Greg Low: Yes.
Grant Paisley: That is because under the covers, the model itself we have flagged particular attributes with this is the photographs. This is the main thing we want to see when we are creating long we say.
Greg Low: Yes.
Grant Paisley: It is worth while looking into that to the users impacting those sorts of things.
Greg Low: To be that is the sort of thing that completely changes the experience for somebody using Power View. I dragged this table onto a report, geez some column suddenly appeared, how did it know which columns to put there? Again that is all defined in the model.
Grant Paisley: Yes Power View is extremely visually compelling users just love it. The two things which also give us a look is a couple of things. One you probably do want to get things where you don’t have a hundred thousand attributes and try and drag those in because it is just too many. The advantage in the Power View is they don’t handle it really quite over a hundred thousand. I will just show you the first few and tell you that there is more. Whereas in Excel environment, you can get into pretty deep water pretty quickly by hooking up to large amounts of information.
Greg Low: Yes, it is worth mentioning too that Power View is a tool that issues DAX queries, at the moment the Tabular engine is the only one that does DAX and at the moment Power View is really only going to backend into the Tabular models although that may well change in the future.
Grant Paisley: Yes, that is going to change, it has been announced that they are going to support that.
Greg Low: Oh they did actually announce it, oh good.
Grant Paisley: We are allowed to say it, we are allowed to say it now Greg. We have to be careful Greg and I, it is public that it is going to happen now. It will support, there will be some limited support in multi-dimensional but you will use Power View against multi-dimensional and of course the Power View as you mentioned is built to Excel 2013 and again as I said in Office 365 you can publish with Power View reports into that environment and people can log on and start slicing and dicing using Power View which is insane. Such a wonderful environment!
Greg Low: The other one I find Power View is interesting is the way you can also publish into PowerPoint as well for presentations and I have found a number of government and large corporations just love the idea that they can slice stuff like that into their presentations in PowerPoint and including even things like animation continue to work.
Grant Paisley: That is worthwhile pointing out that it is brilliant and that works really well. It has to be in SharePoint in that situation. You can’t do that in native Excel. You have to publish within SharePoint and once it is in SharePoint then you can do that.
Greg Low: Yes.
Grant Paisley: The PowerPoint there are too many powers.
Greg Low: For those that are using Reporting Services of course we can just hook that up as well?
Grant Paisley: Yes, it is basically, it can be used as a source that I can connect anything else. Particularly it is very fast. You can create some pretty quick reports because it is going to hook in and get launched by this. One thing I would like to point out.
Greg Low: I was going to say Report Builder as well, same deal there.
Grant Paisley: Yes of course, I guess more user again a little bit more user friendly. One thing as a tip if you like one of the things uses often lot to do and it would take multi-dimensional if I try and create more like a table report where here is my Customer, here is my CustomerID this is the Product they brought and basically. A lot of the users use the same dimension like if you liken the information and they turn the sub totals off and things and under the covers Excel is treating it as though you are trying to nest those things together. It doesn’t take long for you to pull a few attributes which you think wouldn’t cause any issue. All of sudden you have got very slow running reports.
Just keep in mind the power that you can get via a DAX query directly against Tabular. You can create more of a Tabular experience by creating your own query and pulling that into Excel table.
Greg Low: For those that look at lower level things, like MDX travelled inside XMLA, the XML for Analysis Services DAX is just another language that travels inside that same wrapper to head off to the server.
Grant Paisley: Yes.
Greg Low: So what do you think is still missing that you have in multi-dimensional models that you so wish you had in Tabular models?
Grant Paisley: There are a few simple things like closing balances. When you do an attribute in multi-dimensional you can say this is a closing balance. For instance inventory, you want what if it has accumulated in time. The inventory level is what it was at the end of that month. You go and add up the inventory snapshot for the month.
Greg Low: Yes, exactly.
Grant Paisley: So there are a few things like that. The other thing else that is questionable is that in Redmond is really getting some wizards in there to help users with standard issues like last year and all those sorts of things. Again there are some tricks we can do for multi-dimensional like we can do that generically we can sort of do similar tricks but it is a bit trickier than in Tabular.
What else specifically, I guess the many-to-many is a big one.
Greg Low: Many-to-many?
Grant Paisley: Yes many-to-many is a big one. In fact what I would like to see is actually simple version of the many-to-many released which is like a tagging system because that is what I see many-to-many used for in most cases.
Greg Low: Yes very commonly.
Grant Paisley: Yes this customer is both a big customer and in this particular.
Greg Low: Yes I have got business categories and they are in 4 of them. Yes.
Grant Paisley: Yes, Yes.
Greg Low: Yes that is good. When you build the models do you still build an underlying dimensional model?
Grant Paisley: Yes we absolutely do and that can either be real or it can be unreal as it is used. We find that works pretty well.
Greg Low: Yes I was going to say every, it is interesting the demonstrations. A lot of things I see published tend to suggest one of the nice things with all of the Tabular stuff is you can just layer it over the top source systems but I think they are missing a large part of the point about why we didn’t build cubes and things over the top of those in the first place.
Grant Paisley: Yes, absolutely. The reason that I love Power Pivot and Tabular and things like that and it is the same as you with we understand how to prepare the data and get it running. There is a simple example with one of the demos I did was Shark attack victims across the world and I built that, grabbed a single set of data and pulled and said it probably is not fair really because there are different populations in different parts of the world. So what we really wanted to do combine that with population data. So I ran off and found on the internet somewhere that had the population of various countries.
Now for me in Power Pivot and Excel to get those two bits of information working together was the pains to create mapping tables because the names and differences of the countries and one piece of information would in decades of course the other one was the year level. I had to sort out the decade table the amount of effort to try and size of Excel and Power Pivot was horrible to be frank. Whereas if I brought it in and had it all cleaned up and all Power Pivot did and say if we just loaded it straight in it would work.
Greg Low: Yes that to be it is a really big thing. As I said I see this sort of message all the time but I think it is the wrong message. I still like the idea of having the underlying model that is tidied up and is designed for analysis. I just see these tools as analysis tools that sit over the top of it, same as reporting, same as everything. It still is a big job in terms of making that data look right in the first place.
Grant Paisley: Yes, maybe between us Greg we coin a new term like ‘Design and Decorate’ something I don’t know. Basically yes we need the Analytics platform and decorate. I think that is the key. Decoration is either adding our own groups information or adding our own data from other places. It is certainly a subset of data not the core data at all. It is just I want to compare what the average earnings is around the employees in other organizations compared to my. It is never going to go down the ware house.
Greg Low: Yes.
Grant Paisley: But I have got things for my employees and I want to compare it. So that will be a good, a bit more decoration than normal but to be it is the decoration.
Greg Low: Look so a final tricky question is if you are starting a new project now, do you start thinking Tabular or do you start with a multi-dimensional model or how do you choose?
Grant Paisley: It is a very good point, we are actually got a new client right this second and gone in and done our proposal and our first part and it is a larger organization. We are proposing to do both on the basis that well for this particular client is a bit unusual, we are not quite sure which of the most useful. But certainly I lean towards Tabular much now. Just on the basis that it is easier for users, support and understand and it is quicker to build. To a certain extent, it actually doesn’t matter because most of the work is actually user data at the other end.
Greg Low: I am so glad to hear you say that, because I find for most of these projects I find probably 85% of the work maybe is getting the data in right shape and getting the integration work happening so the data stays in the right shape. Once you get the data in the right shape the analysis and reporting is a dream by comparison.
Grant Paisley: You can use a combination of things. You can use third party tools, you can use Power Pivot, you can use traditional. You just need to sort that part out first; it is certainly not a case of having to choose one or other I don’t think in this day and age.
Greg Low: I think one thing I stress though is that I am also not a fan of doing all that integration first necessarily. Often I get people who say look what order do you do the projects in? I don’t know if you do the same thing? What I kind of like to do now is I like to use something like Power Pivot to do an initial proof of concept modeling and to suck in a bunch of data so people can start to play with that. Once I have that in place I can then start to find out if the user would be able to answer the sort of things they will be able to answer right up front.
Once I know that I then have an idea what the model should look like. Iterate that and get it to a point with a bit of dummy data where people can get a good feeling for that and then somebody can go on and do the analysis work whilst somebody does all the integration.
Grant Paisley: It is interesting you should say that, we go in after our sprint zeros if you like. We work in three week sprints at the end of that week we have a pretty cool concept that in Power Pivot that the users can actually play with. When they actually see it, this is what you mean and you know when you are playing with real data and everything basically you very quickly iterate what the model is going to look like. The thing to stress here is it is easy to do because you just take a snapshot of data and away you go in order to make that repeatable is a lot of work to do. You have got this beautiful tool in Power Pivot where you can very quickly prototype really what is a working environment at least an initial one. Then you go back in and make the data look like that in a repeatable fashion.
Greg Low: Yes exactly. I think the other thing is as you say. It is not then a decision between do I do a Tabular or multi-dimension. I tend to start with the assumption that I will do a Tabular till I can’t.
Grant Paisley: Yes I think that is a very good way to do it. Until you run into something that really makes more sense. Analysis Services to do.
Greg Low: It think there will be less and less of those as time goes one.
Grant Paisley: That is right they will just keep adding stuff into Tabular so it will be on par with Analysis Services. It is quite frustrating in some ways because you have spent more time learning all about MDX and multi-dimensional environment. You can do some really cool stuff in there and quite sophisticated business rules. It is probably the biggest difference is when you are doing quite complex relationship between budgets and distributed information and things like that it is still you can do some really nice stuff relatively easily.
Greg Low: I would like to see people not underestimating the effort that goes into getting your head around using DAX appropriately. It is kind of interesting that they took the Excel macro language. They seemed to like removed all the cell related things and added a bunch of Tabular things to create DAX in the first place but most IT professionals I come across were never Excel Power users in the first place. There is no assumption there that they are already comfortable with Excel macro language even though they might have dabbled in it and just the approach you have to take to answer certain question is kind of interesting. I did like again, I mentioned Marco Russo earlier his and Alberto Ferrari’s book where they talked about the initial one on Power Pivot and probably the vast majority of the book was actually on DAX. I would say in fact the longest chapter was just on the calculate verb and just getting your head around what on earth you could with that. I just wouldn’t under estimate what is involved in wrapping your head around that.
Grant Paisley: Yes it is very interesting paradigm. With Analysis Services, multi-dimensional you have a very steep learning curve right up front. You know as well as time and effort just to produce something that is relatively simple would be my argument, whereas in the Power Pivot environment you can get really quickly with a performance core stuff up and running. It is like you hit this wall that is DAX in no time and you say what the hell? Or you want to do something which you think would be relatively simple but you say ah I got to do what, closing balance stuff like that. You have these huge formulas to work out what the closing balance is and there are different ways to do it. You say hang on, I just want to take a closing balance.
Greg Low: Great Grant so look where will people come across you or see you in the upcoming time?
Grant Paisley: If you are up in Sydney you can come and speak with us at the User Group, we meet on the second Tuesday of the month. It reminds me I need to get my Sydney PASS conference that is coming up in March or April. April 20 will be a weekend. That is probably the key areas. Angry Koala website of course
Greg Low: Indeed, thank you so very much for your time today Grant.
Grant Paisley: Thank you Greg, I appreciate it.
Greg Low: Talk to you again soon.
Grant Paisley: We will see you in the year no doubt!
Greg Low: Yes.
Grant Paisley: Bye.
Phone: 1300 SQL SQL (1300 775 775) l International +61 1300 775 775 l Fax: +61 3 8676-4913
Copyright 2017 by SQL Down Under | Terms Of Use | Privacy Statement