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++ 💥
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.
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?
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.
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?
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?
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.
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?
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.
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
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?
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?
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??
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!
Thanks for calling that out. I added them to the description. Here they are also. What is a gateway - czcams.com/video/WJ5Y4FjkWZw/video.html Authentication with AS Live Connections - czcams.com/video/Qb5EEjkHoLg/video.html
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.
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!!
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?
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.
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!!
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
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 "
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!!
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.
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.
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.
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?
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.
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.
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
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.
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.
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?
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
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.
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.
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.
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.
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
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.
@@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
2 years later and it worked like a charm. Thanks
Can I ask you an advice? I don't understand how it works :'(
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++ 💥
Wow that's insane how you made it sound so easy to understand.
Explained in such simple words... Yet to do it, will surely come back if I have doubts :D
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.
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?
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.
@@pedropradocarvalho so u mean to load all my source data in another PC?
Just a good professional demo. Thanks
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?
Thank you @guyinacube this is timely!
I am using Power Bi with Analysis services. There is no Calculated measure in the modelling tab.
same...
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?
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.
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?
Thank You
Hey Adam, Is there a way we could test RLS applied on SSAS model directly on Power BI, instead of using DAX Studio?
Hi Adam, i need to know how we can check the total refreshing/processing time of a AAS model.
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.
Hey @Guy in a Cube can u please adress to the problem of date hierarchy in live connect. Thanks in advance
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
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?
I'd llike to know how you made that awesome visual, that's the part I struggle with.
I get this error: "there was an error accessing the data source. contact the gateway administrator.". Any suggestions?
How you resolve? I have the same problem :(
Hi, I have a query regarding Dashboard based on live connection in cube. Are you able to download the underlying data?
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?
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??
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!
@joshua, Hopefully these links would help you
czcams.com/video/Qb5EEjkHoLg/video.html
czcams.com/video/eATPS-c7YRU/video.html
Thanks for calling that out. I added them to the description. Here they are also.
What is a gateway - czcams.com/video/WJ5Y4FjkWZw/video.html
Authentication with AS Live Connections - czcams.com/video/Qb5EEjkHoLg/video.html
Very thanks for you
Can we make a live connection from powerbi to ADLS Gen2 or a SQLServer?
Is possible to use PowerBi Web and Gateway without SSAS Tabular, i'm using a multidimensional model
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.
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!!
Hello guys, While connecting Power BI report to Azure Analysis Services via Live connection can we create subscriptions on the report?
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?
whether the analysis service cannot use what if models ?
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.
gateway installation : download-install-ok-ok-finish.
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!!
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
How to access the AD for local SSAS Tabular Server?
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 "
Hi, I've not Analysis Services, so, how can I create a user? It's free or I need a license?
Hi guys. Sorry asking here....do you have any video about CI/CD with Power BI?
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!!
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.
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.
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.
I cannot get passed theverror: this report cannot access the data source. I'm missing something but I don't know what
How can i connecting postgresql live?? Thanks
I need help connecting Power BI to my wms system called Exenta. Can you help me?
Is there an ODBC driver available that you can use? That can be used with Power BI. How does Excel connect to it?
Hi Whenever I try to connect to database on AAS I get below error
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?
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.
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.
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
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.
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 ?
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.
@@PJ5399 Hi Paul just wondering if you are using live connection or Import.
@@TheKavo74 Definitely live connection.
@@PJ5399 Hi Paul, so there is no way to work around to put more than one SQL analysis into one PBI? :(
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?
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
@@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.
Hi..very cool ... but how i can remove LiveConnection?
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.
@@GuyInACube
thank you very much for your reply and your availability.
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.
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.
I just created dashboards on Office 365 app. How can i share these reports with other users. Please help on it,
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.
salog hurtownie dzienki
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
I get this error: "there was an error accessing the data source. contact the gateway administrator.". Any suggestions?
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.
@@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