question

AvirupKundu-0830 avatar image
0 Votes"
AvirupKundu-0830 asked LukasYu-msft commented

0 rows transferred when deploying SSAS Tabular Model

I am creating a calculated table inside my model with the following code

=
VAR TodaysDate = MAX(factSalesDetails[Invoice_Date])
VAR YearStart = CALCULATE(STARTOFYEAR(factSalesDetails[Invoice_Date]), YEAR(factSalesDetails[Invoice_Date]) = YEAR(TodaysDate))
VAR QuarterStart = CALCULATE(STARTOFQUARTER(factSalesDetails[Invoice_Date]), YEAR(factSalesDetails[Invoice_Date]) = YEAR(TodaysDate), QUARTER(factSalesDetails[Invoice_Date]) = QUARTER(TodaysDate))
VAR MonthStart = CALCULATE(STARTOFMONTH(factSalesDetails[Invoice_Date]), YEAR(factSalesDetails[Invoice_Date]) = YEAR(TodaysDate), MONTH(factSalesDetails[Invoice_Date]) = MONTH(TodaysDate))
VAR RESULT =
UNION (
ADDCOLUMNS(
CALENDAR(YearStart, TodaysDate),
"Selection", "YTD"
),
ADDCOLUMNS(
CALENDAR(QuarterStart, TodaysDate),
"Selection", "QTD"
),
ADDCOLUMNS(
CALENDAR(MonthStart, TodaysDate),
"Selection", "MTD"
)
)
RETURN RESULT



It returns the table properly as shown
93651-image.png

However when trying to deploy, it says 0 rows transferred, but there is no other error.
93661-image.png

I am using SQL Server 2019 and Visual Studio 2019 with latest SSDT plugin.
I changed the Processing Option to "Full" but that didn't make any difference.
What is the problem here?

sql-server-analysis-services
1.png (40.4 KiB)
image.png (37.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

LukasYu-msft avatar image
0 Votes"
LukasYu-msft answered LukasYu-msft commented

Hi,

Have you checked the table in deployed model, for example, try view the fields in Excel pivot table? If there is no problem in the model, then you don't need to worry about this. This could be a lazy process of the SSAS engine since there is no new data in the calculated table.

Lukas

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I did some testing on this. I think there is a bug in SSAS. The Quarter Function cannot be used as a filter in Calculate. For example, If I comment out the Quarter like this

VAR QuarterStart = CALCULATE(STARTOFQUARTER(factSalesDetails[Invoice_Date]), YEAR(factSalesDetails[Invoice_Date]) = YEAR(TodaysDate)) //, QUARTER(factSalesDetails[Invoice_Date]) = QUARTER(TodaysDate))

Then the table deploys just fine. All rows get transferred. I can verify the table is successfully deployed by viewing it in PowerBI

If I use the Quarter function as a filter, then the table is successfully created in SSAS. It is not empty. But it doesn't deploy and 0 rows are transferred. When trying to import in PowerBI, it throws an error that the table is not processed.





0 Votes 0 ·

Well, still hard for me to say. Have you tested if there is not logic error or data conflict in the model, that is there actually data exists after the filter?

0 Votes 0 ·