Category Archives: Business Intelligence

Cool Stuff : All up Business Intelligence Demo – Release 9.2

Microsoft has developed a comprehensive Business Intelligence solution demo (version 9.2) using SQL Server 2008 R2, Microsoft Office 2010 and SharePoint 2010 technology. This solution enables Business Intelligence for everyone at Contoso (fictional company created by Microsoft) through familiar tools, self–service capabilities, and access to critical business information.

There are two ways to access this demo solution, online remote access to server configured by Microsoft or via configuring virtual machine locally.

The Demo Release 9.2 has been made available on the Microsoft Partner site for download.  There are 42 files in the download (around 29 GB in all, once extracted though over 80).  Need some serious RAM and of course Hyper-V for it to work.

Key Features:
• SharePoint 2010 Insights, PerformancePoint Services, Excel Services, Visio Services, Reporting Services, Search, Social Relevance, Communities, Team Sites, People Profiles, Blogs, Organizational Browser, Workflows
• Silverlight, integrated with Bing Maps for the Enterprise and SQL Server Analysis Services
• Office 2010 Web Applications, Excel Co-editing
• PowerPivot for SharePoint, Excel
• PowerPivot Management Dashboard
• Report Builder 3.0
• SQL Server 2008 R2 DB Engine

Get it from here:

For more info :


Business Intelligence with SharePoint and Excel

Today’s business decision-makers have a distinct advantage over their predecessors when it comes to business intelligence (BI) tools that they can use for analysis, trending, and presentation of data. A good BI strategy, supported by a solid base of BI technologies, can streamline business processes while also boosting productivity and profit. The overall BI strategy of Microsoft comprises a suite of server and client-side data integration tools. Powerful analytical and reporting tools in SQL Server 2005 provide the backbone data management infrastructure, while Microsoft Office applications, specifically Microsoft Office Excel, provide the flexibility for information workers to remotely interact with centralized and secure data sources. This article, which is adapted from the Microsoft Press book Microsoft Office SharePoint Server 2007 Best Practices, focuses on how you can use Excel and Excel Services to analyze business intelligence data within SharePoint sites.

Create KPIs Programmatically in PerformancePoint 2007

I created this web project while I was investigating the performancepoint API, it was really a very hard and silly task so I decided to share this code with you 🙂

Firstly, Add a reference to :
\Microsoft Office PerformancePoint Server\3.0\Monitoring\Assemblies\Microsoft.PerformancePoint.Scorecards.Common.dll

using Microsoft.PerformancePoint.Scorecards;
using System.Net;

public partial class _Default : System.Web.UI.Page
private Kpi kpi_wows;
private PmService Publisher;

protected override void OnInit(EventArgs e)
Publisher = (PmService)PmService.CreateInstance(“http://localhost:40000/webservice/pmservice.asmx”);
Publisher.Credentials = CredentialCache.DefaultNetworkCredentials;

protected void Page_Load(object sender, EventArgs e)

private void CreateKPIandSetSomeProperties()
// Create a new kpi instance
kpi_wows = Kpi.CreateNew();
// Setting its name
kpi_wows.Name.Text = “zzz”;
// Setting the Owner
BpmPropertyUser Owner = new BpmPropertyUser();
Owner.Login = @”Server\Administrator”;
kpi_wows.Owner = Owner;
// setting the actual
KpiMeasure ActualMeasure = new KpiMeasure();
ActualMeasure.Guid = Guid.NewGuid();
ActualMeasure.ModelCurrent = 345M;
BpmPropertyText ActualName = new BpmPropertyText();
ActualName.Text = “The Actual”;
ActualMeasure.Name = ActualName;
ActualMeasure.ValueSource = ValueSources.MaxRollup;
kpi_wows.Actual = ActualMeasure;
// Setting the Target
Target ATarget = new Target();
ATarget.Guid = Guid.NewGuid();
ATarget.ModelCurrent = 656M;
BpmPropertyText TargetName = new BpmPropertyText();
TargetName.Text = “The Target”;
ATarget.Name = TargetName;
// Scoring Pattern
ATarget.Pattern = KpiPattern.DecreasingIsBetter;
// Banding Method
Banding TargetBanding = new Banding();
TargetBanding.Type = BandType.Normalized;
TargetBanding.ActualWorst = 999M;
TargetBanding.SpreadMinimum = 0M;
TargetBanding.SpreadMaximum = 1.2M;
ATarget.Banding = TargetBanding;
ATarget.IndicatorId = new Guid(“70b22c72-6523-487d-ae4a-ad607431584f”);
ATarget.IsCustomCurrentFormula = true; // If true, then CurrentFormula is used to compute the current value.
//ATarget.CurrentFormula = “————–MDX Formula Goes Here ————-“;
ATarget.CurrentFormula = “0”;
//Adding Description
kpi_wows.Description.Text = “This is a kpi created by the object model”;
// Assigning Permissions
Microsoft.PerformancePoint.Scorecards.Membership AdministratorMembership = new Microsoft.PerformancePoint.Scorecards.Membership();
AdministratorMembership.Login = @”Server\Administrator”;
AdministratorMembership.Role = “Editor”;
Microsoft.PerformancePoint.Scorecards.Membership ReaderMembership = new Microsoft.PerformancePoint.Scorecards.Membership();
ReaderMembership.Login = @”Server\ayman_elhattab”;
ReaderMembership.Role = “Reader”;
// Assigning the Responsible Person
kpi_wows.Owner.Login = @”Server\Administrator”;

// publishing the KPI


Create Scorecards and Dashboards Programmatically in PerformancePoint

Hi All, I managed to create a Dashboard and a Scorecard programmatically, I failed to find any resources, articles or anything online talking about this subject.
That’s why I decided to share it online with you!

Here is the web project I created while I was investigating The PerformancePoint API ..

Firstly, Add a reference to :\Microsoft Office PerformancePoint Server\3.0\Monitoring\Assemblies\Microsoft.PerformancePoint.Scorecards.Common.dll
using Microsoft.PerformancePoint.Scorecards;
using System.Net;

public partial class _Default : System.Web.UI.Page
private Scorecard AScoreCard_wows;
private PmService Publisher;

protected override void OnInit(EventArgs e)
Publisher = (PmService)PmService.CreateInstance(“http://localhost:40000/webservice/pmservice.asmx”);
Publisher.Credentials = CredentialCache.DefaultNetworkCredentials;
protected void Page_Load(object sender, EventArgs e)
protected void CreateScoreCard()
Guid AGuid;
// create a new scorecard instance
AScoreCard_wows = Scorecard.CreateNew();
// Setting the name property;
BpmPropertyText NameProperty = new BpmPropertyText();
NameProperty.Text = “The ScoreCard”;
AScoreCard_wows.Name = NameProperty;
AScoreCard_wows.Description.Text = “This Scorecard was created by code”;
// Setting the owner property
AScoreCard_wows.Owner.Login = @”Server\Administrator”;
// Configuring the scorecard view
ConfiguredView ACofiguredView = new ConfiguredView();
GridViewDefinition ParentChildDefinition = new GridViewDefinition();
GridHeaderItem ParentX = new GridHeaderItem();
AGuid = Guid.NewGuid();
ParentX.Guid = AGuid;
ParentX.DefinitionGuid = AGuid;
ParentX.DisplayText = “ParentX”;
ParentX.DimensionName = “Scorecard__”;
ParentX.DimensionValue = “cd30315e-bd40-4977-b21b-cbb47cc83494”; // Guid of KPI
ParentX.HeaderType = ScorecardNodeTypes.Kpi;
ParentX.Depth = 1;
GridHeaderItem ChildX = new GridHeaderItem();
AGuid = Guid.NewGuid();
ChildX.Guid = AGuid;
ChildX.DefinitionGuid = AGuid;
ChildX.DisplayText = “ChildX”;
ChildX.DimensionName = “Scorecard__”;
ChildX.DimensionValue = “e9ec1e6b-8c7e-4f7d-b87d-28cad4b29d7f”; // Guid of KPI
ChildX.LinkedKpiId = new Guid(“e9ec1e6b-8c7e-4f7d-b87d-28cad4b29d7f”);
ChildX.HeaderType = ScorecardNodeTypes.Kpi;
ChildX.Depth = 2;
GridHeaderItem ChildY = new GridHeaderItem();
AGuid = Guid.NewGuid();
ChildY.Guid = AGuid;
ChildY.DefinitionGuid = AGuid;
ChildY.DisplayText = “ChildY”;
ChildY.DimensionName = “Scorecard__”;
ChildY.DimensionValue = “8878d533-b7b7-40f7-8772-4da81c54c85b”; // Guid of KPI
ChildY.LinkedKpiId = new Guid(“8878d533-b7b7-40f7-8772-4da81c54c85b”);
ChildY.HeaderType = ScorecardNodeTypes.Kpi;
ChildY.Depth = 2;
GridHeaderItem ActualColumn = new GridHeaderItem();
AGuid = Guid.NewGuid();
ActualColumn.Guid = AGuid;
ActualColumn.DefinitionGuid = AGuid;
ActualColumn.DisplayText = “Actual”;
ActualColumn.DimensionName = “Scorecard_Measures__”;
ActualColumn.DimensionValue = “Actual”; // Guid of KPI
ActualColumn.HeaderType = ScorecardNodeTypes.KpiActual;
ActualColumn.Depth = 1;
GridTargetSettings ActualColumnTargetSettings = new GridTargetSettings();
ActualColumnTargetSettings.ShowImage = true;
ActualColumnTargetSettings.ShowScore = true;
ActualColumnTargetSettings.RollupType = RollupTypes.AverageWeighted;
ActualColumnTargetSettings.ScoreType = ScoreTypes.Normalized;
ActualColumn.TargetSettings = ActualColumnTargetSettings;
GridHeaderItem TargetColumn = new GridHeaderItem();
AGuid = Guid.NewGuid();
TargetColumn.Guid = AGuid;
TargetColumn.DefinitionGuid = AGuid;
TargetColumn.DisplayText = “Target”;
TargetColumn.DimensionName = “Scorecard_Measures__”;
TargetColumn.DimensionValue = “Target”; // Guid of KPI
TargetColumn.HeaderType = ScorecardNodeTypes.KpiTarget;
TargetColumn.Depth = 1;
GridTargetSettings TargetColumnTargetSettings = new GridTargetSettings();
TargetColumnTargetSettings.ShowImage = true;
TargetColumnTargetSettings.ShowScore = true;
TargetColumnTargetSettings.RollupType = RollupTypes.AverageWeighted;
TargetColumnTargetSettings.ScoreType = ScoreTypes.Raw;
TargetColumn.TargetSettings = TargetColumnTargetSettings;
ACofiguredView.GridViewDefinition = ParentChildDefinition;
// Publishing the ScoreCard
protected void CreateDashBoard()
//Create An Instance
ADashboard_wows = Dashboard.CreateNew();
// Setting the Name
ADashboard_wows.Name.Text = “Ayman’s Dashboard__”;
//Setting the owner
ADashboard_wows.Owner.Login = @”Server\Administrator”;
//Adding A Page
// Creating a Page
DashboardElementContainer APage = new DashboardElementContainer();
APage.Guid = Guid.NewGuid();
APage.Name.Text = “Ayman’s Page__”;
APage.Owner.Login = @”Server\Administrator”;
// Creating A Zone
DashboardElementContainer AZone = new DashboardElementContainer();
AZone.Guid = Guid.NewGuid();
AZone.Name.Text = “Ayman’s Zone__”;
AZone.Owner.Login = @”Server\Administrator”;
// Creating a dashboard Item ( scorecard )
DashboardItem Item = new DashboardItem();
Item.Guid = Guid.NewGuid();
Item.UnderlyingElementId = AScoreCard_wows.Guid;
Item.Name.Text = “Ayman’s Item__”;
Item.Owner.Login = @”Server\Administrator”;
// Adding the Item to the zone
// Adding the zone to the page
// Adding the page to the dashboard