Pivot Viewer with SSAS

Pivot Viewer is a new tool in MS BI. It was proposed to use with large amount of data. Microsoft samples state that it can be used with SSRS to show to details from SSAS cube.


Silverlight based tool will work with SharePoint 2010 and render the details in high graphical.


Download : http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=d31f609d-a353-41ad-a1a4-f81456e3a6c4
Demo : http://www.microsoft.com/showcase/en/us/details/cc397c17-fe21-4dc0-a408-249dbb3299ae

Measure value shows in negative in SSAS

In SSAS, If you have measure type as int, in some cases you can see cube aggregated results in negative. 


Source:
This is because of the limitation of data type.Integer will support -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647). If the aggregated data was falls out of this range, then it shows wrong data or data in negative values.


Solution:
To resolve this issue, select the SSAS Cube measure in the BIDS and change the measure DataType property and Source DataType property to BigInt. Reprocess the cube and check the result.

SSIS Package Re deployment

Deploying around 100 packages in SQL server mode will take long time. If we have any changes in few packages, forces complete deployment by the deployment manifest file. This can be avoided by editing the manifest file.

Link is having sample deployment manifest file to deploy needed packages alone. This will also update the configuration details used in separate file.We need to add the package name need to be deployed like
ETL_SALES.dtsx
ETL_SALES_NEW.dtsx


Sample File

SSRS Report trigger after data load

As a SSRS developer, you may need to trigger some reports after the data load was success. By default, microsoft report automation, will allow you to set the time of report execution. It wont care about your data load and success/failure of the data load.

Triggering report before the completion of data load or data load was failed, result the incorrect report.
To handle this scenario, follow the steps:

1. Schedule the report to execute once in the history date.So it wont run by the schedule in the future.
2. After the data load operation, invoke the below SQL statement with the report name. It will use the details that you specified in the subscription and send the report by mail or by any mean.

insert into THUBISPSDB.ReportServer.dbo.[Event]

    ([EventID], [EventType], [EventData], [TimeEntered], [ProcessStart], [BatchID])
select NewID(),EventType,[EventData], GETUTCDATE(), NULL, NULL
from THUBISPSDB.ReportServer.dbo.Schedule a
inner join THUBISPSDB.ReportServer.dbo.ReportSchedule b on a.ScheduleID=b.ScheduleID
inner join THUBISPSDB.ReportServer.dbo.Catalog c on b.ReportID=c.ItemID
where c.Name='SUMMARY.rdl'

SSAS dimension process

In SSAS(SQL Server Analysis Service), big dimensions like dimensions related to daily sales or ticket booking will have millions of rows. Thousands of row can be inserted in the dimension on daily process. Processing these type of dimensions on daily basis will take long time.

Microsoft, some place refer to use ProcessAdd to process the dimension by XMLA code. But it was not supported by tool process, it was not recommended by microsoft.

ProcessAdd - This wont handle changes in the existing dimension data like updation, deletion. It can handle new inserted data only.

XMLA code segment to process dimension by ProcessAdd is not clear. We can do ProcessAdd by two types:
1) Push method
2) Pull Method

Push Method:
New data needs to be added will be hard coded and make the XMLA to process the dimension. Some samples are available online
Negative - XMLA handling is tough,
Positive - Dimension process will be faster

Pull Method:
We need the pull the data added newly. This is simple and XMLA handling is very easy when compare to push method. Samples available in net wont work normally.
Negative - Process performance is slow when compare to push method
Positive - XMLA is very easy to handle


Sample:
Create the dimension with source or view and process the dimension and cube. Right click on cube, generate XMLA alter script. We need to use the dimension attribute XMLA script to process the dimension by ProcessAdd.

Sampe XMLA code  to process the dimension by ProcessAdd Pull method.