SharePoint is the Fastest Growing Server Product in Microsoft’’s History
The SharePoint Products and Technologies Team Blog announced today that SharePoint has passed the $800 Million mark for the previous fiscal year and is the fastest growing Microsoft server product in Micro$oft’’s history. Congrats to the Blue Monster!
Business Intelligence Survey Updated
Filed under: Business Intelligence, PerformancePoint Server 2007
Nigel Pendse has renamed his OLAP Survey to the BI Survey. Major players in BI, including Microsoft rely on the data in this survey to analyze consumer needs. From the invitation:
We would very much welcome your participation in The BI Survey. This is the largest independent survey of business intelligence/OLAP users worldwide. The Survey will obtain input from a large number of users to better understand their buying decisions, the implementation cycle and the business success achieved. Both business and technical respondents are welcome.
See Nigel in action, talking about the newest release of his survey.
PerformancePoint Rumored To Go RTM as Early as September
Filed under: Business Intelligence, PerformancePoint Server 2007
Thanks to Chris Webb for the tip off.
Rumored PPS Release Date.
PerformancePoint Planning Must Read
Filed under: Business Intelligence, PerformancePoint Server 2007
This series of posts was brought to my attention by Ben Tamblyn and outlines a very practical understanding of PerformancePoint Planning. A series of four articles, Adrian Downes has taken the time to shed some light on not only the features of PPS Planning, but the business models that drive them. Good stuff.
- Setting the stage (Budgeting 101)
- Configuring the Application (Introducing the Planning Administration Console)
- Defining the Model (Introducing Planning Business Modeler)
- Defining Business Rules
Using the BDC and SharePoint Profile Database to Create A Company Directory Lookup
Using BDC to query our company’’s SharePoint profiles database ( SharedServices1_DB in our environment ) I was able to create a pretty snappy employee lookup. The data is contained in two tables, the first is the UserProfile_Full table, which contains the Basic Profile Information. The second is the UserProfileValue table. The two are linked by the RecordID Field.
First things first - we designed a SQL query that would suit our needs and display the information we needed. Here is what my final query looked like:
declare @department nvarchar(100)set @department = ''''declare @employee nvarchar(256)set @employee = ''''select a.Employee, b.Title, e.Department, a.[Office Phone], d.[Cell Phone], c.Email
from
(select
a.RecordID,
a.PreferredName as Employee,
b.PropertyVal as [Office Phone]
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=8 and
a.RecordID=b.RecordID) a
left outer join
(select
a.RecordID,
a.PreferredName as Employee,
b.PropertyVal as Title
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=13 and
a.RecordID=b.RecordID) b
on a.RecordID=b.RecordID
left outer join
(select
a.RecordID,
b.PropertyVal as Email
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=9 and
a.RecordID=b.RecordID) c
on a.RecordID=c.RecordID
left outer join
(select
a.RecordID,
b.PropertyVal as [Cell Phone]
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=19 and
a.RecordID=b.RecordID) d
on a.RecordID=d.RecordID
left outer join
(select
a.RecordID,
b.PropertyVal as Department
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=14 and
a.RecordID=b.RecordID) e
on a.RecordID=e.RecordID
where
cast (e.Department as nvarchar(100)) like @department + ''%''
and a.Employee like @employee + ''%''
and a.[Office Phone] is null
order by
a.Employee
Next I needed to build a BDC xml file for the lookup. We created one to use two wildcard filter descriptors, one for the department and one for the employee name.
<?xml version="1.0" encoding="utf-8" standalone="yes" ?><LobSystem Type="Database" Version="1.5.7.00" Name="EmployeePhoneList" xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog"><Properties>
<Property Name="WildcardCharacter" Type="System.String">%</Property>
</Properties>
<AccessControlList>
<AccessControlEntry Principal="yourdomain\security_group">
<Right BdcRight="Execute"/>
<Right BdcRight="Edit"/>
<Right BdcRight="SetPermissions"/>
<Right BdcRight="SelectableInClients"/>
</AccessControlEntry>
<AccessControlEntry Principal="yourdomain\another_security_group_or_user">
<Right BdcRight="Execute" />
<Right BdcRight="SelectableInClients" />
</AccessControlEntry>
</AccessControlList>
<LobSystemInstances>
<LobSystemInstance Name="ProfileDB">
<Properties>
<Property Name="AuthenticationMode" Type="System.String">PassThrough</Property>
<Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>
<Property Name="RdbConnection Data Source" Type="System.String">SP01</Property>
<Property Name="RdbConnection Initial Catalog" Type="System.String">SharedServices1_DB</Property>
<Property Name="RdbConnection User ID" Type="System.String">SQLUserAccount</Property>
<Property Name="RdbConnection Password" Type="System.String">password</Property>
<Property Name="RdbConnection Integrated Security" Type="System.String"/>
</Properties>
</LobSystemInstance>
</LobSystemInstances>
<Entities>
<Entity Name="Profiles">
<Properties>
<Property Name="Title" Type="System.String">Employee Contact List</Property>
</Properties>
<Identifiers>
<Identifier Name="Department" TypeName="System.String"/>
<Identifier Name="Employee" TypeName="System.String" />
</Identifiers>
<Methods>
<Method Name="GetProfiles">
<Properties>
<Property Name="RdbCommandText" Type="System.String">
select a.Employee, b.Title, e.Department, a.[Office Phone], d.[Cell Phone], c.Email
from
(select
a.RecordID,
a.PreferredName as Employee,
b.PropertyVal as [Office Phone]
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=8 and
a.RecordID=b.RecordID) a
left outer join
(select
a.RecordID,
a.PreferredName as Employee,
b.PropertyVal as Title
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=13 and
a.RecordID=b.RecordID) b
on a.RecordID=b.RecordID
left outer join
(select
a.RecordID,
b.PropertyVal as Email
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=9 and
a.RecordID=b.RecordID) c
on a.RecordID=c.RecordID
left outer join
(select
a.RecordID,
b.PropertyVal as [Cell Phone]
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=19 and
a.RecordID=b.RecordID) d
on a.RecordID=d.RecordID
left outer join
(select
a.RecordID,
b.PropertyVal as Department
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=14 and
a.RecordID=b.RecordID) e
on a.RecordID=e.RecordID
where
cast (e.Department as nvarchar(100)) like @department + ''%''
and a.Employee like @employee + ''%''
and a.[Office Phone] is not null
order by
a.Employee
</Property>
<Property Name="RdbCommandType" Type="System.String">Text</Property>
</Properties>
<FilterDescriptors>
<FilterDescriptor Type="Wildcard" Name="Department">
<Properties>
<Property Name="DeptSearch" Type="System.String">Is Like</Property>
</Properties>
</FilterDescriptor>
<FilterDescriptor Type="Wildcard" Name="Employee">
<Properties>
<Property Name="EmplSearch" Type="System.String">Is Like</Property>
</Properties>
</FilterDescriptor>
</FilterDescriptors>
<Parameters>
<Parameter Direction="In" Name="@department">
<TypeDescriptor TypeName="System.String" IdentifierName="Department" AssociatedFilter="Department" Name="Department">
<DefaultValues>
<DefaultValue MethodInstanceName="PhoneFinderInstance" Type="System.String"></DefaultValue>
</DefaultValues>
</TypeDescriptor>
</Parameter>
<Parameter Direction="In" Name="@employee">
<TypeDescriptor TypeName="System.String" IdentifierName="Employee" AssociatedFilter="Employee" Name="Employee">
<DefaultValues>
<DefaultValue MethodInstanceName="PhoneFinderInstance" Type="System.String"></DefaultValue>
</DefaultValues>
</TypeDescriptor>
</Parameter>
<Parameter Direction="Return" Name="Lookup">
<TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="DepartmentDataReader">
<TypeDescriptors>
<TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="DepartmentDataRecord">
<TypeDescriptors>
<TypeDescriptor TypeName="System.String" IdentifierName="Department" Name="Department">
<LocalizedDisplayNames>
<LocalizedDisplayName LCID="1033">Department</LocalizedDisplayName>
</LocalizedDisplayNames>
</TypeDescriptor>
<TypeDescriptor TypeName="System.String" IdentifierName="Employee" Name="Employee">
<LocalizedDisplayNames>
<LocalizedDisplayName LCID="1033">Employee</LocalizedDisplayName>
</LocalizedDisplayNames>
</TypeDescriptor>
<TypeDescriptor TypeName="System.String" Name="Title">
<LocalizedDisplayNames>
<LocalizedDisplayName LCID="1033">Title</LocalizedDisplayName>
</LocalizedDisplayNames>
</TypeDescriptor>
<TypeDescriptor TypeName="System.String" Name="Office Phone">
<LocalizedDisplayNames>
<LocalizedDisplayName LCID="1033">Office Phone</LocalizedDisplayName>
</LocalizedDisplayNames>
</TypeDescriptor>
<TypeDescriptor TypeName="System.String" Name="Cell Phone">
<LocalizedDisplayNames>
<LocalizedDisplayName LCID="1033">Cell Phone</LocalizedDisplayName>
</LocalizedDisplayNames>
</TypeDescriptor>
<TypeDescriptor TypeName="System.String" Name="Email">
<LocalizedDisplayNames>
<LocalizedDisplayName LCID="1033">Email</LocalizedDisplayName>
</LocalizedDisplayNames>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name="PhoneFinderInstance" Type="Finder" ReturnParameterName="Lookup" />
</MethodInstances>
</Method>
</Methods>
</Entity>
</Entities>
</LobSystem>
Once we uploaded the BDC, we created a new page for the BDC and implemented the Business Data List Webpart, chose our new BDC as the Type and voila!