PowerPivot Training

Roadmap to Creating PowerPivot Workbooks in Excel: http://technet.microsoft.com/en-us/library/ee210641.aspx
PowerPivot for Excel Tutorial: http://technet.microsoft.com/en-us/library/ee835510.aspx
PowerPivot for Excel Samples: http://go.microsoft.com/fwlink/?LinkID=192753&clcid=0×409
PowerPivot for Excel DAX Samples: http://go.microsoft.com/fwlink/?LinkID=192754&clcid=0×409

Year over Year Growth Percentage change calc

((
([Measures].[YourMeasure],[DimTime].[Hierarchy].CurrentMember)
–
([Measures].[YourMeasure],[DimTime].[Hierarchy].CurrentMember.PrevMember)
)
/
([Measures].[YourMeasure],[DimTime].[Hierarchy].CurrentMember.PrevMember)
)



Microsoft Moves into Leaders Category on Forrester’s 2010 Enterprise BI Platforms Wave

Read all about it here
http://www.microsoft.com/presspass/presskits/bi/docs/EnterpriseBIPlatformsq42010.pdf

add random numbers into a database

This code assumes you have an ID (identity) column.

 

DECLARE @counter smallint;
DECLARE @random_number float;
SET @counter = 1;
WHILE @counter < 2000
  
   BEGIN
  SET @random_number = RAND()
  SET @random_number = @random_number * 100 
     UPDATE [db_name].[dbo].[db_table]
   SET  [db_column_rand] = @random_number
  WHERE [ID] = @counter
       SET @counter = @counter + 1
   END;
GO

 

 

Creating a SharePoint 2010 Web Part That Can Read and Write Data to an External Data Source

Learn how to use Microsoft Visual Studio 2010 to create a Visual Web Part that uses a Microsoft Business Connectivity Services (BCS) external content type in Microsoft SharePoint Server 2010 to read and write data to the AdventureWorks sample database.

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

Configuring SharePoint 2010 Kerberos with BI Scenarios

Newly published whitepaper.This document gives you information that will help you understand the concepts of identity in Microsoft SharePoint 2010 Products, how Kerberos authentication plays a very important role in authentication and delegation scenarios, and the situations where Kerberos authentication should be used or may be required in solution designs.The document also shows how to configure Kerberos authentication end-to-end within your environment, including scenarios that use various service applications in Microsoft SharePoint Server. Additional tools and resources are described to help you test and validate Kerberos configuration. The “Step-by-Step Configuration” sections of this document cover the following scenarios for SharePoint Server 2010.

• Scenario 1: Core Configuration
• Scenario 2: Kerberos Authentication for SQL OLTP
• Scenario 3: Identity Delegation for SQL Analysis Services
• Scenario 4: Identity Delegation for SQL Reporting Services
• Scenario 5: Identity Delegation for Excel Services
• Scenario 6: Identity Delegation for Power Pivot for SharePoint
• Scenario 7: Identity Delegation for Visio Services
• Scenario 8: Identity Delegation for Performance Point Services
• Scenario 9: Identity Delegation for Business Connectivity Services

The download URL is:http://download.microsoft.com/download/B/B/F/BBF0C6F3-6E36-4979-8C43-DE165AD7AE34/SP2010 Kerberos Guide.docx

Open Data Protocol

OData Service for SQL Azure. Quick and easy way to demo a data feed from sql azure especially from PowerPivot. Here are some interesting links

https://odata.sqlazurelabs.com/OData.svc/v0.1/hqd7p8y6cy/AdventureWorks

http://www.odata.org/producers

Display children of parent automatically in a hierarchy in PPS

Not a new feature of PPS 2010, but one that is little known or used and is mighty cool. You can select a parent member in a hierarchy in say a filter, and the object (a scorecard or report) will automatically display all of the children members. All with one line of code in a connection formula <<UniqueName>>.Children

Here is my hierarchy

here is the connection formula

and the result. Notice I select a country and get all the provinces.

it works across the hierarchy. Here I select a province and get all the cities

New Silverlight 4 Training Kit Available

Microsoft has just released a new free Silverlight 4 Training Kit that walks you through building business applications with Silverlight 4. You can also download the entire offline version of the kit here.

PowerPivot Excel Document as a Datasource for PPS 2010

this is cool. you can use a powerpivot excel doc as a analysis services datasource to build dashboards in PPS 2010. The connection string is:PROVIDER=MSOLAP;DATASOURCE=http://<servername>/PowerPivot%20Gallery/Demo.xlsx