Tabular Data Model Deployment May 17, 2013

http://msdn.microsoft.com/en-us/library/gg492138

After a model has been deployed, there are additional considerations for securing model data access, backups, and processing operations that can be configured on the Analysis Services server by using SQL Server Management Studio. While these properties and configuration settings are outside the scope of this topic, they are, nonetheless, very important in assuring your deployed model data is secure, kept up to date, and provide a valuable data analysis resource for users in your organization.

http://msdn.microsoft.com/en-us/library/gg492136

Connect to a Tabular Model Database (SSAS)

Client libraries that provide access to Analysis Services databases can be used to connect to model databases that run on a tabular mode server. Libraries include the Analysis Services OLE DB provider, ADOMD.NET, and AMO.

Excel uses the OLE DB provider. If you have either MSOLAP.4 from SQL Server 2008 R2 (file name msolap100.dll, version 10.50.1600.1), or MSOLAP.5 (filename msolap110.dll) that is installed with the SQL Server 2012 version of PowerPivot for Excel, you have a version that will connect to tabular databases. Use the instructions provided in the following topic to check the version of the MSOLAP data provider on your computer: Upgrade PowerPivot for Excel and PowerPivot Data.

Choose from the following approaches to connect to model databases from Excel:

• Create a data connection from within Excel, using the instructions provided in the next section.
• Create a BI semantic model connection (.bism) file in SharePoint that provides redirection to a database running on an Analysis Services tabular mode server. A BI semantic model connection file provides a right-click command that launches Excel using the model database that you specified in the connection. It will also launch Power View if Reporting Services is installed. For more information about creating and using BI semantic model connection files, see Create a BI Semantic Model Connection to a Tabular Model Database.
• Create a Reporting Services shared data source that references a tabular database as the data source. You can create the shared data source in SharePoint and use it to launch Power View. For more information, see Create a Shared Data Source for a Data Model (SSRS).

http://msdn.microsoft.com/en-us/library/hh230972

After you set permissions in Analysis Services, you can return to SharePoint and create a BI semantic model connection.

1. In the library that will contain the BI semantic model connection, click Documents on the SharePoint ribbon.
2. Click the down arrow on New Document, and select BI Semantic Model Connection File to open the New BI Semantic Model Connection page.
3. Set both Server and Database properties. If you are unsure of the database name, use SQL Server Management Studio to view a list of the databases that are deployed on the server.Server name is either the network name of the server, the IP address, or the fully qualified domain name (for example, myserver.mydomain.corp.adventure-works.com). If the server is installed as a named instance, enter the server name in this format: computername\instancename.Database must be a tabular database that is currently available on the server. Do not specify another BI semantic model connection file, an Office Data Connection (.odc) file, an Analysis Services OLAP database, or a PowerPivot workbook. To get the database name, you can use Management Studio to connect to the server and view the list of available databases. Use the property page of the database to ensure you have the correct name.
4. Click OK to save the page. At this point, the PowerPivot service application will verify the connection.Verification succeeds if the connection information is correct, and you have granted administrative permissions to the PowerPivot service application so that it can connect to Analysis Services as the current user.Verification fails if the connection information is wrong, or the service application lacks permissions. A validation message will appear on the page asking whether you want to save the file. If you know that the connection is valid, you should save the file anyway, because the error is the result of missing permissions rather than invalid connection information.You can verify the connection by using it in Excel or Power View to connect to tabular model database. If the data source connection succeeds, the connection is valid despite the verification warning.

http://www.connectionstrings.com/olap-analysis-services

Detailed OLAP connection strings.

http://msdn.microsoft.com/en-us/library/bb283152

Detailed instructions to create a Shared Data Source.

When you run a report from a SharePoint library, connection information can be defined inside the report or in an external file that is linked to the report. If the connection information is embedded within the report, it is called a custom data source. If the connection information is defined in an external file, it is called a shared data source. The external file can be a report server data source (.rsds) file or an Office Data Connection (.odc) file.

An .rsds file is similar to an .rds file, but it has a different schema. To create an .rsds file, you can publish an .rds from Report Designer or Model Designer to a SharePoint library (a new .rsds file is created from the original .rds file). Or, you can create a new file in a library on a SharePoint Site.

http://office.microsoft.com/en-in/create-a-connection-to-a-data-model-for-power-view-HA102835737.aspx#_Create_a_shared

Create a shared data source (RSDS file)

You can create shared data sources on a SharePoint site with SQL Server 2012 Reporting Services (SSRS) SharePoint mode.

1. In a SharePoint document library, click the Documents tab > New Document >Report Data Source.

 Note    If Report Data Source isn’t on the New Document menu, the report data source content type isn’t enabled. The SharePoint site administrator needs to Add Report Server Content Types to a Library (Reporting Services in SharePoint Integrated Mode).

2. In Name, enter a descriptive name for the RSDS file.
3. In Data Source Type, select Microsoft BI Semantic Model for Power View.
4. In Connection String, specify a pointer to the data source and any other settings that are necessary for establishing a connection to the external data source. How you connect to a data model depends on how it is published or deployed. Here are examples for:

• An Excel workbook in Shared Documents on a SharePoint site with the SQL Server 2012 SQL Server 2012 Reporting Services (SSRS) add-in—for example:

Data Source=”http://<SharePointSite>/Shared Documents/MyExcelWorkbook.xlsx”

 Note    In SharePoint 2013, if the report and workbook are in the same farm, then you don’t need the PowerPivot for SharePoint add-in. If they’re in different farms, then you still need the add-in in the farm hosting the workbook.

• An Excel workbook in the PowerPivot Gallery on a SharePoint site with SQL Server 2012 PowerPivot for SharePoint and SQL Server 2012 Reporting Services (SSRS) add-ins—for example:

Data Source=”http://<SharePointSite>/PowerPivot Gallery/MyExcelWorkbook.xlsx”

• A tabular model deployed to a SQL Server 2012 Analysis Services (SSAS) instance on a server—for example:

Data Source=ServerName\InstanceName; Initial Catalog=TabularModelName

 Note    For a default instance, you can leave out “\InstanceName”.

5. In Credentials, specify how the report server obtains credentials to access the external data source. For Power View, credentials need to be stored or integrated. Read Credential and Connection Information for Report Data Sources.
6. Click Test Connection to validate the data source configuration.
7. Click OK to create the shared data source.

http://office.microsoft.com/en-in/create-a-connection-to-a-data-model-for-power-view-HA102835737.aspx#_Toc351453506

Create a BISM connection file

You can create Business Intelligence Semantic Model (BISM) connection files on a SharePoint site configured with the SQL Server 2012 PowerPivot for SharePoint add-in. BISM connection files can connect to Excel workbooks or SSAS tabular models.

• Create a BI Semantic Model Connection to a PowerPivot Workbook
• Create a BI Semantic Model Connection to a Tabular Model Database

http://msdn.microsoft.com/en-us/library/hh230901

Use a BI Semantic model to connect.

If you are connecting to a tabular model database that runs on an Analysis Services server, SharePoint permissions are not sufficient. You must also have database read permissions on the server. This step should have been performed when you created the BI semantic model connection. For more information, see Create a BI Semantic Model Connection to a Tabular Model Database.

For ad hoc report design in SharePoint, the user who creates the report must have SharePoint permissions on the BI semantic model connection (.bism) file and on the business intelligence semantic model database. The security context of the connection is the interactive user who is creating the report.

http://msdn.microsoft.com/en-us/library/gg471575

This section explains the connection behavior between various client applications, such as the Excel desktop application or the Power View reporting client on SharePoint, and a tabular model database inside or outside the SharePoint farm.

Connecting from Excel to tabular data on a network

When an Excel user specifies a BI semantic model connection as a data source, the connection information inside the .bism file is downloaded to the client application, which then issues its own direct request to the tabular model database on Analysis Services. To access the .bism connection, the Excel user must be a SharePoint user with read permissions on the .bism connection file. Once the connection information is downloaded, all subsequent connections bypass SharePoint, flowing directly from Excel to the backend tabular model database.

Connecting from Power View to tabular data on a network

When a SharePoint user clicks on a BI semantic connection in a document library, Power View (if it is installed), starts immediately and opens a connection to the tabular model database.

Connections between Power View and a tabular model database follow a double-hop authentication sequence where the user identity is flowed from the client to SharePoint, and then from SharePoint to a back-end Analysis Services tabular model database that runs outside of the farm. The ADOMD.NET client library that handles the connection request always tries Kerberos on the first attempt. If Kerberos is configured, the user identity is impersonated on the connection to the tabular model database, and the connection succeeds.

If Kerberos is not configured and the request fails, Reporting Services makes a second attempt. Under this scenario, the client library connects to Analysis Services using the Reporting Services service identity and NTLM authentication. The identity of the Power View user is passed on the connection string using the effectiveusername parameter.

Only a member of the system administrator role on the Analysis Services instance has permission to make a connection using the effectiveusername parameter and impersonate another user on the server instance. For this reason, the execution account of the Reporting Services shared service must have administrative rights on the Analysis Services instance.  Instructions for granting administrative permissions to the service account is provided in this topic, Create a BI Semantic Model Connection to a Tabular Model Database.

PowerPivot SQL Server 2008 March 18, 2013

http://msdn.microsoft.com/en-us/library/ff628113%28v=SQL.100%29.aspx

Microsoft SQL Server 2008 R2.

Summary: Microsoft® SQL Server® PowerPivot technology redefines how IT departments deliver and succeed with BI. This technology gives users the power to drill into any aspect of their business and compress decision cycles to gain deeper insight, and it enables them to share their findings with others effortlessly and securely. At the same time, the IT department gains effective new management tools to increase operational efficiency in a Microsoft SharePoint® Server 2010 farm environment.Establishing a PowerPivot environment requires deploying the PowerPivot for Excel®2010 add-in on workstations running Microsoft Office 2010 and PowerPivot for SharePoint on SharePoint application servers. Those tasks are independent of each other, but planning and coordination are required to ensure the overall success of the deployment efforts.

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

Secure Store for BIS (SharePoint 2013).

http://msdn.microsoft.com/en-us/library/hh231687(d=printer).aspx

Use the following checklist to install these BI features in the same SharePoint farm: PowerPivot for SharePoint, Report Builder, and Power View. Although this checklist recommends a specific installation order, in practice you can install these features in almost any order. This checklist assumes installation of the following products or features:

SharePoint Server 2010 with Service Pack 1 (SP1)

• SQL Server 2012 Database Engine
• SQL Server 2012 Reporting Services and Reporting Services Add-in
• SQL Server 2012 PowerPivot for SharePoint

http://msdn.microsoft.com/en-us/library/ee210708(d=printer).aspx

Prerequisites

Step 1: Install PowerPivot for SharePoint

Step 2: Configure the Server

Step 3: Install Analysis Services OLE DB providers on Excel Services Application Servers

Step 4: Verify the installation

http://msdn.microsoft.com/en-us/library/2c62daf9-1f2d-4508-a497-af62360ee859

Additional installation steps are required if you run Excel Calculation Services and PowerPivot on separate application servers. On the application servers running Excel Calculation Services, install the appropriate version of the Analysis Services OLE DB (MSOLAP) provider.

There are two scenarios that call for manually installing the OLE DB provider on servers in the farm.

The most common scenario is when you have older and newer versions of PowerPivot workbooks that are saved in document libraries in the farm. If analysts in your organization are using the SQL Server 2008 R2 version of PowerPivot for Excel, and they save those workbooks to a SQL Server 2012 PowerPivot for SharePoint installation, the older workbook will not work. Its connection string will reference an older version of the provider, which won’t be on the server unless you install it. Installing both versions will enable data access for PowerPivot workbooks created in older and newer versions of PowerPivot for Excel. SQL Server 2012 Setup does not install the SQL Server 2008 R2 version of the provider, so you must install it manually if you are using workbooks from a previous version.

The second scenario is when you have a server in a SharePoint farm that runs Excel Services, but not PowerPivot for SharePoint. In this case, the application server that runs Excel Services must be manually updated to use both the newer version of the provider, as well as install an instance of the Microsoft.AnalysisServices.Xmla.dll file in the global assembly. These components are necessary for connecting to a PowerPivot for SharePoint instance. If Excel Services is using an older version of the provider, the connection request will fail.

Use the following instructions to add the OLE DB provider and other client connectivity components to SharePoint servers that do not already have them installed, such as application servers that run Excel Services without PowerPivot for SharePoint on the same hardware.

Use these instructions to install the current Analysis Services OLE DB provider and to add the Microsoft.AnalysisServices.Xmla.dll to the global assembly.

http://msdn.microsoft.com/en-us/library/855bd055-5ad3-493f-9c5b-1f5297b2e6e2(d=printer)

Verify a PowerPivot for SharePoint installation.

To verify PowerPivot data access on the server, do the following:

1. Download the Picnic data sample that accompanies a Reporting Services tutorial. You will use the sample workbook in this download to verify PowerPivot data access. Extract the files.
2. Upload the Excel workbook (.xlsx) to Shared Documents. The workbook contains embedded PowerPivot data.
3. Click on the document to open it from the library.

Installing SharePoint 2013 Apps March 4, 2013

http://www.dotnetmafia.com/blogs/dotnettipoftheday/archive/2013/02/28/installing-sharepoint-2013-apps-with-powershell.aspx

There are a lot of similarities to working with solution packages but there are several differences to be aware of. The documentation on TechNet is pretty good, but putting it all together can be tricky.

Specific to PowerShell, there is some terminology to familiarize yourself with. You’ll see these for parameter names so you need to know what they are otherwise you’ll find yourself confused.
•App Package – physical file containing the app (.app file)
•App – an instance of an app installed on a particular subsite

Along with the App, you’ll find an Id property that refers to a GUID of that particular app instance. We’ll talk about that more when it comes to updates.

SharePoint 2013 App Manifest February 22, 2013

http://msdn.microsoft.com/en-us/library/fp179918.aspx

A app for SharePoint package is a file that has an “.app” extension and that complies with the Open Packaging Conventions (OPC). The package contains the following items:

Various items and the following.

SharePoint Solution Packages: Optionally, the app may include a SharePoint solution package (.wsp file) that contains the components of the app web. These components may include pages, list instances, views, documents, Web-scoped Features, and other SharePoint 2013 components. (For more information about what SharePoint components can be included in an app for SharePoint, see Types of SharePoint components that can be in an app for SharePoint.) The .wsp file may also contain apps for Office. The components in the .wsp file are deployed to the app web. For an example of an app package that includes a SharePoint solution package, see How to: Create a cloud-hosted app that includes a custom SharePoint list and content type.

SSIS Package Deployment February 1, 2012

http://msdn.microsoft.com/en-us/library/ms137592.aspx

Package deployment for SQL Server 2008.

http://msdn.microsoft.com/en-us/library/ms137916.aspx

Configure extra folders in MSDB for package deployment.

http://www.bidn.com/blogs/DevinKnight/ssis/73/adding-a-folder-to-the-ssis-package-store

Add a custom file system folder.

http://msdn.microsoft.com/en-us/library/ms365329.aspx

Tutorial deploying packages. Building the deployment bundle.

In this lesson, you will copy the deployment bundle to the destination computer and then run the Package Installation Wizard to install the packages, package dependencies, and ancillary files on that computer. The packages will be installed in the msdb SQL Server database and the other items will be installed in the file system. After you complete the package installation, you will test the deployment by running the packages from SQL Server Management Studio using the Execute Package Utility.

In this task, you will run the Package Installation Wizard to deploy the packages from the Deployment Tutorial project to an instance of SQL Server. Only packages can be installed in the sysssispackages table in the msdb SQL Server database, the supporting files that the deployment bundle includes will be deployed to the file system.

Integration Services provides several tools that you can use to run packages in the test and production environment: the command prompt utility dtexec and the Execute Package Utility. The Execute Package Utility is a graphical tool that is built on dtexec. Both of these tools execute the package immediately. In addition, SQL Server provides a subsystem of SQL Server Agent that is especially designed for scheduling package execution as a step in a SQL Server Agent job.

http://msdn.microsoft.com/en-us/library/ms365338.aspx

Head of the package deployment tutorial.

http://arcanecode.com/2009/10/06/using-ssis-package-configuration-values-as-parameters-to-sql-tasks/

Run through of using a variable for a parameter to SQL statement,

http://arcanecode.com/2009/10/06/using-ssis-package-configuration-values-as-parameters-to-sql-tasks/

Run through of passing SSIS variables to the SQL task.

http://vyaskn.tripod.com/sql_server_2005_making_ssis_packages_portable.htm

Making the package portable.

Always reuse existing XML configuration files, if one exists for your server and database combination. This is the sole reason behind configuration files, as you will have to update connectivity information in only one place. If there isn’t a configuration file already created for your server and database combination, create a new configuration file using the below templates and follow the described naming convention.
For Windows authenticated connection to SQL Server:

 <DTSConfiguration> <Configuration ConfiguredType="Property" Path="\Package.Connections[SERVERNAMEdbname].Properties[ConnectionString]" ValueType="String"> 
<ConfiguredValue>Data Source=[SRVNAME];Initial Catalog=[DBNAME];Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue> </Configuration> </DTSConfiguration> 

For SQL Server authenticated connection to SQL Server:

 <DTSConfiguration> <Configuration ConfiguredType="Property" Path="\Package.Connections[SERVERNAMEdbname].Properties[ConnectionString]" ValueType="String"> 
<ConfiguredValue> Data Source=[SRVNAME];Initial Catalog=[DBNAME];User ID=[LOGIN];password=[PWD];Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False </ConfiguredValue> </Configuration> </DTSConfiguration> 

Note: In the above templates, square brackets [] are not required except around the name of the connection. Do not surround server name, database name, user name and password with square brackets

 

Deploying Package January 30, 2012

http://msdn.microsoft.com/en-US/library/ms365338(v=SQL.90).aspx

SSIS 2008 Deploying Package tutorial.

 

Publish and Deploy a BDC Model January 11, 2012

http://troyscott.ca/2010/08/07/bdc-models-in-visual-studio-2010-for-sharepoint-foundation-server-2010/

 

Click Once and BCS December 28, 2011

http://msdn.microsoft.com/en-us/library/ff798273.aspx

The mechanism for taking external lists offline makes use of an ActiveX control to check the prerequisites mentioned earlier. Because only Internet Explorer supports ActiveX controls, taking external lists offline is supported only in Internet Explorer. In other browsers, such as Firefox, the Connect to Outlook and Sync to SharePoint Workspace buttons are, disabled as shown in Figure 7.

XSNFeatureReceiver November 1, 2011

http://henry-chong.com/2010/12/provisioning-files-to-a-features-root-folder-in-sharepoint-2010/

I spent quite a while trying to configure a Module element to provision a file directly to the feature root folder, but Visual Studio 2010 doesn’t allow you to set a blank foldername the module, which means that using a Module, you’ll be stuck provisioning your files to a subfolder on your feature.

It turns out the solution to this (and the right way to include files with your features that you don’t necessarily want to push to Sharepoint/content database) is using the Empty Element item.

Create an empty element and a feature as normal, and add whatever files you want to include to your EmptyElement item:

Then, for each of the files, open the properties window and make sure the Deployment Type is set to ElementFile.

Finally, we’ll set the Path for the deployment location to nothing – unlike a module, an elementfile supports a blank path (which will then deploy the file to the feature root folder, where we want it)

http://www.andrewconnell.com/blog/archive/2007/09/27/6120.aspx

ElementFile attribute for Feature.xml deployment of files.

 

May 20, 2011

http://ranaictiu-technicalblog.blogspot.com/2010/05/sharepoint-2010-deployment-powershell.html

function WaitForJobToFinish([string]$SolutionFileName)
{
    $JobName = “*solution-deployment*$SolutionFileName*”
    $job = Get-SPTimerJob | ?{ $_.Name -like $JobName }
    if ($job -eq $null)
    {
        Write-Host ‘Timer job not found’
    }
    else
    {
        $JobFullName = $job.Name
        Write-Host -NoNewLine “Waiting to finish job $JobFullName”
       
        while ((Get-SPTimerJob $JobFullName) -ne $null)
        {
            Write-Host -NoNewLine .
            Start-Sleep -Seconds 2
        }
        Write-Host  “Finished waiting for job..”
    }
}