Importing SSAS tabular data, Power BI stuck on evaluating

[JargerBiirli]

I'm trying to read data from an SSAS tabular source using import mode. The source works fine with a live connection, but while using import, Power BI keeps ”evaluating” and it never ends. The source data is not particularly large or the model complex. I tried importing only a single column from a dimension table of four rows, and it isn't working either. When trying to import a minimal subset of the data, it gets past "evaluating" to "importing data to model" (or something similar in English), but won't proceed beyond that. When creating the data source, I can see the tables and columns in the model, so it's not like the connection isn't working.

I've looked at this from the database side and it doesn't look like Power BI is even querying anything from the SSAS database while it's evaluating. So what exactly is it doing, and more importantly, is there a remedy? I've tried various tricks I found by searching, such as clearing the cache or disabling importing relationships, but none are helping.

[dbeavon3]

@JargerBiirli Did you figure this out?

Are you an SSAS admin?

Perhaps you can run a profiler trace against the back-end database?

I'm having very odd issues as well, and the culprit seems to be the query,

select [MEASURE_UNIQUE_NAME], [MEASURE_CAPTION], [DATA_TYPE], [MEASUREGROUP_NAME], [MEASURE_DISPLAY_FOLDER] from $system.mdschema_measures where [CUBE_NAME] = @CubeName and [MEASURE_IS_VISIBLE]

For whatever reason, this metadata query seems to create a conflict in SSAS with processing operations.  In general there should never be conflicts between SSAS readers and writers until the commit phase (which takes only a second).  But something really weird is going on with this SQL metadata query....

... I'm finding that the conflicts happen much sooner than you would expect.  I'm finding that in some cases when Power BI is trying to refresh data, then it may end up being locked for the entire duration of the processing operation (~10 mins), and subsequently cancelled during the commit phase (after another ~30 seconds of not completing).

These types of metadata queries should be instantaneous! So I find it highly suspicious that they would end up taking 10 mins, and then needing to be cancelled as part of the commit phase.  We are running SSAS on SQL 2016 CU 12 (13.0.5698.0).

[JargerBiirli · Accepted solution]

I sort of did, though I’m not sure if it applies to my original problem here. I ended up having various issues where Power BI Desktop would not import data from an SQL Server source either (using which was my first workaround), even when the datasets in question were very small. Ultimately the issue was that we were running Power BI in a Hyper-V virtual Windows machine, and the solution was to increase the number of virtual cores from one to at least two.

Power BI Desktop requirements are given as "CPU: 1 gigahertz (GHz) 64-bit (x64) processor or better recommended", with no mention of multicore being a requirement. Maybe @rufengli  or someone else at MS should amend this or look into it? I understand that 64-bit processors in practice may always be at least dual-core, but considering that virtualised environments are not uncommon, it would not hurt to clarify this. Also, it would have been helpful if Power BI could throw some sort of error in such cases, instead of running a query forever.

Hopefully this is helpful.

[rufengli]

Hi,

Thanks for reporting the problem.  Could you provide more details on the issue?  The best way for us to diagnose the issue is to provide screenshot and tracing files for issues like this.

Thanks,

Rufeng 

[JargerBiirli]

Thank you for replying.

I can't post such things as-is on a public forum due to privacy issues, but here's where it gets stuck when trying to import a single column from a single dimension table:

Upon reexamining the extended events output, I noticed that I was mistaken: there actually are some queries being passed to the Analysis Services database. I'm pasting them below:

select [CUBE_NAME], [BASE_CUBE_NAME], [CUBE_CAPTION] from $system.mdschema_cubes where [CUBE_SOURCE] = 1

select [MEASURE_UNIQUE_NAME], [MEASURE_CAPTION], [DATA_TYPE], [MEASUREGROUP_NAME], [MEASURE_DISPLAY_FOLDER] from $system.mdschema_measures where [CUBE_NAME] = @CubeName and [MEASURE_IS_VISIBLE]

select [KPI_NAME], [KPI_CAPTION], [MEASUREGROUP_NAME], [KPI_DISPLAY_FOLDER], [KPI_GOAL], [KPI_STATUS], [KPI_TREND], [KPI_VALUE] from $system.mdschema_kpis where [CUBE_NAME] = @CubeName

select [DIMENSION_UNIQUE_NAME], [DIMENSION_CAPTION] from $system.mdschema_dimensions where [CUBE_NAME] = @CubeName and [DIMENSION_UNIQUE_NAME] <> '[Measures]'

select [DIMENSION_UNIQUE_NAME], [HIERARCHY_UNIQUE_NAME], [HIERARCHY_CAPTION], [HIERARCHY_DISPLAY_FOLDER], [HIERARCHY_ORIGIN], [HIERARCHY_IS_VISIBLE] from $system.mdschema_hierarchies where [CUBE_NAME] = @CubeName and [DIMENSION_UNIQUE_NAME] <> '[Measures]'

select [DIMENSION_UNIQUE_NAME], [HIERARCHY_UNIQUE_NAME], [LEVEL_UNIQUE_NAME], [LEVEL_NUMBER], [LEVEL_CAPTION] from $system.mdschema_levels where [CUBE_NAME] = @CubeName and [LEVEL_NAME] <> '(All)' and [DIMENSION_UNIQUE_NAME] <> '[Measures]'

select [MEASUREGROUP_NAME], [MEASUREGROUP_CAPTION] from $system.mdschema_measuregroups where [CUBE_NAME] = @CubeName

select [CUBE_NAME], [BASE_CUBE_NAME], [CUBE_CAPTION] from $system.mdschema_cubes where [CUBE_SOURCE] = 1

select [MEASURE_UNIQUE_NAME], [MEASURE_CAPTION], [DATA_TYPE], [MEASUREGROUP_NAME], [MEASURE_DISPLAY_FOLDER] from $system.mdschema_measures where [CUBE_NAME] = @CubeName and [MEASURE_IS_VISIBLE]

select [KPI_NAME], [KPI_CAPTION], [MEASUREGROUP_NAME], [KPI_DISPLAY_FOLDER], [KPI_GOAL], [KPI_STATUS], [KPI_TREND], [KPI_VALUE] from $system.mdschema_kpis where [CUBE_NAME] = @CubeName

select [DIMENSION_UNIQUE_NAME], [DIMENSION_CAPTION] from $system.mdschema_dimensions where [CUBE_NAME] = @CubeName and [DIMENSION_UNIQUE_NAME] <> '[Measures]'

select [DIMENSION_UNIQUE_NAME], [HIERARCHY_UNIQUE_NAME], [HIERARCHY_CAPTION], [HIERARCHY_DISPLAY_FOLDER], [HIERARCHY_ORIGIN], [HIERARCHY_IS_VISIBLE] from $system.mdschema_hierarchies where [CUBE_NAME] = @CubeName and [DIMENSION_UNIQUE_NAME] <> '[Measures]'

select [DIMENSION_UNIQUE_NAME], [HIERARCHY_UNIQUE_NAME], [LEVEL_UNIQUE_NAME], [LEVEL_NUMBER], [LEVEL_CAPTION] from $system.mdschema_levels where [CUBE_NAME] = @CubeName and [LEVEL_NAME] <> '(All)' and [DIMENSION_UNIQUE_NAME] <> '[Measures]'
WITH MEMBER [Measures].[Microsoft.Mashup.Engine.One] AS 1 SELECT     {         [Measures].[Microsoft.Mashup.Engine.One]     }ON 0,     SUBSET(         [D_mydimension].[mycolumn].[mycolumn].ALLMEMBERS,         0,         4096     )     PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON 1 FROM [Model] CELL PROPERTIES VALUE

Each query is followed by two commands containing

”<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"/>”

All of the above takes some 20 seconds, after which nothing seems to happen. Power BI Desktop keeps claiming to load something, and will do so for hours if not interrupted.