26 Aug 2015
By Allen
0 Comments
SSAS Lost security context

SSAS Lost security context

SSAS,
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
26 Jun 2015
By Allen
0 Comments

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

SSAS, ,
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
15 Jan 2014
By Allen
0 Comments
SSAS Cube Partition Slice ErrorCode=”3240034361″

SSAS Cube Partition Slice ErrorCode=”3240034361″

SSAS, ,
  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. <Error ErrorCode="3240034361" Description="Errors in the OLAP storage engine: The restrictions imposed on partition slice where violated." Source="Microsoft SQL Server 2012 Analysis Services" HelpFile="" />  <Error ErrorCode="3240034318"…
Read More
10 May 2013
By Allen
0 Comments
Business Intelligence Forecasting in SQL and Cognos

Business Intelligence Forecasting in SQL and Cognos

Cognos, SSAS, , , , ,
Note: This article is an update of a presentation given when I was at the University of Oklahoma's Health Sciences Center. At that time, I was using Cognos version 7.3. This article is updated for Cognos 10 and SQL Server Analysis Server 2012.  Revisiting this subject ten years later, my opinion is that Microsoft has finally surpassed IBM Cognos. Most businesses, including medical clinics, want some method for forecasting business trends. The Cognos BI powerplay presentation tools contain the tools needed for basic forecasting.  The trick in getting accurate forecasting is select the correct algorithm. I recommend trying the model…
Read More
30 Apr 2013
By Allen
0 Comments
How to fix “Usage Based Optimizer” connection errors in SSAS 2012

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

SSAS, , ,
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
05 Jun 2012
By Allen
0 Comments
SQL Rally 2012 BI Track Highlights

SQL Rally 2012 BI Track Highlights

SQL Server, SSAS, SSIS, SSRS, , , , , , , ,
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
01 Jul 2011
By Allen
0 Comments

Using SQL to Build MDX parameters

SSAS, SSRS, , , , , ,
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