Data architecture question and hardware - Analysis services

[ITManuel]

Hi all,

I have a few questions in relation to data architecture and hardware I'm working with, since I'm recently getting time out error during data refresh.

I get data from an ERP system via an Analysis services cube. In the cube no data transformation nor modelling is done, raw data is provided and security is managed via RLS. This is the choice our IT department has made basing on an external consultant. 

Now I'm importing raw data from AS into Power BI and denormalizing tables in order to create a sound data model which in my case ends up to be more a snowflake- rather than a star schema.

In any case the solution was working for weeks and data refresh times and performance of the report were fine.

Since two days I cannot refresh the model anymore because of a data source time out error ( "OLE DB or ODBC error: [DataSource.Error] AnalysisServices: The connection either timed out or was lost").

Is this more likely to be caused by the data source or can it be caused by my model? 

In any case I tried to remove columns in Power Query but I do get the same "Time out error" also in Power Query when the preview of some Merge operations are loaded. 

In one case I'm merging 3 Tables into one ( Table 1: 500k rows, Table 2: 1,400k rows , Table 3: 800k rows)  The 16GB RAM  of my PC gets 99% full and CPU loads reaches 90%. If the time out error does not appear it takes 10min+ to load the preview.

My main questions are:

1) Is the main architecture fit for purpose considering that much more data is suppost to be imported from AS into PBI moving forward?

2) Is my PC hardware, AMD Ryzen 5 PRO 5675U | 16GB RAM 2667MHz, performant enough for the type of operations I'm doing or does it provide a strong limitation?

Thanks in advance

Best regards

[Anonymous]

Hi  @ITManuel ,

You can run SQL Profiler while refreshing. you should be able to see if there are errors in SSAS and why.

It could be memory or CPU pressure occurring at the same time you are trying to refresh.

Use SQL Server Profiler to Monitor Analysis Services | Microsoft Learn

This is the related document, you can view this content:

Timout on SQL Server Analysis Services Database wi... - Microsoft Fabric Community

Automatic aggregations overview - Power BI | Microsoft Learn

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ITManuel]

Hi @Anonymous ,

thank you for your respone, sorry it took me a while to get the SQL Server Profiler and run some tests. I was not able to connect the SQL Server profiler directly to the Analysis services source model, but I connected the Profiler to PowerBI from DaxStudio and had DaxStudio connected to the Analysis services source model.

• I did a test in merging 3 tables coming from Analysis services :
  • Table 1: 4 Columns | 496,812 rows
  • Table 2: 5 Columns | 1,351,618 rows
  • Table 3: 8 Columns | 823,338 rows

First I do left outer join of Table 1 and Table 2, Table 3 is joined to the result again with a left outer join.

The refresh works without an error, but takes 4min and 38sec to complete. The result is a table with 17 columns | 1,398,702 rows.

Are the 4 min 38sec considered to be "normal"for this type of operation? The refresh of Table 1, 2 & 3 is folded back to the source ( "View native query" is available in Power Query), the merge operation seems not to be folded back. 

While the refresh window shows the refresh in Power BI Desktop, the number of rows loaded counts until 511,623 rows and stops there, even if the final table has 1,398,702rows. Does this has any meaning?

• For the next test, I added one further column for Table 3:
  • Table 1: 4 Columns | 496,812 rows
  • Table 2: 5 Columns | 1,351,618 rows
  • Table 3: 9 Columns | 823,338 rows

This time, the load operation from Power Quera into Power BI fails after 3min with the following error message:

I cannot see any error message in the SQL Server Profiler trace which is connected to Power BI. Under the "All Queries" trace in DaxStudio which is connected to the Analysis services data source, the following error message appears:

"Die Anforderung wurde abgebrochen, weil die Verbindung geschlossen wurde. Die Anforderung wurde abgebrochen, weil die Verbindung geschlossen wurde." 

which would transalate to:

"The request was canceled because the connection was closed. The request was canceled because the connection was closed."

The first errror message "Der Vorgang wurde vom Benutzer abgebrochen. Der Vorgang wurde vom Benutzer abgebrochen."

which translates to

"The operation was canceled by the user. The operation was canceled by the user."

also appeard during the first test when refreh was succesful. There was no attempt to cancel the refresh in Power BI from the users side.

[ITManuel]

Sorry I have to correct myself. The following error messages appear in the SQL Server Profiler trace: