Change default values BCS service application for External Lists

When you want to use/show external lists in your SharePoint 2010 environment that have more then 2000 rows, you get the following error:

The above error is a pretty general error that doesn’t tell you the real problem. When you get this error through a mobile phone UI, you get a more detailed error:



The dialog above tells us that the external list has got a predefined maximum of 2000 rows. The amount of maximum can be changed by using PowerShell

Steps:

·         Open SharePoint Management Shell

·         Search for the ID of the BCS service application via the cmdlet: Get-SPServiceApplicationProxy

·         When you found your ID, enter the following command:

Get-SPBusinessDataCatalogThrottleConfig -Scope Database -ThrottleType Items -ServiceApplicationProxy  <fill in GUID here>, when you hit <Enter> you get the following information:

·         To change the default values, enter the following commands:

$Db = Get-SPBusinessDataCatalogThrottleConfig -Scope Database -ThrottleType Items -ServiceApplicationProxy 6b43909f-a52b-4d6e-8c06-631deccf8dcb

Set-SPBusinessDataCatalogThrottleConfig -Identity $Db -maximum 25000 -default 19000

·         After we ran the commands we see that the values are changed:

After that go back to the SharePoint environment and refresh you page, you now will see your rows from the external datasource in this external list:

That's Why: PowerPivot

With PowerPivot (codename: Gemini) for Excel 2010 users can work with loads of company data as never before. With PowerPivot, as an end user, you can create your “own cube” within Excel and simply slice and dice through these tons of information.

In this exercise, you will learn how to import and prepare data from a SQL Server 2008 R2 database into the Microsoft Project “PowerPivot” Add-in for Microsoft Office Excel 2010 (PowerPivot).

Requisites:

• Microsoft SQL Server 2008 R2
• PowerPivot module installed
• Excel 2010
• ContosoRetailDW (2008 R2 database, you can download this one from codeplex or Microsoft site.)

Exercise 1: Getting started

By the end of this exercise, you will understand:

·         How to launch PowerPivot

·         How to connect a PowerPivot to an external data source to the PowerPivot

·         How to control what data is imported into PowerPivot

·         How to create a calculated column using a Data Analysis Expression formula

·         Click Start, All Programs, Microsoft Office, Microsoft Excel 2010 (Beta) ·         Click the PowerPivot tab. ·         Click the PowerPivot window button to start the PowerPivot add-in. The add-in loads a new top-level window entitled PowerPivot Client Window.

·         In the PowerPivot Client Window, click the From Database button, and select From SQL Server from the list of data sources. ·         In the Server name field, enter localhost. ·         In the Database name combo-box, select ContosoRetailDW from the list. The wizard automatically creates a Friendly connection name. ·         Click the Next button to continue the running the wizard. ·         On the Choose How to Import the Data page, choose the Select from a list of tables and views to choose the data to import option and click Next. ·         From the list of available tables, scroll down and place a check next to the FactSales table. ·         Click the Select Related Tables button to have the wizard select those tables related to FactSales. The wizard displays a message that “6 related tables were selected”. ·         With the FactSales table still selected, click the Preview and Filter button. The wizard opens a new window where you can filter the data that it imports. ·         Remove the check for the SalesKey column.

·         Scroll to the right until you can see the CurrencyKey column. Remove  the check from the CurrencyKey column.

 

·         Click OK to apply the filter and return to the wizard. Notice that the Filter Details column now shows Applied filters.

·         Click on the table name FactSales in the Friendly Name column and change the name to Sales.

·         Click Finish to start the import and prepare process.

Note: As the import process progresses, the dialog provides you with information about the number of rows transferred.

Note: Notice how PowerPivot has transferred almost four million rows from the Sales table. With more memory, you could work with 100 million row datasets.

·         Once the import process is complete, click the Details button in the Message column to review any status messages. Generally you only need to do this if any errors occurred.

  

·         Click OK, then Click Close to close the wizard and return the PowerPivot Client Window.

·         At the bottom of the window, you will see a tab for each imported table. Right click on the DimChannel tab and choose Rename from the context menu.

·         Change the name to Channel. You can rename the tables to make it easier for you to work with them.

·         Click on the Sales tab.

·         Scroll all the way to the right until you can see the Add Column column.

·         Select Add Column.

·         In the formula bar enter =[SalesAmount] – [TotalCost] and press Enter. PowerPivot creates the new column and updates the screen with your calculation.

 

·         With the new CalculatedColumn1 column selected, right click and select Rename Column from the context menu.

·         Type Profit to change the column name and press Enter.

·         Click the PowerPivot button in the upper left corner of the application window and select Save.

·         Change the path to the desktop and name the file ContosoSales.xlsx and click Save.

·         Once PowerPivot has saved the file, continue to the next exercise.

Exercise 2: Work with data

In this exercise, you create a sales report in Excel 2010 using PowerPivot as your in-memory data source containing almost four million rows.

By the end of this exercise, you will understand:

·         How to create a PivotTable and PivotChart report connected to PowerPivot

·         Use data slicers to filter data

Note: Exercise 2 assumes you have completed Exercise 1 and that Excel 2010 and the PowerPivot Client Window are still open.
 

·         If necessary, click the ContosoSales.xlsx button on the Task Bar to activate the PowerPivot Client Window.

·         Select the PivotTable button and from the drop-down menu, choose the Chart and Table (Vertical) menu option. PowerPivot will return you to Excel window.

·         In the Insert PivotTable dialog, choose the Existing Worksheet option and click OK.

·         In the PowerPivot Task Pane, scroll to the bottom of the list of tables until you find the Sales table. Then find the Profit field and place a check next to it.

·         Next, find the ClassName field in the DimProduct table. Drag and drop it into the Axis Fields box.

·         Now find the ChannelName field in the Channel table. Drag and drop it into the Row Labels box.

·         Place a check next to the TotalCost field in the Sales table. You should note that Excel adds it under the existing Profit field in the Values box and to the right of the Sum of Profit column in the PivotTable.

·         Click the drop-down menu on the Sum of Profit field in the Values box and select Move Down.

·         Click on the chart, and then place a check next to the SalesAmount field in the Sales table.

·         Click the drop-down menu on the Sum of SalesAmount field in the Values box and select Move to Beginning.

·         On the PivotTable, select the cell with the text Row Labels (it should be cell A1). Change the value to Class / Channel.

·         Change the Sum of SalesAmount column header to Sales Amount.

·         Change the Sum of TotalCost column header to Total Cost.

·         Change the Sum of Profit column header to Profit. The headers should now all be changed.

·         Highlight all of the cells in the PivotTable that contain numeric figures.

·         Click the Home tab on the Ribbon followed by the $ symbol to format the selected values.

·         Next, click the Format button and from the drop-down menu, choose the AutoFit Column Width command.

·         Select the Sheet1.

·         On the PivotChart, select the Y Axis label, then right click and select Format Axis.

·         In the Format Axis dialog, change the Display units to Thousands.

·         Choose the Number option from the list of pages.

·         Choose Currency from the Category list.

·         Change the Decimal places to 0 and click Close.

·         Choose the Number option from the list of pages.

·         Choose Currency from the Category list.

·         Change the Decimal places to 0 and click Close.

·         If you would like to resize the chart. Click the right side of the charge on the grab handle. Drag to left until it is the width that you desire.

·         In the Gemini Task Pane, locate the GeographyKey field under the DimStore table. Drag and drop it to the Slicers Horizontal box.

·         Find the PromotionName field in the DimPromotion table. Drag and drop it to the Slicers Vertical box.

·         Finally, locate the Manufacturer field in the DimProduct table. Drag and drop it to the Slicers Vertical box.

·         Right click on the Horizontal Slicer: GeographyKey, and select Slicer Settings.

·         Change the Caption from GeographyKey to Country and click OK.

·         Repeat the process and change the title of the first Vertical Slicer from PromotionName to Promotion.

·         In the Country slicer, select only one of the fields such as 731. Notice how the PivotChart and PivotTable update in real time. Play around with other slicers to get a feel for how the real time analysis works.

·         Close Excel by clicking the File and select Exit.

·         If prompted to save your changes, do so. Excel will close the PowerPivot Client Window. 

Deploy InfoPath 2010 Forms with PowerShell

How to deploy Administrator Approved InfoPath 2010 forms with PowerShell....well it's pretty easy and it saves you a lot of work.

So: instead of publishing the form to the server (administrator approved publishing), send a question to your administrator to verify it and then wait till he/she uploaded it and deployed the solution and activated the feature on your site collection you can use the following commands:

Uninstall the existing solution (based on the from file name):
Uninstall-SPInfoPathFormTemplate -Identity Exampleform.xsn

Install the new solution (based on the from file name):
Install-SPInfoPathFormTemplate -Path C:\Form.xsn

Disable feature on site collection level
Disable-SPInfoPathFormTemplate -Identity "Form.xsn" -Site http://SPSite 

Enable feature on site collection level
Enable-SPInfoPathFormTemplate -Identity "Form.xsn" -Site "http://SPSite"  

Important note:
Make sure that you publish the xsn as follows:

• Open the backstage in infopath after creating the form and click Server SharePoint:

• Choose your site collection and click next

•  Then Check that you must use the form throughout the browser and choose the last option (by administrator approved form) and click next

• Choose a location (on your local machine or network drive) where to publish the xsn (not to SharePoint location!! because you cannot enter a sharepoint path in the cmdlet in PowerShell).

• If you want to add columns to the sharepoint forms list or parameters, you can add them here from your InfoPath field list. Click next.

•  Click publish and your xsn is published to your location. Now you can use the above PowerShell scripts to deploy the solution and activate the feature.

Werner and I did it again!!

Secure Store Service Application in SharePoint 2010 (and BCS example)

Intro

The Secure Store Service replaces the Microsoft Office SharePoint Server 2007 Single Sign On feature. Secure Store Service is a shared service that provides storage and mapping of credentials such as account names and passwords. It enables you to securely store data that provides credentials required for connecting to external systems and associating those credentials to a specific identity or group of identities. It is very common for solutions to try to authenticate to an external system in which the current user is known differently or has a different account for authentication. In such cases, Secure Store Service can be used to store and map user credentials required by the external system. You can configure Secure Store Service so that multiple users can access an external system by using a single set of credentials on that external system.

For example, if a user named Fred has one account on the server that is running SharePoint Server and another in a CRM application, the Secure Store mechanism enables his CRM credentials to be stored with his user profile in SharePoint Server. As a result, if he uses a Microsoft Business Connectivity Services (BCS) solution in SharePoint Server to obtain data from the CRM application, SharePoint Server looks up the Secure Store Service database on the server and provides his credentials to CRM. In in this manner, Fred automatically logs on to the CRM application without having to log onto the CRM application separately.

To provide similar functionality on Microsoft Office clients, Business Connectivity Services provides a Secure Store provider that uses the Windows Credential Store.

In addition, SharePoint Server enhances the Secure Store Service functionality to include a pluggable secure store mechanism that enables you to use alternate secure store providers.

Installation/configuration

First make sure that the BCS service (for the example in this blog) is started and a BCS Service app is created. Also make sure that the Secure Store Service is started (Central admin --> services on server).

1:
Go to the central administration and click under application management on "Manage service applications".

2:
The list with available service applications becomes visible. Click in the upper left corner on: New/Secure Store Service Application.

After clicking on the link a dialog (SPDialog) appears:

3:

I'll advise you that (for security reasons) you choose for a separate/isolated application pool for the secure store service app (sss) .

4:

Choose a database server and name for the sss and click on OK, the sss and the sss proxy will be created. After clicking on OK you get the message to generate a key first before you can use the service application. This key will be used to encrypt credentials.

5:

Create the key by clicking in the ribbon on <Generate new key>

6:

The database will also be encrypted with a passphrase key, enter the key and confirm it:

7:

Initially there are no target application in the secure store. To create one click on <New>. An new window will be loaded:

I've chosen a ID called: AdventureWorksID, this is the name of my external system that I want to connect to in my BCS solution.

There are two primary types for creating a target application:

• Group, for mapping all the members of one or more groups to a single set of credentials on the external data source.
   
• Individual, for mapping each individual to a unique set of credentials on the external data source.

I've chosen "Group" in this example. Click <Next> and the following screen will appear:

In the screen above you can add fields that are necessary for the credential part. In the <Field Name> textboxes choose you're own label that you want to choose when a sss administrator needs to enter the credentials. If you're credentials are not windows credentials, change this in your dropdown box. You can check the checkbox "Masked" if you want your text to be masked.

8:

Click on <Next> and fill in a administrator for the target application and a member if necessary and click <OK>. You're target application is now created.

9:

Click on your new target application and after that click on <Set credentials>

10:

Enter your credentials in the next screen and click <OK> (notice that the labels of the field names are changed)

You also have the possibility to publish a sss when you're working with multiple farms. To do this click on the secure store service application in <manage service application screen> and click in the ribbon on <Publish>, the following screen will open:

11:

Select the check box <Publish> and copy the urn. For an explanation how to use a service application cross farm see my blog: Metadata cross farm BLOG.

Shortened Real-time Example

1:
Create an external content type with SharePoint Designer. Give the external content type a name and click on the link (see last link in picture below) to discover external datasources:

In the popup box fill in your external database server name and database name. After that, choose: "Secure store application Id" and fill in the ID of the target application you just created:

Click <OK>, choose your data and publish/save your external content type to the BCS metadata store.

From the SharePoint 2010 UI create a list based on the external content type you just created and your list is now available in SharePoint based on the Secure Store Credentials.

PS: @SPJanet: Hope this is a worthfull blog for you, otherwise please let me know!