Hello,
I'm working on a project that I am encountering some errors and was wondering if there is anything I can do to improve my report via changing the model, etc.
Here are the error messages:
Error fetching data for this visual
OLE DB or ODBC error: Either a connection cannot be made to the pbipwus24-westus2.pbidedicated.windows.net server, or Analysis Services is not running on the computer specified. Either a connection cannot be made to the pbipwus24-westus2.pbidedicated.windows.net server, or Analysis Services is not running on the computer specified..
Error fetching data for this visual
OLE DB or ODBC error: The following system error occurred: The handle is invalid. , The following system error occurred: .
Let me give you some background on my model and report.
My model consists of a import connection to a SQL database, two separate excel files stored in SharePoint and a live connection to a semantic Power BI data model.
SQL Database (Import mode)
My SQL tables consist of:
- 8 lookup tables that provide descriptions to keys in my Customer (dim) table.
- 3 lookup tables connected through a bridge table to my Customer table.
- The bridge table is to avoid many-to-many relationships.
- 1 lookup table and 1 fact table containing customer activity data that joins to my customer table.
- Another lookup table and another fact table containing customer balance data, joining to customers.
- 1 calendar (dim) table that connects to the 2 fact tables.
- 1 sites (dim) table that connects to the 2 fact tables.
SharePoint
The SharePoint Data consists of:
- 1 excel file (Fact table) that contains external customer data. It is connect to:
- 1 excel file (translation table) to match external customer IDs with internal customer IDs.
- 1 excel file which contains about 30 worksheets which have a lookup table in each worksheet.
- Each worksheet has a small table with three columns contains Score, Lower Bound and Upper bound limits.
- Each table has different scores and different limits.
Example of Score tables.
| Score | Lower Limit | Upper Limit |
| 1 | 0 | 5 |
| 2 | 5 | 10 |
| 3 | 10 | 15 |
Power BI Semantic Model (Live connection)
My Power BI semantic model consists of:
- 1 Orders Header (fact) table, connected to the Site, Calendar and Customer tables.
- 1 Orders (fact) detail table connected to Orders Header table.
- 1 Transaction history (fact) table connected to the Site, Calendar and Customer tables.
- 1 AR Snapshot (fact) table connected to the customer table.
I have probably given you more than you needed, but I wanted to explain the complexity of the data model.
Visualization
I have a table visual. In the rows I have the customers. In the valuesI have 32 metrics. (The 30 metrics with Scores) then a measure to aggregate all the points, and a measure to convert the points into a customer rating group.
The visual has conditional formatting to color code the score from bad to good for each measure.
Performance
I will admit this report takes a decent amount of time to render. During development I would often get the second error on a consistent basis. As I added more and more measures to the table it became more frequent. To resolve the error I would go to Optimize > Refresh Visuals or Home > Refresh. Unfortunately its not working anymore since I had to create some more complex measures. Once I modified some of the measures I keep getting the first error, and refreshing the data and increasing the Query-limit capacity to premium didn't work either.
Measures
I have 30 calculations that reference various metrics related to the customers and then reference the lookup tables to determine a score. Some metrics are worth 0 to 1 points, some 1 to 10, etc.
The measures started off as simple calculations like CALCULATE( SUM( Sales, Sales Type = 1 ) ) but as I progressed tthrough development the request for monthly averages came up, so certain measures changed to AVERAGEX( VALUES( Date[MonthYear] ), SUM( Sales, Sales Type = 1 ) )
Then is of those measures would applied to get the score using this pattern:
Avg Sales Score =
VAR Metric = [Avg Sales per Month]
VAR Score = CALCULATE(
MAX( 'Sales_Score'[Sales_Score]),
FILTER(
'Sales_Score',
Metric >= 'Sales_Score'[Sales_Lower_Limit] &&
Metric < 'Sale_Score'[Sales_Upper_Limit]
)
)
RETURN
IF( NOT ISBLANK( Metric ), Score )
The reason I applied the ISBLANK () was to be able to omit points if something wasn't applicable. For example a customer might have 40 out of 59 applicable points, whereas another customer might have 72 out of 80 applicable points. If the everything was applicable it might be 87 out of 100 points.
Improvements
I'm sure there are many ways to make improvements to the report, but eliminating the error, and improving rendering time are the most important. Things I have considered, but not sure if it would work.
- Using "Enter Data" and replacing all the score tables in SharePoint and embedding them into Power BI.
- The reason I used SharePoint was I wanted users to be able to update the scores and thresholds if they wanted to make changes.
- Could SharePoint and Power Automate be combined to update the score tables embedded in the pbix file?
- Breaking the visual down into multiple tables
- In terms of aesthetics, I wanted to keep everything in one big table.
Any thoughts or suggestions? Really appreciate the feedback.
