Wednesday, August 29, 2012

How to remove ALL as a dimension member

In BIDS you have to navigate to the highest level in the hierarchy in your dimension and for the corresponding attribute you change the IsAggregatable property from True to False.

Friday, August 17, 2012

Report based on 2 cubes with 1 overlapping parameter

The solution is to used linked dimensions:

Define Linked Dimensions

t been rated 
Rate this topic
A linked dimension is one that exists in one Analysis Services database, but reused in another Analysis Services database of the same version and compatibility level. Linked dimensions are read-only. If you want to modify the dimension or create new relationships, you must use the source dimension.
All related measure groups and dimensions must come from the same source database. You cannot create new relationships between local measure groups and the linked dimensions you add to your cube. After linked dimensions and measure groups have been added to the current cube, the relationships between them must be maintained in their source database.

The source database that provides the dimension and the current database that uses it must be at the same version and compatibility level. For more information, see Set the Compatibility Level of a Multidimensional Database (Analysis Services).
The source database must be deployed and online. Servers that publish or consume linked objects must be configured to allow the operation (see below).
The dimension you want to use cannot itself be a linked dimension.

  1. In SQL Server Management Studio, connect to an Analysis Services server. In Object Explorer, right-click the server name and select Facets.
  2. Set LinkedObjectsLinksFromOtherInstancesEnabled to True to allow the server to issue requests for linked objects that reside in databases running on other instances.
  3. Set LinkedObjectsLinksToOtherInstances to True to allow the server to request data for linked on databases running on other instances.

  1. Start the wizard. In SQL Server Data Tools (SSDT), right-click the Dimensions folder in an Analysis Services database or project, and then click New Linked Dimension.
  2. Connect to the Analysis Services database that provides the dimension. On the Select a Data Source page of the Linked Object Wizard, choose the Analysis Services data source or create a new one.
  3. On the Select Objects page of the wizard, choose the dimensions you want to link to in the remote database.
  4. On the Completing the Wizard page, you can preview the linked objects. If you link a dimension that has the same name as one that already exists, an ordinal number (starting with '1' for the first duplicated name) is appended to the name. When you complete the wizard, the dimension is added to theDimensions folder.

Use the New Data Source wizard to add to your project connection information about the Analysis Services database that provides the dimension. You can start the wizard by clicking New Data Source in the Select a Data Source page of the Linked Objects wizard.
  1. In the Data Source Wizard, on the Select how to define the connection page, click New.
  2. In Connection Manager, verify that the provider is set to Native OLE DB\Microsoft OLE DB Provider for Analysis Services 11.0.
  3. Enter the name of the server (use servername\instancename for a named instance)1 or type localhost to connect to an Analysis Services server that is running on the same computer.
  4. Use Windows authentication for the connection.
  5. In Initial catalog, click the down arrow to select a database on this server.
  6. On the Data Source Wizard, click Next to continue.
  7. On the Impersonation Information page, click Use the service account. Click Next, and then finish the wizard. The connection you just defined will be selected in the Linked Objects Wizard.

You cannot change the structure of a linked dimension, so you cannot view it with the Dimension Structure tab of Dimension Designer. After processing the linked dimension, you can view it with the Browser tab. You can also change its name and create a translation for the name.

Friday, August 10, 2012

Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope.

Use the following list to help determine the cause of the error:
When a report has more than one dataset, an aggregate expression in a text box on the report body must specify a scope parameter. For example, =First(Fields!FieldName.Value, "DataSet1").
To specify a scope parameter, provide the name of a dataset, data region, or group that is in scope for the report item. For more information, see Understanding Expression Scope for Totals, Aggregates, and Built-in Collections (Report Builder 3.0 and SSRS) and Expression Reference (Report Builder 3.0 and SSRS).
Names of objects must be greater than 0 and less than or equal to 256 characters.

The length of object identifiers in a report definition is restricted to 256 characters. Identifiers must be case-sensitive and CLS-compliant. Names must begin with a letter, consist of letters, numbers, or an underscore (_), and have no spaces. For example, text box names or data region names must comply with these guidelines.
To change the name of an object, in the toolbar of the Properties pane, select the item in the drop-down list, scroll to Name and enter a valid object name.

Friday, August 3, 2012

Text qualifier results in text file containing "_x003C_none_x003E"

There is a bug in SSIS when exporting data from SQL to a comma delimited text file where you specified no text qualifier.
This bug apparently only occurs when you develop the SSIS on a x64 win7 PC and copy the .dtsx file (windows explorer copy/paste) to network path of a x86 SQL server and schedule the job to run from SQL Agent on the same x86 SQL server.
When the SSIS runs, the text file is written out containing text qualifier = "“_x003C_none_x003E".
If you look at " _x003C_none_x003E", it actually means <none>.  x003C = "<" and x003E = ">".
If you go into the SSIS package, double-click in the connection manager section to open the flat file connection manager editor and try to clear the text qualifier removing the <none>, the <none> value get added back in.
The only work-around is to NOT open the flat file connection manager editor, but instead make the change using the property window and clear out any value in the TextQualifier field.
Other similar problems occur when you actually want to put a real value in the text qualifier.  For explain if you select the double-quote as the text qualifier and copy to a x86 server and run, you end up with a text file containing the value " _x0022" around each field instead of a double quote.

Wednesday, August 1, 2012

How to remove the ALL member from your parameter

Since you’re new to creating reports from a cube you still probably rely on using the query designer because maybe you don’t know MDX yet and that’s fine.  So pretend you had a request to create a report with a drop down parameter that allows multiple selections and you used the query designer to create the MDX for you.

This is something similar to how your MDX will look like
MEMBER [Measures].[ParameterCaption] AS [Product].[Category].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Product].[Category].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS [Product].[Category].CURRENTMEMBER.LEVEL.ORDINAL

[Measures].[ParameterLevel]} ON COLUMNS,
[Product].[Category].ALLMEMBERS ON ROWS
FROM [Adventure Works]
This is what your parameter will look like
Now let’s say the end-user doesn’t like seeing the ‘Select All’ and ‘All’ in the drop down, as shown above. Below I will show you how to fix this issue quickly by modifying the MDX and using the Children Function.
This is the new MDX
MEMBER [Measures].[ParameterCaption] AS [Product].[Category].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Product].[Category].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS [Product].[Category].CURRENTMEMBER.LEVEL.ORDINAL

[Measures].[ParameterLevel]} ON COLUMNS,
[Product].[Category].Children ON ROWS
FROM [Adventure Works]
This is how the parameter looks like now with the modified MDX

SQL Server Reporting Services - Set default value for multi-value report parameter

In reports when we want to default the multivalue parameter to 'Select All' following are the steps:
  1. Open the Report parameter window from the Report menu.
  2. Select the Report parameter from the left handside of the window.
  3. Select 'Multi-value' checkbox and appropriate 'Available values'.
  4. Under default values select 'From Query' radio button'.
  5. Select the appropriate 'Dataset'.
  6. Select appropriate 'Value Field'.
  7. Save the Report and select Preview Tab. You will find all the items selected in the multivalue parameter list and the result displayed for all the selected items.

SSIS Error - The value violated the integrity constraints for the column

The error, as you can see below, doesn't say much about what is wrong, it is pretty generic and vague.

[OLE DB Destination [469]] Error: There was an error with input column "ColumnName" (578) on input "OLE DB Destination Input" (482). The column status returned was: "The value violated the integrity constraints for the column.".

The reason(most likely) the SSIS package failed is because(atleast) one of the columns in your data source has NULL values and the corresponding column(s) in the destination is set to not allow NULL values. You can fix this by either modifying the destination table to allow NULL values or by setting up a default value for the source column that has NULL values.