Search code examples
sql-serverssasssas-tabular

SSAS Importing Data from SQL Server performance

I've created ssas tabular in import mode. In power query editor, i can import with multiple ways below;

1.

let Source = #"SQL/localhost;ReportDb",

dbo_Fact_Sales = Value.NativeQuery(
#"SQL/localhost;ReportDb",
"SELECT Column1, Column2, Column3 FROM dbo.Fact_Sales WHERE Date_Id >= 20180101 AND Interval_Id = 1")

in

dbo_Fact_Sales

let Source = #"SQL/localhost;ReportDb",

dbo_Fact_Sales = Source{[Schema="dbo",Item="Fact_Sales"]}[Data]
#"Filtered Rows" = Table.SelectRows(dbo_Fact_Sales, each [Interval_Id] = 1),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Date_Id] >= 20180101)
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Date_Id","Interval_Id"})

in

#"Removed Columns"

Is there any performance difference between 1 and 2 when refreshing data?

Also, what is the difference between table processing and partition processing?

Solution

  • 1- The first query seems faster since it retrieves a limited number of columns and a subset of rows.

    2- the difference between partition processing and table processing is that:

    • Partition processing operations processes a limited subset of the table which makes it faster and consumes less memory and CPU.
    • Table processing operations deal with the entire table and related structures which makes it slower, consumes a lot of memory and CPU. Processing a table means processing each partition of the table.