SSAS Tabular Model performance Issue
Hi I have a SSAS tabular model of almost 2 GB size. I refresh it's data using Azure Analysis Service on S1 tier(P.S. I have tried with S2 tier too but it still consume same amount of time) and refresh request is generated through ADF. Earlier it was working fine and refreshing data with 40 mins for all requested partitions. Now someone has added few new DAX measures to my model and since then me refresh time has increased to 3 hours. I want to optimize my model and reduce refresh time. I am only refreshing the relevant data(current data not the historical data). How can I reduce refresh time and optimize my tabular model. I tried reducing the data but still its taking long time to process the data which is not the ideal case. I guess the effectiveness is effected due to any DAX measure addition but still its just a guess. I dont know the method to identify the cause as I have too many DAX measures in my model.
calculated columns are not populated in Bridge table correctly
Hi, Although this question directly relates to Azure Analysis Service but still posting in the power bi. I have a model in AAS and there I have to create some calculated columns. First I tried to create that in on of the actual table and it gave me circular dependency (because in my model maximum of my actual tables are connected through bridge tables). Then I tried to implement those calculated columns in the bridge table but the column is coming as blank in report. When I debug the DAX I found that the filter (which I am using in DAX ) is not working when I am calculating the column. I am sharing my code and the model snap shot here. CalculatedGTWDate_SLN:: =VAR GTWDate = SWITCH ( TRUE (), ISBLANK ( MIN ( SFDC_Opportunity_NBV[GTWClosedDate] ) ), DATE ( 9999, 12, 31 ), DATE ( LEFT ( CALCULATE ( MIN ( SFDC_Opportunity_NBV[GTWClosedDate] ), ALLEXCEPT ( SalesR48NBVBySolution, CalculatedAccountNumber_NP[AccountNumber], SalesR48NBVBySolution[Program],SalesR48NBVBySolution[L3Code] ) ), 4 ), RIGHT ( LEFT ( CALCULATE ( MIN ( SFDC_Opportunity_NBV[GTWClosedDate] ), ALLEXCEPT ( SalesR48NBVBySolution, CalculatedAccountNumber_NP[AccountNumber], SalesR48NBVBySolution[Program],SalesR48NBVBySolution[L3Code] ) ), 7 ), 2 ),1 ) ) RETURN GTWDate CalculatedFirstPurchaseDateAfterGTWCloseDate_SLN:: =VAR GTWDate = CalculatedAccountNumber_NP[CalculatedGTWDate_SLN] RETURN CALCULATE ( MIN ( SalesR48NBVBySolution[invoicedate] ), SalesR48NBVBySolution[InvoiceDate] >= GTWDate ) CalculatedCloseDateVSFirstPurchaseDate_SLN:: =DATEDIFF(CalculatedAccountNumber_NP[CalculatedFirstPurchasedateAfterGTWClosedDate_SLN], MAX(SFDC_Opportunity_NBV[ClosedMonth]),MONTH) CalculatedAnnualization_SLN:: =VAR Denominator = (CalculatedAccountNumber_NP[CalculatedClosedDateVSFirstPurchaseDate_SLN]+ 1 ) VAR Sales = CALCULATE ( SUM ( SalesR48NBVBySolution[TotalSales] ), FILTER ( SalesR48NBVBySolution, SalesR48NBVBySolution[invoicedate] >= CalculatedAccountNumber_NP[CalculatedFirstPurchasedateAfterGTWClosedDate_SLN] ) ) VAR R6AnchorSales = CALCULATE ( SUM ( SalesR48NBVBySolution[TotalSales] ), FILTER ( SalesR48NBVBySolution, SalesR48NBVBySolution[invoicedate] >= EDATE ( CalculatedAccountNumber_NP[CalculatedFirstPurchasedateAfterGTWClosedDate_SLN], -6 ) && SalesR48NBVBySolution[invoicedate] < CalculatedAccountNumber_NP[CalculatedFirstPurchasedateAfterGTWClosedDate_SLN] ) ) RETURN IF ( ISBLANK ( R6AnchorSales ), IF ( NOT (ISBLANK ( CalculatedAccountNumber_NP[CalculatedFirstPurchasedateAfterGTWClosedDate_SLN]) ) && ISBLANK ( R6AnchorSales ), DIVIDE ( Sales, Denominator ) * 12 ) ) Note::Other than CalculatedAnnualization_SLN the columns are populating fine in the report. Could you please guide me how to resolve this
PBI Live connect to AAS (dynamic columns in matrix) ?
Hi all, Iam in trouble with creating matrix in PBI which will dynamically change columns/rows based on selection from slicer. Iam using live connect to Azure analysis tabular model actually and trying to do following solution: static data table for colum names, measure to identify change, row_selector:=sum('Value selector row'[Order]) calculated column "rows" = var x1=IF([row_selector]>39,1,[row_selector]) var x2= CALCULATE(VALUES('Value selector row'[Name]),FILTER('Value selector row','Value selector row'[Order]=x1)) return switch( x2, "Stredisko",RELATED('xy1'[x)]), "Prevádzka",RELATED('xy2'[y)]), "Pokladňa",RELATED('xy3'[z]), "N/A" ) In AAS visual studio is everything working fine, but after deploy to PBI row value is not changing either when underlying measure changes correctly. Is there some way to refresh calculated colum when PBI (live connected) slicer selection changes ? Thank youPower BI Report Server - Live Connection to Azure Analysis Services
I have an Power BI Report Server and the associated edition of Power BI Desktop (September 2019). I am trying to create a Power BI report that gets data from an Azure Analysis Services tabular model via a live connection. After selecting Get Data > Azure Analysis Services database and entering the database details, I'm first prompted for my credentials, but then get the following error message: Cannot load model We couldn't connect to your Analysis Services database. Double-check that your server and database names are correct, and make sure you have permission to access them. Resolution of actual cluster endpoint of Azure Analysis Server: 'asazure://<region>.asazure.windows.net/<instance>' failed. Response from server: Technical details: RootActivityId: Date (UTC): I can import data into Power BI and I can connect to the model via SQL Server Management Studio using the same credentials, so not sure why I can't create a live connection? I am working on a server that might have some firewall restrictions. Power BI version: 2.73.5586.1501 64-bit (September 2019)
Dynamic row level security with Azure Analysis services tabular model Not working
Hi, I am using this Microsoft Article @ https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-row-level-security-onprem-ssas-tabular -->Dynamic row level security with Analysis services tabular model Instead of using On-Prem Data sources I have used Azure SQL Database (AdventureWorksDW) and Created Azure Analysis Services Instance. Also in this I have used Azure ID in the format Username@Domain Name in the userSecurity table. But despite following up all the steps this is not working. Regards Rajaniesh
