Power BI Walkthrough: Analysis Services Live Connect

yo Adam Saxton with Guyana Cuban in this video we're gonna walk through how to set up a live connection to analysis services stay tuned [Music] if you're finding us for the first time be sure to hit that subscribe button to stay up to date with all the videos from both Patrick and myself alright a s live connections how do we actually set this up and what are some things that you need to consider when going this approach we're gonna walk through all of that but enough of all this talking let's head over to my computer and see what this looks like we are in power bi desktop and the first thing that we want to do is go get some data so we're gonna choose analysis services here this is actually sequel server analysis services you want to use this option for on-premises servers or if your server happens to be in like an azure VM or VM somewhere else it's going to be the same as treated as on-premises if you're using as your analysis services you want to come down to more and we'll type in Azure and you will see as your analysis services database listed that's what you want to pick if you're using Azure analysis services if you're using on-premises you want you sequel server analysis services I'll come back to why you need to pick one versus the other in a little bit but for now let's cancel out of that we'll go back to get data and analysis services so this is the on premises sequel server analysis services connector we need to put it in our server name once I have the server name I can leave the database blank or if I know what it is I can type that in leaving it blank will bring up the selection dialog for picking which database that you want the important item here that we need to make sure we select up front is connect live and so you can choose either import or connect live I don't recommend choosing import in general for analysis services because you lose a lot of the benefits that you get with analysis services and it's making a copy inside of power bi desktop and there's just a lot of badness there so we want choose connect live this is going to go against analysis services in the context that I'm doing this from power bi desktop I'm using my Windows credentials so I'm signed in to a domain and it's going to use that windows credential to connect to analysis services had I chose an azure analysis services it would use my Azure Active Directory login which would need access to Azure analysis services so just be aware of the authentication piece that's going on here and obviously I'm gonna need to have access to that database in order to read data from it alright so let's go and hit okay helps to type in the server name right alright so we get our list of databases that we want I'm gonna pick Star Wars here I'm gonna go to model because Star Wars is awesome and boom we are now live connected to our Star Wars database sitting in an on-premises analysis services database what's going on here first couple things you're gonna notice over on the far left I only see the report option I don't have any modeling options and I don't have the model view so I can't see like relationships and all of that all I've got is my report canvas over on the right side I do have my fields and tables and stuff that I've got access to that I can see from a report view perspective so those are available this is because all the modeling stuff will take place on analysis services itself it doesn't happen inside a power bi desktop all we've gotten power bi desktop is just a connection string to go to that analysis services database this will work against tabular or multi-dimensional if you're using tabular because power bi is built on analysis or tabular I can actually optionally go and create what's called a report level measure inside of my report and so if I go to modeling I can do new measure and create a measure inside of power bi desktop this measure goes with the report it doesn't persist back to the actual model so no one else will be able to share that they can only use it in the context of this specific report so if I had another report where I wanted to do that I'd have to recreate that measure in that report as well so that's something that you do have available when connecting live to analysis services but in this case we can go through and we can look at let's go grab our starships just to show you I can bring in let's see manufacturer and then we can do ship you know and we can do things like a chart and then we can do bring a name now you count and you know we can interact with this data and as we're interacting with the data it's actually sending those queries off to analysis services itself so all the processing all the heavy lifting the querying all of that happens in analysis services not inside a power bi desktop pretty cool right and then we can go ahead and create our report like we normally would by dragging dropping slicing and dicing and it'll work just great like a cooking show though let's go over to a report that I've already got and so we can see here you can witness my amazing reporting skills and design capabilities which are awesome not really and so now I've got this report done I've you know connected to my data I've got everything laid out the way I want the next thing we're gonna have to do is publish this report to the power bi service so we're go ahead and it publish and then I'm gonna choose my Star Wars group so you can pick any workspace that you want I've got a Star Wars app work space I'm gonna publish it there it's publishing and then we get an error the error here is because it's an on-premises analysis services and what it's telling me is that it couldn't detect a gateway available to use for this live connection for an on-premises analysis services connection a gateway is required and so something's got to be set up here but that's okay we're going to take care of that in the service so it'll it's still publish a report and we can see that if we go to power bi and we go to reports we can see our Star Wars live report is there and if I select it up top it'll tell me there is no gateway to access the data source Star Wars and if you go to show details you will see further information and basically says hey there's no gateway we can't do anything I do actually have a gateway installed in my on premises environment and so we'll go ahead and use that gateway but if you don't have a gateway you'll need to install one you may not be able to install one so there may be a chance that you have to go to your IT group or whoever is administering power bi and get something available for you that you can use with your live report hopefully you do have access to that though so in this case I've got that gateway installed I'm not gonna walk through installing that in this video I've got a link to another video that'll go through how to work with gateways so you can go check that out but I'm gonna go up to the gear icon come down to manage gateways and my gateway I've got my contoso gateway here we're gonna add a new data source and that data source you guessed it is going to be an analysis service so let's do say Star Wars live the server name and the database name here have to match what I used inside a power bi desktop this is a simple string lookup right so they have to be identical I can't use IP address here and name on power bi desktop I have to use name in both or IP address in both so on and so forth so let's go do this same thing with the database all right I'm gonna have to enter in credentials here so these credentials because of the way live connections work I have to enter a credential that is a server admin on the analysis services machine so this is a server admin defined inside of the analysis services instance not a machine admin for the computer its installed on but specifically a server admin with inside of analysis services itself I'm gonna go and enter in my personal credential but this could easily be like a service account or something of that nature I don't recommend using your personal credential but that's what I have in this case and then I can go and hit add the other thing you're gonna want to do is whoever is publishing the report they need to be in this user list so make sure that they're defined here in this case is just me so we're good to go to learn more about authentication with analysis services I've got another video that talks about up above that talks about how the actual analysis services authentication works and how we pass through the connection from power bi or how we pass through the authentication from power bi to analysis services this is done by way of effective username so I've got a whole whiteboarding session that goes through and shows you how to do that so check that out if you're interested in that piece I will also say that nine times out of 10 the hiccups that you run into are because of authentication so definitely check out that video so we've got our gateways set up now let's go back to our report now you may have to wait it could be like up to 10 minutes for that to actually cycle through the system things are cached and so if you go back and do the report right away it may not work so just be patient wait a few minutes try it again it should work after that so let's see if we're good to go at this point we are not let's just go and refresh it and see if that kicks it into gear and bam after our patience has paid off our is now usable inside of the power bi service and so I can slice and dice like anything and everything works just as expected connecting through the Gateway to my on-premises analysis services I mentioned before about the difference between Azure analysis services and on-prem analysis services in terms of the connection type the difference here has to do with the authentication so sequel server analysis services connections inside of power bi desktop they're going to go down the road of Windows authentication for Azure analysis services we go by way of your azure ad credential or your office 365 organizational authentication so that's why you would pick one versus the other it's all about how are we actually authenticating and obviously if it's through Azure analysis services you want to go that way well I did mention the authentication video to go check that out one thing that's not covered in there is the updated way of mapping user names from a gateway perspective using the local Active Directory as your lookup mechanism so I have another video on that as well if that's something that you want to take a look at and then what about as your VMs or just VMs in general what happens if I install analysis services there maybe it's up in an azure VM so it's up in the cloud how does that work the thing to remember with virtual machines is that you're still installing analysis services on it so it's still considered an on-prem server of some kind even though it's up in the cloud technically and so all the things that apply to on-prem data sources apply here we still need a gateway and we need to make sure that that gateway has network connectivity to analysis services and all of those need to be domain joined as well so however you go about doing that you need to take care of it so maybe it's Express route maybe you're doing some other things with you know peer-to-peer networking however you can get it domain joint is what you're gonna have to do alright let me know what you think down in the comments below or using live connections are you having some struggles with getting the set up did this video help let me know down in the comments below I'd love to continue the conversation there if you like this video be sure to hit that big thumbs up button smash it if you so desire if it's your first time here hit that subscribe button and as always from both Patrick and myself thank you so much for watching keep being awesome and we'll you in the next video

Channel: Guy in a Cube

Views: 47,329

Rating: 4.9385562 out of 5

Keywords: power bi, power bi demo, power bi desktop, power bi desktop tutorial, power bi desktop tutorial for beginners, power bi desktop update - april 2019, power bi reports, power bi training, power bi tutorial, power bi tutorial for beginners, analysis services power bi, azure analysis services power bi, live connection in power bi, on-premises data gateway power bi, analysis services, azure analysis services, business intelligence, on-premises data gateway, powerbi

Id: GPf0YS-Xbyo

Length: 11min 21sec (681 seconds)

Published: Thu May 02 2019