SSAS and percentage measures

Scenario

Let's say you have the following base measures that are the raw data held in the source system:

• ListPriceDollars
• StandardDiscountPercentage

The cube users require these two measures and an additional StandardDiscountPriceDollars measure to be available for use.

In this situation, it's easy to get stuck at the point of ensuring StandardDiscountPercentage displays sensible numbers at all aggregation levels. There are plenty of options to play with - aggregation functions, measure expressions, calculated members, etc. For people with more of a SQL than MDX background there's a lot to understand here. Throw in some null values and a parent-child relationship and you'll be torturing yourself trying to find the best way to represent this in SSAS.

Solution Option

One possibility is to completely avoid trying to aggregate percentages.

1. Change your source view or named query to calculate StandardDiscountPriceDollars (=Product.ListPriceDollars / (1 - (Product.StandardDiscountPercentage/100))
2. Don't use the raw StandardDiscountPercentage value in the cube - instead, create a simple replacement calculated member (= 1 - ([Measures].[Standard Discount Price Dollars] / [Measures].[List Price Dollars])

With a little bit of zero and null handling the job will be done.

Getting MDX from Excel pivot tables

1. Data: Connections
2. Properties
3. Select Definition Tab
4. Add Log File=c:\temp\pivotlog.txt;
5. OK
6. Close
7. Refresh Pivot

Note: the definition text provided above is case sensitive! Particularly Log File

Displaying data source information in Reporting Services reports using expressions

The following expression is about the best I have come up with:

=DataSources("myDataSource").DataSourceReference & " " & DataSources("myDataSource").Type

This will display:

• Data source name - probably also "myDataSource" in this example. The full path is provided when the report is run in Report Manager
• Data source type e.g. "OLEDB-MD" is an Analysis Services data source

Limitations:

• You can't use expressions referring to data sources in the header or footer. But who wants the data source details displayed in the report body??
• The data source reference has to be hard coded in every report. I'm not aware of any way to enumerate this to just fetch the first datasource. It would be fantastic if the following worked, but it doesn't:

=DataSources[0].DataSourceReference & " " & DataSources[0].Type

Pausing SQL Server transactional replication without losing queue contents

To Pause:

1. Open SSMS and connect to the distributor
2. Disable and Stop the "Distribution clean up: distribution" job
3. Expand SQL Server Agent, right-click on Jobs, select View Job Categories and choose REPL-Distribution
4. Disable and Stop all listed jobs

To Continue:

• Enable and Start all the jobs that were stopped

I have seen countless forum discussions for this topic but no succinct list of steps.

SSIS OLE DB Command error with NOT NULL constrained columns

Symptom:

OLE DB Command fails with the following error reported to the package log:

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. The RPC name is invalid.".

Possible Cause:

The data flow task is experiencing a constraint violation.

For a table ThisTable defined as follows:

ThisTableKey int not null
ThisTableValue varchar(10)

and OLE DB Command data flow task containing the following statement:

update ThisTable set ThisTableValue = 'UpdateVal' where ThisTableKey = ?

where ? has been mapped to a data flow column.

If the data flow column contains a NULL value then the task will fail because of the NOT NULL constraint on the column.

Diagnosis:

This is hard to diagnose in SSMS because the SQL statement above will run fine (and do nothing) when ? is substituted with NULL.

Instead add data viewers to the outputs of the offending OLE DB Command. You should see the following columns at debug time:

• ErrorCode: -1071607702
• ErrorColumn: 12345 (this is an example value)
• ErrorDescription: The data value violated the schema constraint.

Open the package in a text editor and search for 12345. You will find a LineageId recorded against it. Search for the LineageId and you will find the column name (should be ThisTableKey).

Resolution:

Use a Conditional Split task to filter out all NULL values in the data flow column before the OLE DB Command.

SSAS Aggregation Design error

Symptom:

The following error appears when deploying the cube or designing aggregations

The reference to attribute is not valid

Cause:

The partitions file has gotten out of sync with the cube file. Perhaps the cube file got checked back in to source control but the partitions file changes were undone.

Solution Options:

You may be able to hack the partitions file to clean up invalid attribute references.

Then again it may be easier just to roll back and reapply your changes.

SSAS processing error could be due to proactive caching settings

Symptom:

"Errors in the high-level relational engine. The data source view does not contain a definition for the [object] table or view. The Source property may not have been set."

But the data source view doesn't contain this table.

Possible Cause:

A table has been specified for tracking in the notifications for proactive caching, but isn't available any more for some reason.

Solution Options:

• Update the table name and location as required
• Disable proactive caching for the partition

See http://www.mssqltips.com/tip.asp?tip=1563 for more details.