SSAS Lost security context

SSAS Lost security context

I recently logged into an SSAS server and was unable to add a user to the server security. Worse yet, the Security Properties tab was not showing domain user names, just SID's. This means the service has lost connectivity to resolve SID's with the domain. The solution today was to run SSMS as Administrator, domain admin that is. Once connected the domain administrator account is able to remove the disconnected SID's and add back the proper accounts. On other occasions I have seen the security tab empty, meaning there is no recognized administrators. To resolve that issue, stop the service…
Read More

SSAS Tabular Error “The object reference not set to an instance of an object”

This just goes under the blog tag "Useless Error Messages". My instance of this error happened when I added a new calculated column. Finally I realized I had named the new column the same as another column in a different table. Changed one of the names and no error. Lesson learned: SSAS Tabular is picky about not having two columns named the same. So boys and girls, only you can prevent stupid coding mistakes...
Read More
SSAS Cube Partition Slice ErrorCode=”3240034361″

SSAS Cube Partition Slice ErrorCode=”3240034361″

Slices are a great way to improve query performance in a heavily partitioned SSAS Cube.  The photo above shows where to add or change a Slice properties in partition properties dialog box. If you ever get the following errors when processing cubes, it has to do with the Slicer property at the partition level.  The first error message tells you it is a slicer, the next few messages will indicate which partition puked. This post is sponsored by our partners Wigs <Error ErrorCode="3240034361" Description="Errors in the OLAP storage engine: The restrictions imposed on partition slice where violated." Source="Microsoft SQL Server…
Read More
Sharing SQL 2012 data in SharePoint 2013

Sharing SQL 2012 data in SharePoint 2013

I enjoyed presenting at SQL Saturday #223 in Oklahoma City last week.  If you attended, you will find the presentation and Data Solution files below.  The session recording is available at UserGroupTV, thanks to Sean. Session Description: Microsoft SharePoint is a collaboration software that provides a web based portal for business intelligence, content management and document sharing. In this session get an overview of the basics of sharing a SQL table as a 'List' using Sharepoint and SSIS. Next we move on to fun stuff of integrating SQL Server Reporting Services integration. Finally we look at how to give users…
Read More
How to fix “Usage Based Optimizer” connection errors in SSAS 2012

How to fix “Usage Based Optimizer” connection errors in SSAS 2012

The Usage-Based Optimizer can be a very powerful tool for calculating aggregations, when it works. When it doesn't work perfectly, then it can be frustrating to figure out. The devil is in the details. If you move data between environment, then you have even more details to find and fix. Just for a quick overview, in Visual Studio for 2012 (BIDS for previous versions) your project properties deployment will point at an SSAS Server Instance which in turn has to have query logging configured to point to a SQL Server database.  That database table, then logs info about the queries…
Read More
SQL Rally 2012 BI Track Highlights

SQL Rally 2012 BI Track Highlights

When I got back from SQL Rally 2012 a few weeks ago in Dallas, I shoved the notes into my laptop bag and took off for vacation.   I still want to share a few of the highlights of my trip.  These were some excellent speakers and I hope to see them again at future events. Sessions: Tuning Analysis Services Performance by John Welch Presentation John did an excellent job of explaining the basics of the processing options in SSAS. What was interesting was his experience splitting up jobs to increase performance.  Process Full will process all the dimensions and facts…
Read More

Using SQL to Build MDX parameters

I had a client report which needed a parameter list to default to the previous closed financial period date. The SSAS cube includes reporting periods into the future which have data entries even if the month has not been 'closed' by accounting department. Without a 'Month Closed' flag in the cube, the only way to build the parameter list was by querying the date dimension database table. When adding a date parameter to the report dataset, SSRS will automatically build a hidden dataset which pulls the list of available dates from the cube. For this example, I left that data…
Read More