is it a direct query or import

[stanteitelbaum]

hi i was surprised when my co worker told me she was getting a memory exception when trying to import our sales cube into pbi on her desktop.   and that as much as she didnt want to, was using direct query instead of import for time being so she could get going.   so i asked her to send the pbix, i opened it up and refreshed at will.   

but i cant tell if what im experiencing on my desktop with her file is direct query or import.  and if it is import why is she getting the error and im not?  i clicked everything i can think of and really dont know which mode im getting.   how can i tell which mode im getting? 

I show her error below.  and i can tel you in ssas (im sure compressed), the db is only 3.6 gig.   we use tabular.   the central sales fact table has 66,751,416 records.   the star dimensions surrounding it are the usual customer, bill to, ship to, product, customer product, and a few low volume items like cost center and facility.   the most voluminous dimension table product has 10.6 million records.   everything else is a distant second third etc.

[v-kpoloju-msft]

Hi @stanteitelbaum, 

Thank you for reaching out to the Microsoft fabric community forum. Also, thanks @speedramps, @MasonMA, for those valuable insights for this thread. I have identified several alternative workarounds that may assist in resolving the thread.

To answer your first question about how to check whether the PBIX file is using Import or Direct Query mode just open the file and go to the Model view (the icon with the table on the left). Then, click on any table and look at the Properties pane on the right. Under "Storage mode," you will see either:

• Import – which means the data is loaded directly into Power BI, or
• Direct Query – meaning it's connected live to your SSAS cube.

Another quick way to check is by going to Transform Data → Data source settings. If it says, “Connect live” or “Direct Query,” then it’s not using Import mode.

Now, regarding the memory exception your coworker experienced while trying to import the model there could be a few reasons behind it:

• Limited RAM: If she is  working on a device with 8GB or less RAM, that could easily trigger memory issues, especially with large datasets like a 66M-row fact table.
• 32-bit Power BI Desktop: It is  important to ensure she’s using the 64-bit version the 32-bit one often runs into memory limits with large models.
• Too much data at once: She could try filtering rows or excluding unnecessary columns/tables during import to reduce the load.
• Auto Date/Time setting: Disabling this option (under File → Options → Data Load) can help reduce memory usage, as it prevents Power BI from generating hidden date tables automatically.

Kindly refer to the below mentioned document links for more information:
Use storage mode in Power BI Desktop - Power BI | Microsoft Learn

Connect to Analysis Services data in Power BI Desktop - Power BI | Microsoft Learn

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[stanteitelbaum]

no problem v-kopoloju.

i followed your instructions for seeing table properties etc.   as the image below shows there is no table icon just a model icon, viz and dax icon.  when i click the model icon, then a table i dont see what you said to look for over to the right under properties.

the second image shows what i see when i click transform and then data source settings.   r u sure that this indicates import mode or is there a chance that isnt true?

i'll read your links and pursue with her but the fact that at least one of your tests for "live" suggests im in import mode, and the fact that i refresh at will without error makes me think only her install or pc are in question.  i'll post back here.  does the fact that i can create a measure but not add a column suggest anything more to you?

[v-kpoloju-msft · Accepted solution]

Hi @stanteitelbaum,

Thanks for the follow up. From the screenshots you have shared, it is now clear that your Power BI file is using a Live connection to a SQL Server Analysis Services (SSAS) model.

• In Live mode, you can create measures but not calculated columns that’s expected behaviour.
• The “Storage mode” does not appear in the Properties pane because it's only available for Import or Direct Query models.
• When you go to Data Source Settings and see the SSAS server prompt, that confirms it’s not an Import mode.

So yes, your PBIX file is connected live, and not importing data, which is why your refreshes work fine and you are not running into memory issues.

If your coworker is trying to import the full model into Power BI, that could cause memory exceptions, especially with large datasets (e.g., 66M rows). Instead, she could try:

• Using a Live connection (same as yours),
• Ensuring she's on the 64-bit Power BI version,
• Or reducing data size during import via filters.

Please refer to the below mentioned link:
Connect to Analysis Services data in Power BI Desktop - Power BI | Microsoft Learn

Hope this helps. If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[speedramps]

You will get this message in Power Bi desktop

Consider applying a filter to your query for just 1 record.

If that works then increase it to 10 records.

Then check the visual.  Is the use trying to display all records instaed of just the total.
That may exceed the capacity.

Change the visual to sum the total. Then change the filter to all records.

Does it now work ok?

[stanteitelbaum]

hi again speedramps.   she is already using a filter (basic filtering) on both pages for only one fiscal year from a list of fiscal years where she has that box checked.   i dont know any other way to filter a semantic model whose power query isnt visible.   i dont know if the handshake between pbi and ssas in import mode is smart enough to make that one selection more efficient than it would be otherwise but that really reduces the population to 4.5 million facts.    i dont know how a semantic model can be reusable if ssas is smart enough to make this distinction so who knows, maybe she's importing everything anyway.  i dont know if she put that filter on all pages whether pbi would act differently.   my refreshes are fine so for the moment i'll direct her to    vkopoloju's ideas and see what happens.   she already has 32 gig ram.

[stanteitelbaum]

thx speedramps, i was hoping to start with the trick for determining direct or import.   if im in import mode, then clearly something is different on her pc because i refresh without error.  kind of like a baby step before we mitigate volume issues, if that makes sense.

[MasonMA]

Hi @stanteitelbaum 

It's usually by looking at Model view/Power Query editor to tell whether it's an import mode or DirectQuery, isn't it? 

From your description, it might sound like your colleagues' RAM is not enough..., but i might be wrong. 

If the fact table has 66Million rows, it might be okay on some computers but not for all users. I'd consider using DirectQuery or Composite model. 

Thanks
Mason

[stanteitelbaum]

thx Mason, i show below what i see in the model view.   normally i'd click transform to get to power query etc but when i click transform in this case, all is greyed except "data source settings".    that means "transform data" is greyed.    when i hit "data source settings" i see what/only i already know which is the name of the ssas db. 

also, she'd like to avoid direct query if possible because of all the restrictions associated with DQ.   With DQ she says she cant add measures and columns.   which contradicts what im seeing because i seem to be able to add measures, but not columns.   i, not sure why you suggested composites but i fmemory serves that a modelk that can act as either DQ or import.