Two Part Question - Analysis Services Connection

[lucas9e]

Our company has a program McLeod that integrates with SQL Server. I am trying to pull our data into PBI through the Analysis Services under Get Data. 

1) If I do a live connection all the data loads up right away. However, I don't want to use a live connection because I am not able to create new dimensions and transform the data. If I use import instead of a live connection will I be able to make new dimensions and then just refresh the data as needed, sort of replacing the need for a live connection?

2) When I am trying to import data I am getting a loading screen that is lasting forever. Why is this? Live connection loads everything instantly. Data will also load in excel instantly. (there is also a yellow banner at the top that pops up when i click import " There are pending changes in your queries that haven't been applied")

Would anyone be able to help with this?

[Arul · Accepted solution]

@lucas9e ,

1. Yes, if you use the "Import" option in Analysis Services to bring in data into Power BI, you will be able to create new dimensions and transform the data. You can use Power Query Editor to apply transformations to the imported data and then create new dimensions using the "Manage Relationships" option under the "Modeling" tab. Once you have created your new dimensions, you can refresh the data as needed by clicking the "Refresh" button under the "Home" tab in Power BI Desktop. This will update the data in your model without the need for a live connection.
2. The reason for the long loading screen when you try to import data may be due to a large amount of data being imported or the complexity of the queries used to fetch the data. When you create a live connection, data is queried directly from the source database, which can be faster than importing data into Power BI. However, as you mentioned, a live connection may not provide the flexibility to create new dimensions or apply transformations.
3. The yellow banner you are seeing may indicate that there are some changes to the queries in your data model that have not been applied. You can click on the banner to apply the changes and see if that resolves the issue. If not, you can try optimizing your queries by removing unnecessary filters or reducing the amount of data being fetched.

If you continue to have issues with importing data into Power BI, you may want to consider optimizing the queries or consulting with a database administrator to ensure that the database is properly configured for efficient querying.
Thanks,

Arul