Analysis Services Power Query Editor performance
up vote
0
down vote
favorite
Currently I am building a model in Visual Studio for Azure Analysis Services, but I am experiencing very slow performance of the Power Query editor.
I am trying to do a left join on a table of about 1.600.000 rows. The table I am joining with is around 50 million rows. The merge-step works, but when I try to expand the columns it is downloading all the 50M rows for some reason. At least the status bar at the bottom indicates this.
This is quite annoying as it will do this every time I try to edit the query sequence.
- Already tried setting several indexes on the SQL table
- The Azure SQL server does not show usage peaks of 100%, max 80% sometimes
Any ideas how to solve this?
sql
azure ssas powerquery azure-analysis-services asked Oct 31 at 21:56
user3596100
164
add a comment |
up vote
0
down vote
favorite
Currently I am building a model in Visual Studio for Azure Analysis Services, but I am experiencing very slow performance of the Power Query editor.
I am trying to do a left join on a table of about 1.600.000 rows. The table I am joining with is around 50 million rows. The merge-step works, but when I try to expand the columns it is downloading all the 50M rows for some reason. At least the status bar at the bottom indicates this.
This is quite annoying as it will do this every time I try to edit the query sequence.
- Already tried setting several indexes on the SQL table
- The Azure SQL server does not show usage peaks of 100%, max 80% sometimes
Any ideas how to solve this?
sql
azure ssas powerquery azure-analysis-services asked Oct 31 at 21:56
user3596100
164
Is this a tabular model? Are you using DirectQuery (if it's available)? Do the 1.6 million rows have matches in all of the 50 million rows?
– Alejandro Lopez-Lago - MSFT
Nov 2 at 18:06
Also, can you connect directly to the Azure SQL table instead of using AS? Is there a reason you would prefer to connect to the AS instance instead of the SQL Server instance?
– Alejandro Lopez-Lago - MSFT
Nov 5 at 19:01
This is a tabular model, and not using Direct Query. All 1.6M rows have matches. I am using AS because I need to create a model in there for use in PowerBI
– user3596100
Nov 6 at 9:48
Could you create the model in PowerBI or SSDT instead? You'll get better performance if you connect to the Azure SQL Server instance and apply the transformations, and then load the results into the PowerBI or SSDT model. Do you need Analysis Services-specific features like row or object level security?
– Alejandro Lopez-Lago - MSFT
Nov 6 at 18:04
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
Currently I am building a model in Visual Studio for Azure Analysis Services, but I am experiencing very slow performance of the Power Query editor.
I am trying to do a left join on a table of about 1.600.000 rows. The table I am joining with is around 50 million rows. The merge-step works, but when I try to expand the columns it is downloading all the 50M rows for some reason. At least the status bar at the bottom indicates this.
This is quite annoying as it will do this every time I try to edit the query sequence.
- Already tried setting several indexes on the SQL table
- The Azure SQL server does not show usage peaks of 100%, max 80% sometimes
Any ideas how to solve this?
sql
azure ssas powerquery azure-analysis-services asked Oct 31 at 21:56
user3596100
164
Currently I am building a model in Visual Studio for Azure Analysis Services, but I am experiencing very slow performance of the Power Query editor.
I am trying to do a left join on a table of about 1.600.000 rows. The table I am joining with is around 50 million rows. The merge-step works, but when I try to expand the columns it is downloading all the 50M rows for some reason. At least the status bar at the bottom indicates this.
This is quite annoying as it will do this every time I try to edit the query sequence.
- Already tried setting several indexes on the SQL table
- The Azure SQL server does not show usage peaks of 100%, max 80% sometimes
Any ideas how to solve this?
sql
azure ssas powerquery azure-analysis-services sql
azure ssas powerquery azure-analysis-services asked Oct 31 at 21:56
user3596100
164
asked Oct 31 at 21:56
user3596100
164
asked Oct 31 at 21:56
user3596100
164
asked Oct 31 at 21:56
user3596100
164
asked Oct 31 at 21:56
user3596100
164
164
Is this a tabular model? Are you using DirectQuery (if it's available)? Do the 1.6 million rows have matches in all of the 50 million rows?
– Alejandro Lopez-Lago - MSFT
Nov 2 at 18:06
Also, can you connect directly to the Azure SQL table instead of using AS? Is there a reason you would prefer to connect to the AS instance instead of the SQL Server instance?
– Alejandro Lopez-Lago - MSFT
Nov 5 at 19:01
This is a tabular model, and not using Direct Query. All 1.6M rows have matches. I am using AS because I need to create a model in there for use in PowerBI
– user3596100
Nov 6 at 9:48
Could you create the model in PowerBI or SSDT instead? You'll get better performance if you connect to the Azure SQL Server instance and apply the transformations, and then load the results into the PowerBI or SSDT model. Do you need Analysis Services-specific features like row or object level security?
– Alejandro Lopez-Lago - MSFT
Nov 6 at 18:04
add a comment |
Is this a tabular model? Are you using DirectQuery (if it's available)? Do the 1.6 million rows have matches in all of the 50 million rows?
– Alejandro Lopez-Lago - MSFT
Nov 2 at 18:06
Also, can you connect directly to the Azure SQL table instead of using AS? Is there a reason you would prefer to connect to the AS instance instead of the SQL Server instance?
– Alejandro Lopez-Lago - MSFT
Nov 5 at 19:01
This is a tabular model, and not using Direct Query. All 1.6M rows have matches. I am using AS because I need to create a model in there for use in PowerBI
– user3596100
Nov 6 at 9:48
Could you create the model in PowerBI or SSDT instead? You'll get better performance if you connect to the Azure SQL Server instance and apply the transformations, and then load the results into the PowerBI or SSDT model. Do you need Analysis Services-specific features like row or object level security?
– Alejandro Lopez-Lago - MSFT
Nov 6 at 18:04
Is this a tabular model? Are you using DirectQuery (if it's available)? Do the 1.6 million rows have matches in all of the 50 million rows?
– Alejandro Lopez-Lago - MSFT
Nov 2 at 18:06
Is this a tabular model? Are you using DirectQuery (if it's available)? Do the 1.6 million rows have matches in all of the 50 million rows?
– Alejandro Lopez-Lago - MSFT
Nov 2 at 18:06
Also, can you connect directly to the Azure SQL table instead of using AS? Is there a reason you would prefer to connect to the AS instance instead of the SQL Server instance?
– Alejandro Lopez-Lago - MSFT
Nov 5 at 19:01
Also, can you connect directly to the Azure SQL table instead of using AS? Is there a reason you would prefer to connect to the AS instance instead of the SQL Server instance?
– Alejandro Lopez-Lago - MSFT
Nov 5 at 19:01
This is a tabular model, and not using Direct Query. All 1.6M rows have matches. I am using AS because I need to create a model in there for use in PowerBI
– user3596100
Nov 6 at 9:48
This is a tabular model, and not using Direct Query. All 1.6M rows have matches. I am using AS because I need to create a model in there for use in PowerBI
– user3596100
Nov 6 at 9:48
Could you create the model in PowerBI or SSDT instead? You'll get better performance if you connect to the Azure SQL Server instance and apply the transformations, and then load the results into the PowerBI or SSDT model. Do you need Analysis Services-specific features like row or object level security?
– Alejandro Lopez-Lago - MSFT
Nov 6 at 18:04
Could you create the model in PowerBI or SSDT instead? You'll get better performance if you connect to the Azure SQL Server instance and apply the transformations, and then load the results into the PowerBI or SSDT model. Do you need Analysis Services-specific features like row or object level security?
– Alejandro Lopez-Lago - MSFT
Nov 6 at 18:04
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
I noticed in SSMS that the PowerQuery editor creates so-called folded-queries and also introduces sorting statements which I don't setup in the editor.
So I fixed my performance issues by enabling legacy datasources in the Visual Studio options. With this I can write my own SQL statement which are many times faster.
Does anyone know why this is happening in the PowerQuery editor, and if using this legacy way of working has drawbacks compared to the editor?
@alejandro: I need Analysis Services mainly for the fast cache it provided. I tried to load the tables in PowerBI directly, but this became totally unresponsive.
answered Nov 12 at 10:39
user3596100
164
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53092514%2fanalysis-services-power-query-editor-performance%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
I noticed in SSMS that the PowerQuery editor creates so-called folded-queries and also introduces sorting statements which I don't setup in the editor.
So I fixed my performance issues by enabling legacy datasources in the Visual Studio options. With this I can write my own SQL statement which are many times faster.
Does anyone know why this is happening in the PowerQuery editor, and if using this legacy way of working has drawbacks compared to the editor?
@alejandro: I need Analysis Services mainly for the fast cache it provided. I tried to load the tables in PowerBI directly, but this became totally unresponsive.
answered Nov 12 at 10:39
user3596100
164
add a comment |
up vote
0
down vote
I noticed in SSMS that the PowerQuery editor creates so-called folded-queries and also introduces sorting statements which I don't setup in the editor.
So I fixed my performance issues by enabling legacy datasources in the Visual Studio options. With this I can write my own SQL statement which are many times faster.
Does anyone know why this is happening in the PowerQuery editor, and if using this legacy way of working has drawbacks compared to the editor?
@alejandro: I need Analysis Services mainly for the fast cache it provided. I tried to load the tables in PowerBI directly, but this became totally unresponsive.
answered Nov 12 at 10:39
user3596100
164
add a comment |
up vote
0
down vote
up vote
0
down vote
I noticed in SSMS that the PowerQuery editor creates so-called folded-queries and also introduces sorting statements which I don't setup in the editor.
So I fixed my performance issues by enabling legacy datasources in the Visual Studio options. With this I can write my own SQL statement which are many times faster.
Does anyone know why this is happening in the PowerQuery editor, and if using this legacy way of working has drawbacks compared to the editor?
@alejandro: I need Analysis Services mainly for the fast cache it provided. I tried to load the tables in PowerBI directly, but this became totally unresponsive.
answered Nov 12 at 10:39
user3596100
164
I noticed in SSMS that the PowerQuery editor creates so-called folded-queries and also introduces sorting statements which I don't setup in the editor.
So I fixed my performance issues by enabling legacy datasources in the Visual Studio options. With this I can write my own SQL statement which are many times faster.
Does anyone know why this is happening in the PowerQuery editor, and if using this legacy way of working has drawbacks compared to the editor?
@alejandro: I need Analysis Services mainly for the fast cache it provided. I tried to load the tables in PowerBI directly, but this became totally unresponsive.
answered Nov 12 at 10:39
user3596100
164
answered Nov 12 at 10:39
user3596100
164
answered Nov 12 at 10:39
user3596100
164
answered Nov 12 at 10:39
user3596100
164
164
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53092514%2fanalysis-services-power-query-editor-performance%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Is this a tabular model? Are you using DirectQuery (if it's available)? Do the 1.6 million rows have matches in all of the 50 million rows?
– Alejandro Lopez-Lago - MSFT
Nov 2 at 18:06
Also, can you connect directly to the Azure SQL table instead of using AS? Is there a reason you would prefer to connect to the AS instance instead of the SQL Server instance?
– Alejandro Lopez-Lago - MSFT
Nov 5 at 19:01
This is a tabular model, and not using Direct Query. All 1.6M rows have matches. I am using AS because I need to create a model in there for use in PowerBI
– user3596100
Nov 6 at 9:48
Could you create the model in PowerBI or SSDT instead? You'll get better performance if you connect to the Azure SQL Server instance and apply the transformations, and then load the results into the PowerBI or SSDT model. Do you need Analysis Services-specific features like row or object level security?
– Alejandro Lopez-Lago - MSFT
Nov 6 at 18:04