Unpredictable File Crashing, Refresh Failure, and Memory Issues - msmdsrv.exe

[alexrmorris88]

Good morning,

I developed a fairly simple star schema consisting of 10 Dimension tables and 1 fact table, all direct import. 

The dataset is quite small, only 7,122 KB. 

All tables are being queried directly from our Snowflake CDW from Power Query, as seen here:

 

- The dimensions Querys are all: 

"SELECT * FROM [TableName]"

- The fact Table is a bit more complex:

SELECT

  OI."Key_OrderItem",
  OI."Key_Warehouse",
  OI."Key_Site",
  OI."Key_ShipAddress",
  OI."Key_ItemID",
  OI."Key_Company",
  OI."Key_OrderStatus",
  OI."Key_OrderType",
  OI."Key_Salesperson",
  OI."Key_SalesTaker",
  OI."Date_ActualShip",
  OI."OrderID",
  CASE 
    WHEN ( OT."OrderTypeName" = 'Return' OR OT."OrderTypeName" = 'Returned order') AND ( OI."AllocatedWeight(Kg)" > 0 )
    THEN -1 * OI."AllocatedWeight(Kg)"
    ELSE OI."AllocatedWeight(Kg)"
  END AS "AllocatedWeight(Kg)",
  CASE 
    WHEN ( OT."OrderTypeName" = 'Return' OR OT."OrderTypeName" = 'Returned order') AND ( OAI."InvoicedAmount" > 0 )
    THEN -1 * OAI."InvoicedAmount"
    ELSE OAI."InvoicedAmount"
  END AS "InvoicedAmount",
  OAI."DestCurrencyID",
  OAI."SourceCurrencyID",
  OAI."ExchangeRate"

FROM "FactOrderItem" OI

INNER JOIN "FactOrderItemAmount" OAI
ON OI."Key_OrderItem" = OAI."Key_OrderItem"

INNER JOIN "DimCompany" C
ON OI."Key_Company" = C."Key_Company"

INNER JOIN "DimItem" I
ON OI."Key_ItemID" = I."Key_ItemID"

INNER JOIN "DimOrderType" OT
ON OI."Key_OrderType" = OT."Key_OrderType"

-- Conditions
WHERE C."CompanyID" IN ('CPQLN')

-- Adding only CAD and USD 
AND OAI."DestCurrencyID" IN ('CAD', 'USD')

-- Removing Price Adjustments
AND OT."OrderTypeName" <> 'Price Adjustment'

-- Removing unwanted Items(Products)
AND I."Category" <> 'Offal' 
AND I."SubCategory" <> 'Offal'
AND I."ItemType" IN ('Finished Goods Co product', 'Finished Goods Organic premium', 'Finished Goods Prem', 'Finished Goods Select', 'Raw Material Prem', 'Raw Material Select', 'Sales Inventory', 'Work in Progress Prem', 'Work in Progress Select')

This dataset was working well in production for about a month, until a week ago. We received a "Refresh Failure" notification from our Power BI workspace. The notification stated:  "Failure details: There's not enough memory to complete this operation. Please try again later when there may be more memory available. "

Now, everytime I try to refresh or rebuild the dataset from stratch, the dataset crashes. The error seems to change randomly. Sometimes its a "Memory Issue" other times its "Too Many Queries". 

When I rebuild the model from a new file, the point of failure is different everytime. Sometimes its fails on refresh and sometimes it fails on building relationships. 

Here's a screenshot of the current failure (when I tried to join dimCustomer to factOrders): 

During most of these issues the "Microsoft SQL Server Analysis Service" (via msmdsrv.exe) seems to be leaking memory, as seen below: 

I have tried to look up my issue but I cannot seem to find any solutions that help my issue. 

I have tried:

- uninstalling and reinstalling Power BI

- rebuilding the dataset from a clean Power BI file (6 different times)

- I have simplified all the queries to "SELECT * FROM [TableName]" 

- Updating my Power BI to the latest version

and nothing seems to be working. 

Would anyone know how to help?

I am very conused as to why this issue happened seemingly out of no where. 

[lbendlin · Accepted solution]

By specifying your own queries you are preventing query folding from taking effect.  That's not necessarily a bad thing, just want to make sure you are fully aware.  Might be worth trying with raw table connections and query folding enabled.

[alexrmorris88]

Thank you, I looked in to the Query folding and it appears the Case statements were causing the issue.