Power BI Walkthrough: Analysis Services Live Connect

Adam walks through how to setup a live connection to Analysis Services, or Azure Analysis Services, in Power BI Desktop. This also involves getting things working with the On-Premises Data Gateway.
What is a gateway - • Power BI and the On-pr...
Authentication with AS Live Connections - • Authentication with AS...
********
LET'S CONNECT!
********
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
Check out my Tools page - guyinacube.com/tools/

[@johng5295]

Thanks in a million! Very well explained. Great content. Awesome. I couldn't find this explanation--simply put anywhere else. “Great teachers are hard to find”. Grade: A++ 💥

[@alexkim7270]

Wow that's insane how you made it sound so easy to understand.

[@kelechie6836]

Thank you @guyinacube this is timely!

[@ovdaddy3153]

2 years later and it worked like a charm. Thanks

[@giuliapiazza4603]

Can I ask you an advice? I don't understand how it works :'(

[@peterdoyle1994]

Just a good professional demo. Thanks

[@Riya-bn6jy]

Explained in such simple words... Yet to do it, will surely come back if I have doubts :D

[@user-ih7ot7vl8u]

Very thanks for you

[@pradeepyadagani7856]

Thank you for Video. Can we use Analysis Services from Power Dataflow? Didn't see such option in Power BI online. And is there a way to use LIVE connection along with IMPORT?

[@devman2k]

Great video ! Could you answer this question (or create a video) on connecting to Analysis Services VIA HTTPS using OLE DB and how that impacts the gateway. I've a report using HTTPS Analysis Services to the DLL which works in PBI Desktop but once published cannot refresh as the Gateway says it needs to be setup. I would think we would not need the Gateway at all. Thoughts?

[@yogeshbansal7184]

Thank You

[@COk1ll3r]

Hey guys fantastic video, thanks!. just a quick question: is there a way i can control whether or not excel uses DAX or MDX in background when live connecting to a SSAS tabular?. i've been using DAX Stuidio yo review the queries sent to the tabular and found out that when you do something from EXCEL it queries the tabular on MDX, is it possible to force it to use DAX?

[@tonisergi3573]

I'd llike to know how you made that awesome visual, that's the part I struggle with.

[@sreeramgodavarthy1828]

Hey Adam, Is there a way we could test RLS applied on SSAS model directly on Power BI, instead of using DAX Studio?

[@melianiferiel3562]

Thank you a lot for this video. I’m struggling with translations that are implemented on Ssas tabular. Everything work fine when we connect to the Ssas instance via Excel. But metadata translations don’t appears to work once the report published on power BI report server and even in power BI desktop. I’m not sure if translations on Ssas live connexion are supported by Power BI

[@GaryMWilson]

Thanks for another great video! Could you elaborate on what you said about not choosing import in general for Analysis Services? Why is there "a lot of badness there"? I agree with you but others think they need to be able to do this and then publish the pbix file to an on-prem Power BI Report server that is set up to connect to the same SSAS model the data was imported from.

[@garymcbride340]

What if you have external data that you need to link to the database. You cant do this with live but extracting / imports specific data and tables for the data model in Power pivot or Power Bi provides that flexibility. Why is this not a good idea and how can I link external data to the live connection data if using live connection? Thanks?

[@krishkrishna2630]

Hi, I have a situation in power bi , i am unable to figure out. Wondering if you had any issues with SSAS live connection and inactive relationship within the data model ? Any help is highly appreciated. Thanks!!

[@MrRakesh448]

Hello guys, While connecting Power BI report to Azure Analysis Services via Live connection can we create subscriptions on the report?

[@chandeepsingh9581]

Hi, I have a query regarding Dashboard based on live connection in cube. Are you able to download the underlying data?

[@TheHoussem94]

Hello, How can we edit the connection string to add the customdata in the power bi services juste like the reporting server ??? I have a report deployed in reporting server and each report have a specific value for the custom data that will be used for rls filter, how can i migrate this to power bi services??

[@TheKavo74]

Hi currently investigating live connection with SSAS tabular and Import. When I choose import it tries to bring all tables into one table if I select them all at once. Has anyone else found this?

[@andresjorgegebhardt9302]

Hi Adam, I've a PowerBI report connected live to my Azure Analysis Services Tabular model, that actually have translations configured. It's working fine in the desktop, but from the service is always showing the labels in English.. I tried to create a dataset (connected live to the tabular model), and a report connected live to that dataset, but it didn't worked aswell. Is there a way to make the traslations work from my tabular model in powerbi service? Thanks in advance!!

[@SSerillo]

Hey @Guy in a Cube can u please adress to the problem of date hierarchy in live connect. Thanks in advance

[@Sandhyaansoman]

Hey I am facing issues while connecting azure analysis services in live connection in power bi..once published to power bi service, i am geeting an error saying "cannot load model"...my analysis servjce is already up and running..and it works perfectly if i use import mode for the same..plz help

[@zenboxs8128]

whether the analysis service cannot use what if models ?

[@roxnroll8050]

Great video - though I'm already lost at the very first step! LOL... @1:40 you mention enter the server name. What would that be if the server is on AWS EC2 instance? (not local). Microsoft's own website lead me here - so now I'm in a crazy loop without knowing how to do this... I watched the whiteboard video - but didn't really help.

[@kennethmartinez10]

I need help, i can't publish my report. I have an issue : "There was a data source access error. Please contact the gateway administrator". I did all the configurations but it still doesn't working

[@delcidr20]

Is possible to use PowerBi Web and Gateway without SSAS Tabular, i'm using a multidimensional model

[@jeetukumargupta8051]

Can we make a live connection from powerbi to ADLS Gen2 or a SQLServer?

[@FreightLine10]

How to access the AD for local SSAS Tabular Server?

[@jashiralejandrocanllalinar1236]

I HAVE A CUBE IN SQL SERVER 2012 (ANALYSIS SERVICE) BUT WHEN I TRY TO CONNECT WITH A LIVE CONNECTION I GET THAT THE FOLLOWING ERROR: "THIS DATA ORIGIN DOES NOT ALLOW EXPLORATION WITH A DYNAMIC CONNECTION "

[@gbj88]

Can a Power BI Capacity Hosted Solution, connect to a SSAS Cube there it on our Servers Live? Power BI Premium P1 ( Power BI Premium capacity-based license)? We are new to Power BI, but went for a Hosted Model, vs. On-Prem.

[@user-ih7ot7vl8u]

How can i connecting postgresql live?? Thanks

[@VishalGupta-tc2zl]

The only problem for us is we cannot add another dataset when we choose live connection. So if we have to use analysis service live connection with local dataset then it is not supported.

[@ericgolf14]

We are running into this same issue as well. Anyone have an idea about when there will be a composite model available using both Live Connection (via SSAS) and Import mode?

[@pedropradocarvalho]

In that case you should get all data to your AS, in other words, your AS should be you DW ou Staging Area, load all your source data to your AS and go from there.

[@v5046.]

@@pedropradocarvalho so u mean to load all my source data in another PC?

[@amirdaud8855]

I am using Power Bi with Analysis services. There is no Calculated measure in the modelling tab.

[@bensalazar5763]

same...

[@joshuaharvey9271]

If you get a chance to add a link to either of those two videos you mentioned that would be great (authentication and gateway). Thanks for a great video though guys!

[@sakakiranmai9954]

@joshua, Hopefully these links would help you kzbin.info/www/bejne/h5OYdnigoK2igsk kzbin.info/www/bejne/m3K3gYZjmJyMiLc

[@GuyInACube]

Thanks for calling that out. I added them to the description. Here they are also. What is a gateway - kzbin.info/www/bejne/jXuYimd8n9CKkNk Authentication with AS Live Connections - kzbin.info/www/bejne/h5OYdnigoK2igsk

[@annnnnnnnnnnnnnnnnnnnnn]

Hi, Thanks for your sharing. Could you help me in this case: In our server we have an SSAS database. I use PBI desktop in my REMOTE SERVER MACHINE to connect the SSAS database and then publish this dataset in PBI service. I can create report using this dataset in PBI service. But when I try to connect this dataset using PBI desktop on my PERSONAL COMPUTER it has an error. It works when I connect using PBI desktop in my REMOTE SERVER MACHINE. How can I connect SSAS dataset in PBI Service from my personal computer?

[@srlabhe]

Hi Whenever I try to connect to database on AAS I get below error

[@elissandrorosa]

Hello guy. I see that you know a lot about sql server / analysis services. I would be happy if you gave me a light on how to work with cubes in SSAS. Power BI supports connecting to just one, but how to limit access to data for some PBI users? If you create cubes by business area (commercial, product ...), could you share information between cubes without impairing performance? I'm even looking for someone with this knowledge to do a little consulting with our BI project.

[@weipan3238]

hey there, when importing i have almost every field twice. One with the name and the other with name.value. They both seem to be the same so one is enough. Is there anything i missed?

[@alexkim7270]

A rough guess from what I understand about cube so far, although I hadn't made one yet. That could happen when you have the same field names in the two tables you're loading into the cube. Have you figured out your answer yet? I'd love to hear from you. I might need to heed your advice too.

[@anveshkumar6770]

Hi, I have a query regarding report server. We are building a data model and will be using live connection to connect to azure analysis service database. These report will be used by 200 of them through live connection. How does system distinguish bw Power bi report server credentials and Azure AD credentials while connecting to Power BI report server. If we want to use a live connection how would that be done is it though Azure AD for all 200 users or we can use a single explicit user credentials for all these 200 users. Does username and group name should be same for users in both report server and Azure AD, I am not sure on this, it would be great if you can explain me how the access works here in this case Please let me know if you have not understood my query.

[@ricardoacostagonzalez4018]

I get this error: "there was an error accessing the data source. contact the gateway administrator.". Any suggestions?

[@VB-bs9cq]

How you resolve? I have the same problem :(

[@giuliapiazza4603]

Hi, I've not Analysis Services, so, how can I create a user? It's free or I need a license?

[@normanmartinez7305]

Hi guys. Sorry asking here....do you have any video about CI/CD with Power BI?

[@kelechie6836]

Microsoft hasn't set this up yet but I have developed alternatives to this. Something you can do is save your PowerBI report directly to Git repository (separate folder) and use Publish Task in your powershell script - download from Powershell library and add as a step in your release pipeline. You should have some cmdlets in your script for overwrite (if exists) or add as new report in your report server (this is for on premise reports). Different process for publishing to Online - Consider publishing to a dedicated SharePoint folder and link this folder to your PowerBI service because it updates automatically when correctly linked - Rmbr to check out of SharePoint check back in! If you have many reports to publish online, use One drive and save as PowerBI template file. pbit is like a compressed form of. pbix!!

[@jdf3310]

I cannot get passed theverror: this report cannot access the data source. I'm missing something but I don't know what

[@dineshs9670]

can I restrict end user from accessing analysis server in power BI Desktop after giving access to workspace ( workspace mail id is in roles ) ? This is just to force user to use the reports I created for them.

[@GuyInACube]

If you are live connected to a model (Azure AS, SSAS or Power BI Dataset), you have to give credentials. RLS/role settings will be enforced if you are a reader only. If you have author/owner rights on the model, Roles will not be enforced.

[@vog5197]

It's probably just me, but this was a bit hard to follow. Perhaps it would be clearer if you made a separate video where you show us how to install a Gateway on a VM and connect to it from the Power BI Service.

[@h1n1worm]

gateway installation : download-install-ok-ok-finish.

[@lucasbarreira3667]

So i use a AS live connection, and measures are not enabled for me, is it because my SSAS version is 2016 or because my model is Multi-demensional ?

[@PJ5399]

Lucas we found that using Power BI desktop against traditional multi-dimensional OLAP cubes caused significant performance issues on the Analysis Services instance. If I remember correctly the issue was DAX to MDX translations causing poorly performing MDX. Multiple scenarios where SSAS would actually crash. We have made a full transition to Tabular models and performance is stellar.

[@TheKavo74]

@@PJ5399 Hi Paul just wondering if you are using live connection or Import.

[@PJ5399]

@@TheKavo74 Definitely live connection.

[@neoeason92]

@@PJ5399 Hi Paul, so there is no way to work around to put more than one SQL analysis into one PBI? :(

[@ariespinal]

I need help connecting Power BI to my wms system called Exenta. Can you help me?

[@GuyInACube]

Is there an ODBC driver available that you can use? That can be used with Power BI. How does Excel connect to it?

[@srlabhe]

An error occurred during On-Premise Gateway related activity. Additional error details: DM_GWPipeline_Gateway_DataSourceAccessError Received error payload from gateway service with ID 2934559: OLE DB connection failed to be established. OLE DB connection failed to be established ORA-12154: TNS:could not resolve the connect identifier specified

[@GuyInACube]

That's an issue with your Oracle client tools. Also, that's not a live connection. live connection is only for Analysis Services. Not Oracle. This error is a general connectivity error.

[@marto9555]

Hi Guy(s)InTheCube (I'm a big fan), but I need some help that I can't get anywhere else on the internet. I'm using Analysis Services Tabular Model, but when passing dates from my model to Power BI or Excel, date hierarchies are not carried over on Live Connection. I could go back to Import instead of Live Connection but this would take my model size and performance back down a step. How can I get dates working as well as Multi-Dimensional, in tabular?

[@GuyInACube]

Are you using Multidimensional? Hierarchies are called out as a limitation of live connections to Multidimensional. docs.microsoft.com/power-bi/desktop-ssas-multidimensional#considerations-and-limitations

[@marto9555]

@@GuyInACube Hey! No I'm using Tabular. Such a pain. Can't find any answers to how to resolve date hierarchy with sort of month names.

[@andreiuntila6211]

Hi..very cool ... but how i can remove LiveConnection?

[@GuyInACube]

If you chose live connection when you initially pulled in the data, you can't convert it to import. You would need to start over. With the new DirectQuery over Power BI Datasets, you can convert the live connection to DirectQuery, but it still wouldn't be imported. This at least allows you to enrich the existing model with some other information. Just be very careful about it as you could run into some performance problems. In general, we don't recommend importing a Power BI Dataset or AS Live connection as you lose a lot of the benefits of that semantic model.

[@andreiuntila6211]

@@GuyInACube thank you very much for your reply and your availability.

[@kumarpolisetty3048]

I just created dashboards on Office 365 app. How can i share these reports with other users. Please help on it,

[@kelechie6836]

Nope, better to publish to PowerBI Apps and permission the reports on there. It's great if you need to control who has access to the reports. Workspace are like your online place to work on your reports as a team prior to publishing to users. Check PowerBI website if you don't know how to do this.

[@JEffigy]

Meh not a great story from a vertipac engine perspective...why can’t I see a read-only view of the data model? Kinda funky it requires the gateway account to be a SSAS Server Admin. Be awesome if you did a video on ETL through SQL to SSAS Tabular and another through PBI. Lines of demarcation are just all over the shop these days. Just showing the use case or MS best practices would be great.

[@GuyInACube]

Thanks for your thoughts Jeff. The admin account is required due to the use of EffictiveUserName for impersonation. This requires that the initial credential be a server admin. This has been the case for Analysis Services for a long while. We can take a look at the ETL piece. Definitely a larger story to tell with regards to your data journey.

[@wiktorleszczynski6002]

salog hurtownie dzienki

[@prabhatdass6184]

Hi Adam, i need to know how we can check the total refreshing/processing time of a AAS model.

[@keyquick2560]

I get this error: "there was an error accessing the data source. contact the gateway administrator.". Any suggestions?

[@GuyInACube]

Could be for different reasons. For a live connection, usually my guess would be that the account you used in the data source, within manage gateways, isn't a server administrator on the SSAS server.

[@edwinmartinez1628]

@@GuyInACube I also have the same problem, and the credentials that I put are the account that I have on my computer, I put it in the format DOMAIN \ myaccount, and the password that I use to access my computer ... Also if I check the cube in analysis services my role is administrator at the sql server level