Archive for March, 2013

I am able to right click on an empty space in the Control flow then select the “Package Configurations” option in SSIS 2005, 2008 and 2008R2. I am no longer able to do that in SSIS 2012.

In SSIS 2012, go to the Properties window then select the package and then look for the “Configurations” property. Click the ellipsis button next to the Configuration property to access the “Package Configurations”.


SSIS 2008R2

ssis 2008R2 package configurations

SSIS 2012

ssis 2012 package configurations

Read Full Post »

Microsoft has officially announced the availability of “Microsoft SQL Server 2012 With Power View For Multidimensional Models CTP”.

Downloadable from below. You must download all files below:

SQLFULL_x64_ENU_Core.box 2.0 GB  
SQLFULL_x64_ENU_Install.exe 88 KB  
SQLFULL_x64_ENU_Lang.box 702.8 MB


If you have an existing SQL Server 2012 already installed, make sure that you install this one using a named instance such as POWERVIEW_OLAP. You then have to create a new SQL Server Reporting Services Service Application in SharePoint before you can work with PowerView. Once created successfully, create a new report data source as below.

PowerView multidimensional report data source setup


PowerView Multidimensional

If  your existing powerview tabular report stops working, recreate the SQL Service Reporting Services Service Application thenremap the report data source.  It should work as it has with me. I can now use both PowerView tabular and multidimensional 🙂



Read Full Post »

I received this error when creating a report data source for PowerView.

My connection string is below.

Data Source Type is set to Microsoft BI Semantic Model for PowerView

Connection String is set to below:

Data Source=BIDEMOVM\SSASTABULAR; Initial Catalog=HelloWorkPicnic;

a connection redirector error


This error is very misleading. My SQL Browser service is confirmed running. So I double check my set up again and found out that my connection string was incorrect. It should be below. I have missed the number 2 there.

Data Source=BIDEMOVM2\SSASTABULAR; Initial Catalog=HelloWorkPicnic;


Read Full Post »

When I access my Picnic dashboard from my laptop, the  images of fruit, beverages, bread, and vegetable on my slider and tile container were not displayed. This is because the URL of these images were referencing to http://localhost. The following screenshot shows that the URL links have been referenced correctly.

Member value in SSAS Tabular

To point it to a server name, right click on the SSAS Tabular database then select “Script Database as” option followed by the ALTER TO” option to the new query editor window as per screenshot below. This will output the script in XMLA. Do a quick find and replace from http://localhost to http://bidemovm2, in my case. Then process the database.

How to modify ssas tabular


Read Full Post »

It took me 3 days to set this up correctly on my VM. Ensure that your VM has got at least 4 GB of RAM and 150 GB of disk space. PowerView is not a standalone application. You access PowerView from PowerPivot in Excel or the Report Shared Data Source (RSDS) within SharePoint.

PowerView - Picnic Items

My VM specification:

1. OS: Windows 2008R2 server Standard edition 64 bit with SP1 (evaluation copy)

2. SharePoint Server 2010 Enterprise edition 64 bit (with SharePoint Foundation Server 2010, SharePoint Foundation 2010 Language Pack SP1 and SharePoint Server 2010 SP1 ALL installed consecutively). SharePoint 2010 Server SP1 is required to support SQL Server 2012. Install prerequisites from the media, if fails, retry till successful.

Steps to set up PowerView:

1. Install SQL Server 2012 developer or enterprise edition. Install SP1 as well. This will require 8946 MB of disk space. Select all features EXCEPT SSRS – native. This is very important. SSRS must be configured as integrated mode.  Install SSAS multidimensional first. Once installation completes, re-run the media but this time install SSAS as TABULAR.  In this exercise, I have set my SSAS Tabular Instace name as SSASTABULAR. Tabular model represents your Dimensional model in a flattened table format.

ssas 2012 tabular

2. Go to my blog below to set up SSRS 2012 as integrated mode.


3. Grant NT AUTHORITY\Network Service sysadmin access to the database.

4. Configure PerformancePoint 2010 (OPTIONAL –  good to be configured correctly for future need):

– Create Secure Store Service Application Key.

– Create a local account on the server called SPServiceAccount. Grant this account access to the db (no need reader or writer access)

– Set up Unattended Service Account using SPServiceAccount.

5. Test PerformancePoint:

– Grant SPServiceAccount access to OLAP Cube as browser.

– Grant your account access to OLAP Cube as superuser.

– Test PerformancePoint by creating a data source to the OLAP Cube. Make sure that the connection is successful.

6. Install Silverlight 5, 64 bit as PowerView uses Silverlight 5 and above.

7. Activate PowerView on SharePoint.

    To Activate the Power View Feature

  1. Open your browser to the desired SharePoint site.
  2. Click Site Actions.
  3. Click Site Settings.
  4. Click Site Collection Features in the Site Collection Administration Group.
  5. Find Power View Integration Feature in the list.
  6. Click Activate.

8. Install Excel 2010 64 bit

9. Install PowerPivot for Excel 2010 64 bit

10. Install SSRS 2012 Add-In with SP1.

11. Install PowerPivot Add-In for SQL Server 2012

12. Install SQL Server PowerPivot for SharePoint from the media.

In summary, below is a list of the database server instances and SSAS 2012 server instances created on my VM:

my VM list of servers

NOTE!!! I followed the tutorial provided by Microsoft to create my first report in PowerView. I attempted MANY TIMES  to create PowerView report using the PowerPivot Excel spreadsheet as the data source as per tutorial but it has been failing on me with an error of  ” The remote server returned an error: (404) Not Found.”

Connection String is set up to below. Many Internet users reported having failure in configuring this!

Data Source=”http://bidemovm2/Documents/HelloWorldPicnicSQL2012/HelloWorldPicnicPowerViewRTM.xlsx”;

setting up powerview excel as report data source in powerview

So I resolved to restore it to my SSAS Tabular instance. I then used this as my report data source in PowerView.

Tutorial – http://msdn.microsoft.com/en-us/library/hh759325.aspx

Steps to restore SSAS TABULAR from PowerPivot:

1.Download the sample (i.e. PowerViewPowerPivotHelloWorldPicnicSamples.zip)  from the link below. This sample contains 3 files.


HelloWorldPicnic sample list

2. Copy the HelloWorldPicniPowerViewRTM Excel file to where the SSAS Tabular Backup folder location on the server.

3. Open SSMS then connect to SSAS. Set the server name as localhost\SSASTABULAR.

3. Right click on the databases then select the “Restore from PowerPivot” option.

Restore SSAS Tabular from PowerPivot

4.  Browse to the location of the Excel file previously copied in Step 2.

ssas tabular backup location

5. Restore set as below:

Restore from PowerPivot details

6. Refresh SSAS Tabular databases then browse the SSAS Tabular Model. It looks exactly like when you browse to the OLAP Cube.

ssas tabular browse look and feel

7. Done!

To create a report data source for PowerView:

1. Go to your SharePoint site.

2. Go to Library or Shared Documents.

3. Create a new document then select Report Data Source.

how to create a report data source for PowerView

4. In this example, configure as below:

Data Source Type  = Microsoft BI Semantic Model for PowerView

Connection String = Data Source=BIDEMOVM2\SSASTABULAR; Initial Catalog=HelloWorldPicnic;

HelloPicnicTabular RSDS

5. Test the connection. Once successful then click OK to create it.

6. Hover over your mouse to the right on the RSDS you just created then select the “Create PowerView Report” option.

Create PowerView Report from RSDS

7. This will pop up the PowerView window. The PowerView report extension is rdlx.


8. Follow the tutorial below to create Picnic dashboard in PowerView. 🙂

Tutorial – http://msdn.microsoft.com/en-us/library/hh759325.aspx

Have fun!

Read Full Post »

Whilst configuring PowerView, I stumbled across the fact that I was no longer able to switch the mode from native to integrated using the Reporting Services Configuration Tool. Microsoft has decided to change this setting by integrating it to SharePoint and it is not a straight forward process as usual!  o_O

My SQL Server 2012 developer edition (eval copy) was installed with ALL features selected. Same goes with my SharePoint Server 2010. I would presume that the SQL Server Reporting Service should have been installed and configured automatically on SharePoint but that was not the case!

Steps to start the Reporting Services SharePoint Service:

(source: http://msdn.microsoft.com/en-us/library/gg492276(v=sql.110).aspx#bkmk_create_serrviceapplication


  1. Click the Start button
  2. Click the Microsoft SharePoint 2010 Products group.
  3. Right-click SharePoint 2010 Management Shell click Run as administrator.
  4. Run the following PowerShell command to install the SharePoint service. A successful completion of the command displays a new line in the management shell. No message is returned to the management shell when the command completes successfully.
  5. Run the following PowerShell command to install the service proxy:
  6. Run the following PowerShell command to start the service or see the following notes for instructions to start the service from SharePoint Central administration
    get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance

You can also start the service from SharePoint central Administration rather than running the third PowerShell command. The following steps are also useful to verify that the service is running.

  1. In SharePoint Central Administration, click Manage Services on Server in the System Settings group.
  2. Find SQL Server Reporting Services Service and click Start in the Action column.
  3. The status of the Reporting Services service will change from Stopped to Started. If the Reporting Services service is not in the list, use PowerShell to install the service.

Steps to create a Reporting Services Service Application on SharePoint 2010:

  1. In SharePoint Central Administration, in the Application Management group, click Manage Service Applications.
  2. In the SharePoint ribbon, click the New button.
  3. In the New menu, click SQL Server Reporting Services Service Application.
  4. create ssrs service on sharepoint
  5. Configure using the sample below:
  6. create ssrs service app
  7. If you receive an error of “Login failed for user” as below, then make sure that the NT AUTHORITY\Network Service has access to the database.
  8. SP error1
  9. If  you receive an error of “Could not find stored procedure sp_dboption” then make sure that the Microsoft SharePoint Foundation Server 2010 (MUST BE 173,633KB in size) and Microsoft SharePoint Server 2010 SP1 were installed. If not, install them. The Microsoft SharePoint Server 2010 SP1 must be installed in order to support SQL Server 2012! Microsoft SharePoint Foundation Server 2010 must be installed first then followed by the Microsoft SharePoint Server 2010 SP1!!!
  10. SP error2
  11. SSRS Service Application (Integrated mode) installed successfully.

SSRS integrated mode created

Read Full Post »

Found this useful reference from the blog below. The yeartodate formula only works if it exists in the Cube. If it does not then use the following formula instead:
Calendar YTD = Year.FirstMonth:Month

Parallel Calendar YTD = (Year-1).FirstMonth:(Year-1).Month


Content below:

Time Intelligence Formula (TI Formula) allows time periods, sets of time periods and ranges to be specified using a simple, easy to understand syntax.

Formula Structure


Quick Samples

The following are typical scenarios with their corresponding formula and results:

To Get… Formula Result
Yesterday day-1 The previous day relative to the current date.
Tomorrow day+1 The next day relative to the current date.
The current quarter and today quarter, day A set of time periods consisting of the current day and current quarter
Last 10 days day:day-9 A 10 day range including today.
Last 10 days (not including today) day-1:day-10 A 10 day range NOT including today.
Same day last year (year-1).day Parallel period: days by year.
Same quarter last year (year-1).month Parallel period: Quarter by year.
Same range of months last year (year-1).(month-5):
Set of parallel periods last year to the last six months.
Same range of months to date for last year (year-1).firstmonth:
From the first month of last year to the month parallel to the current month this year.
Year to date yeartodate A single time period representing the aggregation of values from the beginning of the year to the last completed period.
The period corresponds to the most granular time period defined for the data source.
Year to date (by month) yeartodate.fullmonth A single time period representing the aggregation of values from the beginning of the year to the last completed month.
Year to date (by day) yeartodate.fullday A single time period representing the aggregation of values from the beginning of the year to the last completed day.
Parallel year to date yearToDate-1 The aggregation of the same set of default time periods completed in the current year except for the prior year.
Parallel year to date (by month) (yearToDate-1).fullMonth The aggregation of the same set of months completed in the current year except for the prior year.

General Conventions

  • TI Formula is not case sensitive
  • White-space is ignored
  • Use parenthesis whenever you use a time period with an offset together with a function.
  • Offsets can be defined on any standard and “to date” time period.
  • The current period is assumed when no offset is specified.

Syntax Elements

TI Formula consists of Operators, Periods and Functions.   The formula is intentionally simple yet robust.


The following operators can be used in TI formula expressions.

Operator Use
“.” The period or “dot” operator delimits time periods from functions.  The part before the dot always corresponds to the time period.  The part after the dot always corresponds to the time period function.
Example:  year.firstMonth
“+” or “-” The plus and minus operators are used to determine the offset relative to the current date. Use the minus sign to specify time periods in the past.  Use the plus sign to specify time periods in the future.
Example:  day1
“()” Parenthesis are used to group a time period and its offset when used together with a function.  This is required.
Example:  (year-1).firstMonth:(year-1).month
“,” Comas are used to delimit multiple time periods in the same formula.
Example:  yearQuarterMonthday
“:” The colon operator specifies a range of time periods.
Example:  day:day-4

Standard Time Periods

Standard time periods supported in PPS include:

Period Example
Year Year-1, year+2
Quarter quarter, Quarter-4
Month Month-2
Week Week-51
Day Day-9, day+2
Hour Hour-12
Minute minute-30
Second second+5

Standard Time Period Functions

Time period functions can be applied to any standard time period using the dot operator.  The functions available in PPS include:

Function Use
FirstQuarter Specifies the first quarter in a year.
LastQuarter Specifies the last quarter in the year
FirstMonth Specifies the first month in a year or quarter.
LastMonth Specifies the last month in a year or quarter.
FirstWeek Specifies the first week in a year, quarter, or month.
LastWeek Specifies the last week in a year, quarter, or month.
FirstDay Specifies the first day in a week, month or higher time periods.
LastDay Specifies the last day in a week, month or higher time periods.
FirstHour Specifies the first hour in a day, week or higher time periods.
LastHour Specifies the last hour in a day, week or higher time periods.
FirstMinute Specifies the first minute in an hour, day or higher time periods.
LastMinute Specifies the last minute in an hour, day or higher time periods.
FirstSecond Specifies the first minute in an minute, hour, or higher time periods.
LastSecond Specifies the last minute in an minute, hour, or higher time periods.


Periods “to-date” are a NEW type of TI formula added in Office 14.  The result of a to-date period is an aggregation of all time periods to date up to the last completed full period.  Incomplete time periods are automatically excluded.  They are evaluated to the lowest degree of granularity in the data source by default.  For example, if most granular time period in the data source were days, then the month to date expression will aggregate all days from the beginning of the month to the last completed full day in the month.  (The opposite is true for standard time periods.  They automatically include incomplete periods.)

PPS supports the following “to date” time periods:

Period Use Works with…
YearToDate Specifies a time period from the beginning of the year to the current period. FullQuarter functions or lower.
QuarterToDate Specifies a time period from the beginning of the quarter to the current period. FullMonth functions or lower.
MonthToDate Specifies a time period from the beginning of the month to the current period. FullWeek functions or lower.
WeekToDate Specifies a time period from the beginning of the week to the current period. FullDay functions or lower.
DayToDate Specifies a time period from the beginning of the day to the current period. FullHour functions or lower.
HourToDate Specifies a time period from the beginning of the hour to the current period. FullMinute functions or lower.
MinuteToDate Specifies a time period from the beginning of the minute to the current period. FullSecond function.

Period To Date Functions

Period to date functions allow you to control the granularity of which time periods to include.  If for example I want to show the year to date including up to the last completed full month, I can add a full period function.

PPS supports the following full period functions:

Function Use Works with…
FullQuarter Specifies that the period to date should include up to the last full quarter. YearToDate
FullMonth Specifies that the period to date should include up to the last full month. QuarterToDate periods of higher.
FullWeek Specifies that the period to date should include up to the last full week. MonthToDate periods of higher.
FullDay Specifies that the period to date should include up to the last full day. WeekToDate periods of higher.
FullHour Specifies that the period to date should include up to the last full hour. DayToDate periods of higher.
FullMinute Specifies that the period to date should include up to the last full minute. HourToDate periods of higher.
FullSecond Specifies that the period to date should include up to the last full second. MinuteToDate periods of higher.
Note this is included for consistency, though technically this period is not needed.

Parallel Period To Date

It is often useful to compare values for the equivalent set of time periods for the current year, but for the previous year.  For example, imagine that the current year to date includes Jan – July of 2010.  You may want to compare this value to the same set of time periods for the previous year.  In this case Jan – July 2009.  This is easy to accomplish using TI formula.  Simply add and offset value to the to-date period.


In fact, offsets can be specified on any to-date time period.   This also works when a full period function is applies as well.  Remember to use parenthesis in this case.


Stephen Handy is a Program Manager on the PerformancePoint Services SharePoint BI team.

Read Full Post »

Older Posts »

%d bloggers like this: