SSAS Calculated Member's Currency Format Displayed in Excel 2007

Problem

After creating a calculated member in your SSAS cube, you connect to the cube via Excel 2007 and drag the calculation into your Pivot table. However, although the Format is set to Currency in the cube , the calculation appears as an unformatted number in the Pivot table.

Solution

When creating the calculation in the Form View, there is an Format String drop down box that allows for selecting “Currency” as the format. However, the LANGUAGE property of the calculation needs to be set for this to carry over into the Excel pivot table example described above.

To set the LANGUAGE property of the calculation, click on the Script View to show all calculations in the cube. In the Script View, the MDX can be edited for any and all calculations and additional properties can be added. These additional properties are not available via the Form View but the properties are valid MDX properties of a calculated member. One such property is the LANGUAGE property.

The LANGUAGE property accepts the LANGUAGE CODE ID as the value to be set. For example, the value for US Currency is 1033.

The following is an example of a simple calculated member with the LANGUAGE property being set:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Avg Sales Amt]
AS IIF(ISEMPTY([Measures].[SALES Count]), NULL, IIF([Measures].[SALES Count] = 0, 0,
[Measures].[AMOUNT] / [Measures].[SALES Count])),
FORMAT_STRING = "Currency", LANGUAGE=1033,
NON_EMPTY_BEHAVIOR = { [AMOUNT] },
VISIBLE = 1 ;

For more information about the LANGUAGE property, navigate to the following KB article: http://support.microsoft.com/kb/950598

Maintenance Plan SSIS Package fails on the exported server

by Sarma Musty, Senior Consultant

Problem Statement

Here we tried to solve the problem as mentioned the following link.

http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/880fc71e-f214-4f87-85b3-592244764b49/

The developer created a Maintenance Plan using SQL Server 2005 Maintenance Plan Wizard. The tasks included a backup database task. The Maintenance Plan package (SSIS) was exported to a different Server. While trying to edit the Backup Database task, the system threw the following error.

Figure 1 - Error while editing the Back DB Task

Background

In SSIS, Backup Database Task is used to backing databases. The following diagram shows the screen for editing Backup Database Task. User can list the databases that need to be backed up and also provide information about locations, devices etc. The backup can be done on a Tape or Disk.

User can select “Backup databases across one or more files” and manually provide the list of backup files (Manual Option). Or he/she can select “Create a backup file for every database” and give a path for the backups (Auto Option). In this option system will generate backup files automatically. This property is saved in “DestinationCreationType” attribute of Backup Database Task properties.

Figure 2 - Edit Screen on Back DB Task

Figure 3 - Backup DB Task Properties

In the problem, the setting that the developer opted was manual and a backup file location was also provided. The system was not able to recognize this list. Possible reasons could be that system was not able to recognize the location or device that’s defined in the task. Nevertheless, the error message does not give any details about the actual reason.

Solution

As a work around, we changed the DestinationCreationType property from Manual to Auto. After this change the edit screen of the Back Datbase Task is working as expected.

New Technet Post on Installing PerformancePoint Server SP2 with SQL Server 2008

I was updating a virtual server instance to use SQL Server 2008 with PerformancePoint, specifically with the new SP2. Come to find out unstalling the PerformancePoint 2007 SP2 components with SQL Server 2008 and getting a working configuration is not the most straightforward process. It involves the download and installation of a handful of hot fixes and utilities to get past - among other things -communication issues betwen the Planning components and SQL Server 2008. There's a recent TechNet article that provides the details that helped me get this working:

http://technet.microsoft.com/en-us/library/dd335965.aspx

Resolving update conflicts with Change Data Capture

Another use of the new SQL Server 2008 feature Change Data Capture (CDC) is to resolve conflicts between multiple updates.

Consider a large online store where multiple business users update product information. Suppose product specialists use a big form to enter the data. Suppose also that one user can only update unit name or size. Another user can change product category. Let's see how we can use CDC to accurately track the changes.

Assume the following table holds the products:

In the Management Studio I opened table properties, selected Change Tracking, and enabled both "Change Tracking" and "Track Columns Updated". Note that a primary key is required for change tracking. This
command can accomplish the same from a script:
ALTER TABLE Products ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)

When a user opens a Web form and intends to change the UnitName, application records the current version of the row that contains product data. We can use one of the new SQL 2008 functions to determine the current version of the database row:
DECLARE @prevVersion integer
SELECT @prevVersion = SYS_CHANGE_VERSION
FROM CHANGETABLE(VERSION Products, (ProductID), (1)) CT
The query selects the current version of a product with ID of 1 and saves it in variable @prevVersion.

The user makes desired changes and wants to save the data. To update the row in the database the application runs the following query:
UPDATE Products
SET Name='CDC Power', UnitName='book and CD', Category='Books'
FROM CHANGETABLE(VERSION Products, (ProductID), (1)) CT
JOIN Products P ON P.ProductID = CT.ProductID
WHERE CT.SYS_CHANGE_VERSION = @prevVersion

The WHERE clause condition ensures that the data in the database has not changed since the application last retrieved it.

Suppose that at the same time the first user is making the change, another user is changing category for the same product using the same query. The update executed by the first user increments the current version of the row, so the above query updates no rows for the second user. If the application did not check the version, the second update statement could override UnitName with obsolete data.

Now consider changing the application, so that the query for the second user updates the Category column only. It can execute the following query:
UPDATE Products
SET Category='Programming Guides'
FROM CHANGETABLE(CHANGES Products, 1) CT
JOIN Products P ON P.ProductID = CT.ProductID
WHERE P.ProductID = 1
AND CT.SYS_CHANGE_VERSION = @prevVersion
AND CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('Products'), 'Category', 'ColumnId'), CT.SYS_CHANGE_COLUMNS) = 0

The CHANGE_TRACKING_IS_COLUMN_IN_MASK function tells us whether the Category column has been updated since the last check. Since that is the only column the query updates, we can ignore changes to other columns. Now the query would succeed even if both users initially looked at the same version of the row. By checking which columns have actually changed, the conflicting updates will more likely succeed.