Those trained in SSAS may cringe a bit, but a curious thought entered my mind. Many organizations start using data warehouse structure with fact and dimension tables, but never make it to the ‘cube’ state–actually using analysis services. Admittedly, I don’t use SSAS and the occasional reprocessing of a cube or dimension is about my only experience. When Trey Johnson, our guest for this episode, wanted to chat about SSAS, I thought this would be interesting to get his perspective on using SSAS, the future of the service, and how PowerBI just might make it much more accessible.
Episode Quotes
“I think there’s thousands, if not hundreds of thousands of cubes that are deployed on more traditional multidimensional.”
“People have really been working in cycles that are not easy to break, from a business standpoint.”
“If your data’s not growing exponentially or not having challenges managing data volumes, you probably still have some life left in an on-premise-type environment, versus just the huge benefits that the cloud tends to give in those scenarios where maybe you don’t quite know what the spikes are going to be around your data.”
“Set goals that you want, not that your parents want, or your spouse wants, or your friends want, [not ignoring them, but] make sure they’re goals that really inspire you.”
Listen to Learn
00:40 Intro
01:56 Compañero Shout-Outs
02:40 SQL Server in the News
04:01 Invitation to SQL Trail Mix 2018
05:19 Intro to the guest and topic
06:47 Why has adoption been a bit low?
08:01 ZAP Data Hub accelerates delivery
09:59 There are still thousands of cubes deployed on multidimensional
11:22 People are using SSIS and Data Warehouse but not getting into the “cube”
13:36 Why organizations might want to stay on-premises with SSAS
15:43 Having data structured vs unstructured
18:58 It’s funneling towards either PowerBI Premium or Azure Analysis Services
23:58 People are happy to use what’s working for them until they start running out of road
26:56 Everyone always wants the new thing, but most don’t implement it right away
28:11 What does XMLA do for me?
33:25 SQL Family Questions
41:23 Closing Thoughts
SQL Trail Mix at Summit – register here to attend: https://www.eventbrite.com/e/sql-trail-mix-2018-tickets-51966152160
About Trey Johnson
About Trey JohnsonTrey Johnson has been working with SQL Server for 25 years in pursuit of greater analytic and decision making solutions, since the earliest days of SQL Server.
Today, Trey is a leader of ZAP, an organization with software called ZAP Data Hub which provides automated SQL Server/Azure-based data platforms for rich analytics from a number of ERP, CRM and viable business data sources.
Trey’s past experience includes having spoken multiple times at Microsoft Events for both SQL Server and Power BI, SQL Saturdays, The PASS Summits and a rich history of speaking at numerous Visual Studio and SQL Server Live! events. In addition to speaking, Trey has previously delivered whitepapers, books and magazine articles around the full spectrum of Business Intelligence.
Trey’s most significant SQL Server related career achievements have revolved around his work with PASS, as a multi-term Board Member and a volunteer.
Credits
Music for SQL Server in the News by Mansardian
*Untranscribed introduction*
Carlos: Compañeros! Welcome to another edition of the SQL Data Partners Podcast. My name is Carlos L Chacon and this week, it’s taken me long enough to make this happen, but finally introducing, Angela Henry.
Angela: I’m happy to be here.
Carlos: Yes, thanks for being here. Like I mentioned, it’s taken long enough, just our schedules and trying to get you onboarded to the SQL Data Partners team and then just kind of integrating you into this opening process. But I’m glad you’re here. Glad we finally did this and so she’ll be bringing her thoughts and creativity and the IQ of this podcast has just increased by a factor of many multiples.
Angela: Yeah, fractions of multiples, maybe.
Carlos: So, our guest today is Trey Johnson. He is the Chief Evangelist at ZAP, a BI integrator and they’ve actually done quite a bit in the dynamics space, building data warehouses and cube processes. And so what we wanted to invite him on today is to talk a little bit about Analysis Services, maybe it’s future, and how that area is changing. So that’s our topic for today.
Before we get into the conversation with Trey, we do have a couple of compañero shout-outs we want to pass along. And you’ll, again, compañeros, forgive me if I butcher these. Karel Tavernier, Roji Thomas, Brad Llewellyn and Ron Wheeler. Yes, we actually met up with Brad in Charlotte. Yeah, so you know Brad, right, Angela?
Angela: I do know Brad. He helps run the Charlotte BI users group down in Charlotte and he is part of the SQLSaturday Charlotte organizing team.
Carlos: Oh, very nice. It was nice to connect with him. I had not met him before, so we met down in Charlotte and it was good to connect. And then we continued the conversation on LinkedIn and he will actually be joining us in a future episode of the podcast.
Angela: Oh, fabulous.
Carlos: That’s fun. Okay, time for a little SQL Server in the News! So, we’ve been a little–I say we, so all of a sudden you get invited to the program, and I’m already trying to drag you down into why things haven’t gotten done. So no, not Angela’s fault, completely my fault, I’ve been a little behind on getting some of this news out. I know I’ve been trying to dissect some of the stuff that’s been going on at Ignite, so we thought we could start with some of the low hanging fruit today.
Angela: Absolutely.
Carlos: And talk about a new CTP release.
Angela: Yes, at Ignite, they did actually announce that SQL Server the next will be SQL Server 2019 and it’s got some really cool features in there that bring kind of big data into play a lot more than previous versions. They have big data clusters, that’s kind of a big one that’s out there right now. That’s with Hadoop and Spark and all of those big fancy things.
Carlos: Yeah, that’s right. Just more and more stuff getting into the database. So, it’ll be interesting to see how that all plays out. Yeah, so I think, again, more stuff for us to be talking about on this program. As that rolls out, because, not that we’re going to be masters of it, but at least we have to figure out how it fits into the overall scheme of things.
Angela: Yes.
Carlos: Yeah, use cases, all that stuff.
One announcement we want to make exclusively here on the podcast is SQL Trail Mix 2018 is going to happen during Summit. It will be Tuesday the 6th from 4:30 to 7:00pm and will be at the Yard House, which is where we were last year. So compañeros, if you are headed to Summit, we’d love to connect with you. We’re going to be inviting our podcast guests, and of course, you, the podcast listener. If you’re there and you have some time, you don’t have to stay the whole time. What we’re going to do is the first 30, maybe 40 people who come will get a free drink ticket. So you’re welcome to come have a drink on us and we’ll visit, we’ll have some t-shirts, we’ll have stickers and things to give away as well. But we’d love to just connect with you, chat with you, get to know you and wish you a good experience at Summit. Angela will be staying. Angela’s actually going to present on Wednesday morning, but I will be taking off and won’t be able to stay the whole time as I have another conference to go to. We hope to see you. Again, that’s going to be November the 6th, Tuesday from4pm to 6:30pm at the Yard House. We’ll be over there if you want to stop by and say hello. Registration information will be on the show notes page for today’s episode. Or if you’re registered to our news list, you’ll get an email with the registration link there as well.
Okay, so the URL for today’s episode is going to be sqldatapartners.com/ssas or sqldatapartners.com/152. And so with that, let’s go ahead and jump into the conversation with Trey.
Carlos: Trey, welcome to the program.
Trey: Thank you, Carlos.
Carlos: And it’s nice to have Eugene, as well.
Eugene: Thank you, yeah, glad to be here.
Carlos: Trey, ultimately, our conversation today is around the data platform, but specifically, I think we’re going to spend a lot of our time maybe focused or emphasizing with the Analysis Services piece. One of the things that’s interesting is that with a SQL Server SKU, we know that we get, obviously SQL Server, we get Integration Services, we get Analysis Services, but of those three services, it’s probably the one that’s least used, but probably one that is, I don’t know, causing the most, I don’t know if anxiety is quite the right word. But there are lots of projects around the whole data warehouse space is happening right now. I guess it’s been happening for a while, but everybody’s trying to figure out how to analyze their data and when you think about Analysis Services, the name itself kind of lends to that idea, but it has kind of a limited use, and things are changing very quickly, there. So to start off, why do you think the adoption maybe has been a little bit low and then I’d like to get into the thought of Microsoft pivoting from multidimensional to saying, “hey, sorry guys, remember all those models you created? Well, hey, what about Tabular?”
Trey: Yeah, sure, absolutely. Well, and I think Analysis Services has such a rich history in terms of being part of the Microsoft SQL Stack, going back to 90’s initially unveiled Analysis Services, and there’s probably a lot of people that are certainly on the multidimensional side, that argue a lot of those technical advancements haven’t necessarily taken quite the quantum leap over the years. But part of it is because it’s just, for lack of a better way to put it, it’s pretty stable technology in terms of the way that it works, particularly with on-premise environments and those sorts of things. Interestingly, the company I’m at’s app, one of the things that we’ve really worked very hard to do, because you’re right, the adoption side of Analysis Services is sometimes kind of challenging, because it has been traditionally different than constructing databases and those sort of things, is our tool set. We have a product called ZAP Data Hub, and ZAP Data Hub, what it basically does is it accelerates the delivery of not only the relational platform, but also the Analysis Services platform. As a result, you may have folks that maybe were the non-traditional BI, but are kind of developers, probably maybe folks wearing a little bit more of a DBA hat or even an analyst-type hat, that were able to generate those Analysis Services deployments. Through that cycle of kind of being involved with ZAP and seeing what we were doing, there’s actually a lot of companies that are still really dependent as third-party software vendors on the Analysis Services platform, specifically multidimensional, so it’s really interesting.
Carlos: Right, so that is an interesting point you make. I wonder if third-parties are potentially some of the reason why multidimensional still gets used a lot. For all intents and purposes, I’ll use the word abandoned, but only because Microsoft hasn’t provided a way forward. The big talk with the cloud services and Analysis Services is Tabular is the new way. But it is interesting that you say, “well, hey, there’s a whole lot of invested infrastructure here, or invested application that’s still depends on multidimensional,” so it’ll be interesting to see where that goes.
Trey: Yeah, well, and I think part of it, honestly, is the equation or kind of how you measured adoption around Analysis Services technology, for a long time, was purely around multidimensional. Now, Tabular has come to life, and Tabular really underpins PowerBI as well, so in a lot of ways you could say, “okay, we discovered a new territory that was Tabular and therefore, we found all of these new citizens that lived in that territory, so our population has grown, and oh what do you know, the multidimensional is the smallest population.” I think that’s the reality of where we are today. I think there’s thousands, if not hundreds of thousands of cubes that are deployed on more traditional multidimensional.
Carlos: Do you still see that as a path moving forward for a long time to come?
Trey: I think the challenge is the language that’s used around multidimensional being MDX is not something that is really taught, so for those of us with grey hair, it becomes the kobold of the Microsoft BI space, where there’s still going to be a need for potentially MDX and those skill sets, but it’s not something that people necessarily are rushing out to learn, and there’s no criticisms in that regard. But I think the challenge, and I can only imagine what Microsoft’s challenges really are around this, but there’s, I presume, maybe a half dozen people that actually work at Microsoft that could touch the multidimensional code at this point. I mean, there can’t be that many. Because they’ve all kind of moved on to different roles or possibly beyond the organization, so that’s definitely a challenge, but at the same time, with all of these partnerships and really somewhat significant organizations with Analysis Services behind their solutions, Microsoft’s not, I don’t think, quite willing to say, “hey, we’re going to put a bullet in this technology, and it’s going to be, in this time frame.” It leaves a lot of questions, versus answers, that’s for sure.
Carlos: Right. So now, one of the things that you mentioned, actually, in one of the blogs that I read was again, as we think about data and moving data around and SQL Server obviously gets a lot of use, Integration Services gets a lot of use, I think there’s even Data Warehouse, the integration between there, but we never quite make the leap to Analysis Services. Or, I know a couple of people are going to cringe when I say this, but I’m going to say, “to the cube”, so as the knuckle-dragging Neanderthal that I am, at least my view of the world is, when I start thinking about Analysis Services, I’m thinking about a cube. And obviously, there’s different terminologies for that, and what that is and whatnot, and maybe we can get into some of that, but I guess, why do you think that is that folks will even stand up to Data Warehouse, but they’ll never quite get into the, again, “cube”?
Trey: I think part of it is that they may not have historically possessed tools that really drove them to use a cube versus using other technologies. I mean, Reporting Services is a very prevalent technology and certainly the common denominator around the SQL Server SKU in terms of languages is actually SQL itself, so people tend to understand that. But what’s interesting, if you think about it a little bit, Analysis Services to the SQL Server SKU and the SQL Server platform, is kind of like the transition that’s happening around the data platform on Azure, because there’s such a range of full, particularly analytically oriented technologies that support data lakes and support all of these traditionally open source languages for scripting, like Jive and Pig and all this other stuff. Those languages, you could draw a little bit of a parallel between the way MDX was unlocking data on Analysis Services, being so foreign to a lot of the folks that were traditionally SQL only.
Carlos: Right. Just basically the chasm, or you have to have that skill-set to be able to take advantage of all of those things. Now it’s interesting, you mentioned, and Eugene, obviously, feel free to jump in here.
Eugene: Yeah, definitely.
Carlos: We’ve talked about– we’ve kind of already jumped into some Azure services there, but you make an observation that there could still be a lot of reasons that we would want to stay on-premise with our Analysis Services pieces, so why would organizations continue to do that?
Trey: I think some of the drivers, and I fortunately was able to do a session just this past weekend in Orlando at a SQLSaturday on this topically, and the sessions I do at SQLSaturdays, I like to really turn them more into conversations and get as much feedback as I would share. And it was really interesting talking to the group about the reasons that you might choose to stay on-premises versus potentially just purely move to Azure or move to a cloud platform. And a lot of the feedback was people have really kind of been working in cycles that are not easy to break, from a business standpoint. So, kind of the capital procurement cycles around servers and a lot of those sorts of things that are kind of off the radar, for me, often, from a BI perspective. But just purely from a technology and the SQL front, if you’re not doing things with, let’s call it modern data, or unstructured data, well, the structured data still lives and thrives really well in a SQL Server platform. So that really just means that you have options, that you can stay on-premises with your SQL Server deployment. If your data’s not growing exponentially or not having challenges managing your data volumes, well, you probably still have some life left to live in an on-premise-type environment, versus just the huge benefits that the cloud tends to give in those scenarios where maybe you don’t quite know what the spikes are going to be around your data. Or maybe you’ve had an acquisition of another entity and you’re not really familiar with their business, but you know you need to store their data, too. All of those other things that Azure and other cloud platforms allow you to really scale up quickly, if you need to, but also, throttle way back if you need to, as well.
Carlos: To that point, to the ability to throttle up, and down, and Microsoft is, obviously, from a marketing perspective, throwing a lot of money at unstructured data solutions, how simple it is to get up and running, new development, if you will. And even think about data science and some of that reporting, and I would include PowerBI kind of sort of in that, from a self-service perspective, where it’s like, “hey, I just want to get in there and ask questions of the data.” This is maybe a little more philosophical, but do you see, at the end of the day, once we have some of those questions and we come up with the models to say, “oh yes, we care about A, D and F”, that that should translate into more structured environments? Or do you think that will always stay in again, this unstructured, for example, the data lake or–
Trey: Yeah, it’s a good question. I mean, because at some point, and I think it’s more on the human consumption side than even what’s the right way to structure the data, that I think people need structure in the data, in terms to be able to ask questions from a traditional business user context. People tend to ask questions as dimensions and facts, right? They still do, and I think they will continue to do so. What’s really interesting is the data that’s living in places like a data lake, for example, everybody talks about schema on read, it’s because you kind of tend to need to apply a schema to that data in order for it to really still make sense, even in the PowerBI Tabular models and all that sort of stuff, as well.
Carlos: Yeah, so it’s interesting that we’re trying to– so like XML didn’t quite solve the method of, “hey, I just want to be able to have all this data. I don’t really care what form it’s in and I just want to move it at will and then still be able to report on it.” There’s still two sides to that coin, and so it will be interesting to see from a marketing prospective, some of these things like Analysis Services won’t get the love. They’re mature products. But I feel like there’s still going to be that need, because yeah, eventually you’re going to want to come home to where you can hang your hat.
Trey: Yeah, yeah, no, for sure. What’s really cool, and Microsoft, I think, just only recently made these announcements, I think at Ignite, around the XML for Analysis or XMLA for the schema definition for PowerBI Tabular models being open. Well, what that really does it that opens the door on some bidirectionality from being a model underneath PowerBI in a very small context to being an Analysis Services in Azure model that could be in a much larger context, to be used by potentially a larger population. At the point that you can do those things, Azure starts to look a lot more like what I’ve seen in Enterprise Data Warehouse environments where you have the structured data that evolves into an Analysis Services. Cube, if you talk multidimensional, model if you’re talking Tabular, and then you can use front ends against that. It just becomes very interesting.
Eugene: I’m glad you brought up the whole XMLA piece, because I think that’s going to be big, because I’m sure you’re probably aware, but for the longest time, Microsoft very much treated the .pbix, Power BI desktop format as this black box and you weren’t supposed to touch it and you weren’t supposed to poke around. But when 2017 announced its services was in preview, I used to be able to do this demo where I could rip out the data model file and the .pbix file, because it’s just a zip, and just change it to a .abf and restore it as a backup for Analysis Services. Because that’s all that it was, right? And they changed the format slightly, but it’s really interesting that they finally at least added a supported end point to be able to poke around with that stuff. But something I’d be interested to get your thoughts on is, I’m comparatively a young whippersnapper, I’ve been in the field for like 6 or 7 years. But my understanding is 20 years ago, setting up a data warehouse and doing all of that stuff was a huge, expensive, extensive kind of process, right? Where you needed like a team of 10 or something like that?
Carlos: You’re talking about years, not months or weeks, even.
Eugene: Yeah, exactly. But it’s really interesting, this strange targeting vector, marketing vector they’ve got set up now with PowerBI and that you were talking about a lot of people are going to want to stay on on-premises. But what I think is going to happen more and more is you’re going to have a lot more of these tiny, itty bitty data marts that are just homegrown by, I describe the person as like Kris in accounting. She’s wicked smart, she knows the business, she can use VLOOKUP like a scalpel, is a little bit dangerous with SQL, kind of person. So you’re going to have all these tiny little data models popping up and Microsoft loves to talk about their grow-up strategy, and the thing is, as far as I’m aware, right now there’s no good way– and maybe that will change with XMLA. But there’s no good way to say, “okay, I want to take this data model from PowerBI and move it into on-premises Analysis Services.” But they make it super easy to just upload and export to Azure Analysis Services. So, what I think is going to be interesting is to see how much Azure Analysis Services adoption is driven by people who, Frank in IT, who’s been handed this .pbix file and the CFO says, “I love this report that Kris made, productionize it.” It’s interesting, I’m curious about your thoughts about that grow-up story they have where it’s basically all funneling towards either PowerBI Premium or Azure Analysis Services.
Trey: And I think what’s really funny to me is, I’ve had the good fortune of working with SQL Server for 25 years, so I’ve been kind of through it all, but through it all, there’s always been this proclamation of a single version of the truth that people are aspiring to get to. And you’re right, in those scenarios where people are doing what they used to do in Excel, but now are doing in PowerBI and creating your own personal models and how Janey defined gross profit’s different than how Bob defines gross profit. They bring results to the meeting and they fight over whose gross profit’s more correct than actually the fact that they aren’t making as much profit as they should. And all of that good stuff tends to come through. It is going to be a bit of a challenge, because there’s a different constituency that’s actually building, where it used to be more of an IT exercise, it’s now federated and out amongst the business. There’s people with analyst hats and you go to a PowerBI group meeting, I would say probably 60 to 70 percent of the people in the room are not IT. They’re something else.
Eugene: I can confirm that. I help run the Pittsburgh PowerBI user group and so, so many of the people are end users and a lot of them aren’t even PowerBI users, per se. They’re Excel users, because a lot of those same technologies are available in Excel. So, you’re absolutely right that it’s kind of like grassroots BI, essentially, which is really interesting to see.
Trey: Yeah, and not to dwell on my company or our product or anything like that, but that’s where we’ve found a lot of sweet spots with customers is because we’re accelerating that data platform piece, and you’re right, it used to take an obscene amount of time to get a warehouse built. Now it’s something, especially with our software, we’re literally in the days. So, you’re doing things over days, you have a data platform, you built your PowerBI model off of that, and then people start to build assets from that. And in that situation, you’re fully grown up. There’s no grow-up model that’s needed, but in the scenarios where people are creating their own silos, that is the trap that a lot of the tableau stuff fell into.
Eugene: Okay, that makes sense.
Trey: Tableau was gold in more of a departmental context, and so as a result, either you had the, hey, marketing looks at the conference everybody went to and thinks it’s great, because they were on budget and this, that and the other thing, but then the sales guys had their own tableau thing and they say, “oh, man that sucked. We had three opportunities out of seven hundred leads.” That sort of thing. But there was no real conversations that were happening between those departments, because they were using the same tools in different ways.
Eugene: Right.
Carlos: To circle back for a moment to this idea of pushing your PowerBI desktop back to an on-premises solution and the fact that there are many folks that are outside of the IT department. I think one of the big roadblocks will be is, “where do I deploy it to?” So the whole PowerBI concept is very simple. The moment that the user has to request, “hey guys, can you install Analysis Services for me?” it’s too many roadblocks. You’re kind of back to this whole model of, “hey, I have this request,” where Microsoft’s provided the Easy Button, in a sense, to be like, “hey, you don’t need them, just do this,” so it will be interesting. I think in a sense, they have to be able to support it. We’re not saying that you should go an unsupported route, but I think that could be one of those lessons from an IT perspective, where we need to get in front of some of these things and try to start providing solutions. Because, yeah, inevitably, the whole shadow IT thing, particularly in the analytics realm, it’s very easy to do that, as you just pointed out, because people have their own tools and they’re like, “well, I’m going to make my own destiny, here.”
Trey: Yeah, I think the inherent goodness in all of this is that more people are actually using the tabular model as a technology. Whether it sits under PowerBI and ultimately evolves to Analysis Services on Azure or some other place, I think those pieces are inherently good. And as you guys both know, users are happy to be content working with the technology that works for them, until they run out of road. And that’s where it’s going to be really interesting is at what point do people start running out of road? Is it going to be data volumes because they’re not on the right PowerBI subscription level?
Carlos: Which is very possible, because that seems to always be the case. I was like, “well, hey, it worked for this thing. It worked for my one department, now I want to apply it to the Enterprise,” and all of a sudden, everything’s really slow.
Trey: At least there’s some commonality and even more so with Microsoft opening up the XMLA pieces. But I guess my main thing about the ‘don’t rush to the cloud’ or ‘don’t rush off-premises’ is the things that I tend to see at a lot of the SQL events. Where people are coming to those events and frankly, they’re still trying to come to grips with maybe a prior version of SQL Server, is one of the things that they’re working in there. And it’s a challenge, I think, for the community leaders to introduce what’s on the leading edge of things, but also bring a level of expertise to what people are doing day in and day out. I think the people that are the most notable in our community, honestly, are the ones that are still sharing advice and guidance that can apply to the technologies people are running under their roof right now.
Carlos: Right. To tangent slightly, SQL Trail, which is, well, as we record this, it hasn’t happened yet, but it will have happened when this goes live and I’m sure it went excellent. I feel like that is one of the issues or key points. And there’s a bit of a balance, because when people think about conferences, they want the bleeding edge, or they want the new stuff, like, “hey, gimme, gimme, gimme new” but the vast majority of folks are not going to be implementing that. And I’ve always felt like, not that you don’t need to understand the roadmap and understand what’s coming, but I don’t know that you need an hour-long deep dive of how to do this X functionality when you’re not going to see it for two years. And so, it’s an interesting conundrum and now as an organizer, to try to put on things like that. And of course, the internet helps with a lot of that stuff, so we’re grateful for the internet.
Trey: Oh, absolutely, absolutely.
Carlos: Okay, I obviously got us into a dead spot and I apologize. I started to think about my conference. We’re right about, actually we’re about at the half an hour mark.
Eugene: Yeah, I think we’re at a good point to cut off. I mean, there’s obviously more that we could talk about, but I’m sure your viewers would appreciate an episode that’s actually half an hour, instead of the alleged half an hour.
Carlos: For people like myself, who are not familiar with the whole XMLA thing, I feel like maybe we should describe that in a little bit more detail than maybe we talked about. So you guys kind of threw around the terms like, “oh yeah, I totally know what I’m talking about, there.” So, for me, who has no idea, why don’t we just discuss that really quick, just to tie that loose end? So, what does that mean, exactly? When I create my PowerBI, I have my report is in a certain format, and I think, I’m not sure if this is kind of going back to we’re comparing it to Reporting Services, but the ability to break up data sources in that report? Or what exactly does that mean? What is the XMLA thing do for me?
Trey: Yeah, so Eugene, I can take a swing at it, and feel free to kind of–
Eugene: Yeah, absolutely.
Trey: Clean up on it, I guess. But when you think about PowerBI underneath the PowerBI report or dashboard, there’s typically a definition of the data source, and that tends to take on the definition of a tabular model and the XMLA that we mentioned is really just the way that describes that model. It’s almost like the equivalent of DDL for a SQL Database in that it defines, really richly, the schema of the model, and then that XMLA, having the ability to interact with that. Firstly, allows you to port it between PowerBI and Azure Analysis Services and potentially Analysis Services on-prem. It also potentially opens the door for other parties to automate or otherwise inject elements into that schema.
Carlos: Gotcha, so for a simple example might be a versioning? Like I have this, and I want to add something to it? Or in the case of like your tool, there’s a standard, if you will, report out there that I want to add my flavor or my spice into it?
Eugene: I think the big thing in my mind is a couple quick things. One of the things is a demo that I would always love to do when I do a PowerBI Internals is if you run PowerBI, then you can take like, say, DAX Studio from Marco Russo and Alberto Ferrari, or you can run some command scripts to get a port number for Analysis Services. Because when you are running PowerBI, it spins up a copy of Analysis Services in the background. You could connect to it with SSMS or you could connect to it with Excel or whatever, but doing so was highly unsupported. And so imagine if you had the equivalent of SQL Server Express, but if you connected to it with SSMS, that was unsupported and you were on your own.
Carlos: Oh I see, uh huh.
Eugene: Yeah, exactly, so that’s the way that I think about it is, Microsoft’s basically saying, “okay, here is an API, here’s an endpoint, here’s something that you can connect to, to officially make changes or pull data out or pull schema out, that kind of stuff, and it’s actually going to be supported.” So, this is the equivalent of, “okay, now you can actually talk to SQL Server Express with the tools that you know, with SSMS and it’s going to be supported.
Carlos: Okay, so that’s the big difference. Here’s a supported way to–
Eugene: Yeah, that’s the big thing for me is because before, like I said earlier, they just kind of treated it as this black box and literally, they would change file names or move stuff around inside that .pbix format and again, you weren’t supposed to touch anything, you weren’t supposed to poke around with stuff. And so now, they’re saying, “well, we still don’t guarantee that you’re allowed to look at the inside, but at least here’s this stable endpoint.” Where now, you can pull out some of this information and it’s going to be supported.
Carlos: The same, okay.
Eugene: And so if you wanted it before, again, DAX Studio.
Carlos: The other example that pops into my mid there is almost like you introducing the DMVs in 2005. It’s like, “hey, we’re going to abstract some of this stuff. You can still get in there, but we’re going to abstract it a bit.”
Eugene: Yeah, exactly. And so now, if you’re a tool-builder, you have an endpoint that you’re allowed to actually talk to. Because again, the thing about DAX Studio. DAX Studio’s great if you want to just run some DAX code or some MDX code or whatever. It’s a really great free piece of software, but in the past, whenever in that dropdown you could pick a PowerBI file that was running, that was highly unsupported. You could poke around, you could run queries against that data model, but you weren’t supposed to. Well, now they’re going to be able to add official support with that tooling. So I think it opens up a lot of ways to talk to that data model or take a look inside in a way that Microsoft isn’t going to just hang up the phone on you when you have a support ticket. So, that’s kind of the big thing, in my mind, about it.
Trey: Yeah, and I think the only thing I would add is there’s just a lot of foretelling there, that Analysis Services, as a technology, is still very important to Microsoft. That it happens to have become a little bit pervasive and underlies PowerBI, for example.
Carlos: Interesting.
Trey: But it’s still very important.
Carlos: There you go. Yeah, so I’m not sure why, but it shouldn’t surprise me anymore, especially having Eugene on here that we start talking about Analysis Services or any kind of analytics and somehow, we get into a conversation about PowerBI.
Eugene: Well, I do make my living off of PowerBI, so I’m a bit biased, that’s for sure.
Carlos: Well, there you go. Shall we do SQL Family?
Trey: Sure.
Carlos: Your all-time favorite movie?
Trey: I think the one that I go back to, I’ve got a lot that I like, but it’s probably, as crazy as it sounds, and it really is going to make me seem incredibly old, is Tron. And the reason is that more as an adult, I came to appreciate that movie. It’s just that the things that they did in that movie from the visualizations and all the other stuff, there was no CGI, when that movie was done. They actually had to develop all of the rendering software and all of those sorts of things in order to even make that movie happen, which is just monumental, compared to the, you know, you go throw millions of dollars at a CGI firm and you end up with a lot of really cool stuff.
Carlos: Right, right, yeah, very cool. Yeah, that one, I admit, as a person who’s not into movies or like the whole making of the movies, I struggled with that one, trying to go back and see it a little bit later. But I do remember it being a very futuristic when that came out. The city or place you most want to visit?
Trey: I’m fairly well-traveled and I’ve had the good fortune of being on the PASS board for 6 years and a number of things, I did get to see a lot of places. But I think I’d love to get back to Germany, and I don’t necessarily have a specific place, although, if I do travel to Germany, I do want part of my travels to take me through a little town called Garmisch, because it’s a very quaint little town that’s right there at the base of the Alps and it’s just kind of a cool place to be.
Carlos: Very nice. Interesting. A food that reminds you of your childhood?
Trey: On occasion, and this’ll probably sound weird, too, but I grew up playing a tremendous amount of golf as a kid. I mean, kind of the dropped off at the course at 7am, parents pick you up at 7pm, kind of thing. So, me and my buddies, we kind of lived off of hotdogs. And it just had a certain look and taste of whatever, so if I come across that same kind of hotdog, it has to be a pretty good quality hotdog, it reminds me a lot of that.
Carlos: Now, what are you putting on that hotdog?
Trey: You know, just ketchup, honestly.
Carlos: There you go.
Trey: I’m not loading it down. No, I mean you know, there’s nothing wrong with a chili dog or anything like that, but ketchup gets the job done for me.
Carlos: There you go, so it’s the golfer hotdog of choice. Now, how did you first get started with SQL Server?
Trey: Gosh, that was 1993, it was an internship at the University of Central Florida in their College of Business, and was using Access 1.0 against a Sybase Plus Microsoft SQL Server platform on OS2 Warp from IBM, so what could go wrong, right?
Carlos: That’s right. I’m sure that environment is still up to this day.
Trey: Well, shortly thereafter, it was the work that I did in that internship, and that’s why I think we’ve got to get kids interning as much as possible when they’re in college, but shortly thereafter, I took a job that really was purely a BI career in the makings, OS2 Warp still, SQL Server still, but I was evaluating all of the decision support tools, is what they called them at the time. But that goes back 25, 24 years.
Carlos: Interesting. Funny you mention the internship because I was also affected or my career choice was affected by my internship, as well, so that’s interesting you bring that up.
Trey: Definitely.
Carlos: Now, in all that time with SQL Server, if there was one thing, and there’s been lots of changes and our focus has been a little bit on Analysis Services and so you can include that or PowerBI. We’ll be a little bit broad with this question, but if you could change one thing about the SQL Server space, what would it be?
Trey: Yeah, I’m going to just sound like a total dud, because we just hammered this topic into the ground, but I really would just like some clarity around the whole multidimensional thing. I think if there’s going to be a pathway at some point on the horizon for multidimensional to move over to Tabular, that would be fantastic. I think it’s a challenge and I appreciate the challenge that Microsoft has, but it would have been nice to have seen multidimensional make its way into the cloud.
Carlos: Right, okay. Well, now, is that rumors that I’m hearing. I guess I feel like they’ve said it will eventually come.
Eugene: They’re planning on it, yeah. That’s what they’ve said, but you know, eventually is a very long time.
Carlos: Yeah, that’s fair. Just think about how fast things move in the cloud. This is definitely not going to be one of those things.
Eugene: Well, and you think about it, too. The people most likely to still be using MDX, I would expect are also the least likely to be moving to the cloud very quickly.
Carlos: Fair enough, fair enough. Okay. The best piece of career advice that you’ve received?
Trey: I think the best advice that I’ve received was the younger you are, your goals have a shorter horizon. You know, you want things in a year or whatever the case is. But whatever the timeline is that you set those goals, set goals that you want, not that your parents want, or your spouse wants, or your friends want, those sorts of things. Make sure they’re goals that really inspire you. And that has worked well for me throughout my career. Not that I’ve ignored my spouse or my parents or my friends, but it has to be something that I gravitate towards. Because if you don’t have that, then you’re working possibly for the wrong reasons.
Carlos: There you go. So now, to put you on the spot here, and of course, we’ll use editing if we need to pull this out, but can you give us an example, potentially? I mean, so you’re not a golfer anymore, so I’m assuming that that was not one of the goals.
Trey: Wasn’t in the cards for me long-term, no.
Carlos: Long-term, right.
Trey: I’m not playing with David Duvall the professional golfer, so to have to benchmark myself against a guy that became number one in the world is kind of hard.
Carlos: But did you have one of those long-term goals? I mean I know you started this business here. I mean, as an entrepreneur myself, I know I think about that a lot. Do you have an example, maybe, of one of these long-term goals? It doesn’t have to be complete. It doesn’t have to be complete, maybe just something you’re just working on?
Trey: No, it’s really fair. I knew that from an early stage that I loved technology, and as I began working in technology, I meandered and ended up working in consultancies and had the good opportunity to run a national consulting practice and other stuff. But it was really at that point that I said to myself, my goal was I really wanted to cross the aisle a little bit and work more in a software company and so, that took a couple of pivots in terms of my own career to get to the point where I was actually working on the other side of the aisle. And I thoroughly enjoy it and there’s still consultative aspects of what I do and some of the people that I manage do. But at the end of the day, we’re working as more of a software entity than we are a consulting entity.
Carlos: Okay. There you go, so we’re going to have to talk off-mic. I’d like to circle back around to this very idea. I’ll have to hit you up some other time.
Trey: Absolutely.
Carlos: Because, our last question for you today. If you could have one superhero power, what would it be and why do you want it?
Trey: Oh gosh, that’s really a challenging one, because the ability to grow hair out of the top of my head would be pretty good, oh gosh, it’s such a difficult one. It’d have to be intellectually oriented. I don’t know that I want to be able to smash through walls or bend steel or you know, any of those sorts of things.
Carlos: That’s the wisdom of age talking, there.
Trey: Yeah, yeah.
Carlos: Eugene, that’s all he wants to do. Every time he’s like “can we smash something today?”
Trey: Yeah, maybe Jedi mind tricks or something.
Carlos: There you go. Trey Johnson, thank you so much for being on the program, today.
Trey: Oh, thank you very much, Carlos. Thank you, Eugene. Thanks for having me.
Eugene: Pleasure.
Carlos: So Angela, one of the takeaways for me is that there continues to be change in the space. So, we had Tabular and that was kind of a big move, and obviously lots of things have been happening with that. Eugene talking a little bit about how PowerBI is kind of moving the needle in this SSAS space is interesting and I think a lot of folks are waiting to find out what happens. We’ve been waiting for a while to understand better what’s going to happen with multidimensional and yeah, I think we’re among those people that are kind of sitting here watching and waiting, because there’s a lot going on, and there’s a lot that we just don’t know.
Angela: There is, there is. There’s a huge investment out there for lots of companies in multidimensional, so they are waiting on pins and needles to see what’s going to happen in the future.
Carlos: Yeah, that’s right. I have to imagine there’s going to be some kind of refactor. But I also think that that’s, at least from us the technical folk, that does represent some new opportunities, potentially.
Angela: Absolutely.
Carlos: In the future. So yeah, it’ll be interesting to see what happens here, and yeah, I guess one thing that we didn’t necessarily flush out in this conversation was that idea of that going from the data warehouse to again, the cube, so actually, using Analysis Services piece. It seems like lots of people have fact and dimension environments and perhaps it is that for those smaller organizations, they’ve been hesitant to take on the multidimensional, so I guess I’m curious. I don’t have the numbers, but it would be interesting to see if some of the Tabular stuff, or even the integration with PowerBI, increases those numbers and people finally decide to bite the bullet and make those investments.
Angela: Yeah, yeah, I’m very interested, as well.
Carlos: Okay, so that’s going to do it for today’s episode, compañeros. Our music for SQL Server in the News is by Mansardian, used under Creative Commons. As always, we’re interested in hearing from you. If you have things we should be talking about, feedback on the program, please let us know. You can reach out to us on social media. And so, I know Angela, you’re active on social media, you have a couple of accounts. Do you want to take us through some of the ways that folks can engage with you?
Angela: Absolutely. You can always find me on LinkedIn. I’m just AngelaHenryDBA, not very creative, but that’s who I am. And then you can also get me on Twitter, which I actually frequent that a little bit more than LinkedIn and I am sqlswimmer.
Carlos: And compañeros, you can connect with me on LinkedIn. I am @CarlosLChacon. And we’ll see you on the SQL Trail.
Leave a Reply
You must belogged in to post a comment.