Excel 2016 PivotTable timeout error on SSAS data source after 1 minute

Excel 2016 PivotTable report may generate an error message after running the report for 1 minute. The connection timeout was exceeded. This problem only happens if you have the build number 11.0.9167.412 of the SSAS OLE DB Provider shipped in Excel 2016 update. You can check the following files and see if the version is…


SSAS tabular model database fails to restore due to out of memory error

We have 2 customers getting an out of memory error when they backup or restore a large tabular model database. ¬ ¬† In the case of restoring a database backup, it is a 300GB .abf file restore on an SSAS server¬†with 1TB of memory. When all the files are extracted to the data folder, the…


Excel 2016 PowerPivot MDX query designer error

Problem: When you open Excel 2016, go to the Power Pivot tab, click the Mange button to bring up Power Pivot window In Power Pivot, click Get External Data, choose From Other Sources Choose Microsoft Analysis Services. Click Next Fill in the SSAS server name, then click Next, ¬† Click on the Design button, you…


How to import data from a Teradata database into Excel 2013 using LDAP authentication

Problem: Teradata can accepts authentication of different types. Teradata can authentication user credentials from local accounts, LDAP, or Active Directory. Local account is probably the easiest to create, but hard to manage. If there are hundreds or thousands of users who need access to Teradata database, the local account method is very difficult to manage…


Power BI Personal Gateway supplemental information

Note: Power BI software is updated frequently. This is written based on the Power BI Personal Gateway released in August 2015. Power BI Personal Gateway allows refresh of data models stored in Power BI that connects to an on premise data source. Power BI support has 2 documents on how to install and troubleshoot this…


SSAS MOLAP dimension size limit

After the introduction of the StringStoresCompatibilityLevel property in SSAS dimension after SQL Server 2012, SSAS database designer may try to create a MOLAP dimension with more unique values than what is allowed in previous SQL Server versions. The article Configure String Storage for Dimensions and Partitions has a very good documentation of this property. The…


SSIS execution time out error when using SSISDB stored procedure [catalog].[create_execution]

After SQL Server 2012 is released, SSISDB provides stored procedures to create SSIS package executions. There is one problem though. If too many package executions are created a the same time, you may get the following error “The operation failed because the execution timed out” The cause is in the stored procedure [catalog].[create_execution]. It throws…


Error in MDX query result cell is not shown in SSRS query designer

This is a very little known problem regarding how SSRS query designer having trouble showing cells with MDX error. I created an MDX query to create a result cell that shows an error with member measures.ErrorCell as [somethingbad] select measures.ErrorCell on 0 from [Adventure Works] ¬† If you run the following MDX query in SSMS…


Performance problems on high cardinality column in tabular model

Marco Russo has a great article regarding optimization on high cardinality column in tabular model at Optimizing High Cardinality Columns in VertiPaq The basic idea is to avoid having high cardinality columns in an SSAS tabular model database. The reasons are 1. It takes a long time to process a high cardinality column. If you…


Power BI PowerPivot report error after 30-second timeout

Power BI allows you to upload an Excel file that contains a report that gets data from the PowerPivot data model. When you refresh the report on the Power BI report gallery, you may get an error after 30 seconds An error occurred while working on the Data Model in the workbook. Please try again….