Thomas Bolt's Business Intelligence Blog

Gemini and SQL Server 2008 R2 Setup

blog@tobo.ch — Wed, 12 Aug 2009 14:27:38 GMT

While installing the August CTP of SQL Server 2008 R2 I recognized a new dialog in the setup-wizard.

This dialog allows selecting between two roles. One is to install SQL Server Features – for example if you install a new SQL Server. And the other one if you want to install Gemini in a SharePoint Farm.

I will do the Gemini installation as soon as possible, an will let you know about the facts in my blog.

Technorati-Tags: SQL Server 2008 R2 CTP Gemini

SQL Server 2008 R2 August CTP available now

blog@tobo.ch — Mon, 10 Aug 2009 09:41:40 GMT

I just received the notification mail that the August CTP of SQL Server 2008 R2 will be available today (for MSDN and Technet Subscribers; public on 12th of August), and that I can start now registering myself for the Preview of Project Gemini. That’s what I’m waiting for, for a long time now. So hopefully I will get the invitation for the Gemini preview. I’m very interested in testing out Gemini and see what chances and what challenges will come up for our future projects.

Technorati-Tags: Microsoft SQL Server 2008 R2 August CTP Project Gemini Preview

PerformancePoint Server Planning – The living Dead

blog@tobo.ch — Thu, 18 Jun 2009 13:51:54 GMT

You thought PerformancePoint Server is dead? It is not!

Microsoft announced a new Accelerator-component on their partner website; The "Financial Planning Accelerator". This component is derived from the source code and project files of the Planning Module of PerformancePoint server and is available on a non-cost, individual license.

Microsoft Partners can get and modify the source code like it were open source. But in fact it isn't, because PPS Client Access Licenses are needed to distribute the code to the end users.

You can find more information about the Financial Planning Accelerator on Microsoft's Partner Website.

Technorati-Tags: PerformancePoint Server Planning,Financial Planning Accelerator,Microsoft Business Intelligence,BI

MDX LinkSet

blog@tobo.ch — Fri, 09 Nov 2007 13:55:00 GMT

While implementing dynamic dimension data security in Analysis Services 2005 I ran into a problem with linking members of a set into another hierarchy. My problem was that I needed a set of members from a specific hierarchy "A" to specify the allowed member set of the dimension hierarchy "A". Unfortunately it wasn't possible to get the members from the hierarchy "A". It was only possible to get them from another hierarchy "B" of the same dimension.

As you possibly know, there is a nice MDX function LinkMember, which can be used to do exactly this kind of conversion between hierarchies. Sadly, you can only link one member, but not a whole set of members.

I've searched a long time to find a solution for this problem, but couldn't find something. As far as I know, there is no possibility to do this in MDX, so I wrote my own LinkSet function in a .NET assembly. The following code-listing shows how I did that. I know there is possibly a better and more flexible solution, but it works for my requirements.

public static Set LinkSet(Set set, Hierarchy hierarchy)
{
    SetBuilder sbReturn = new SetBuilder();
    foreach(Tuple currentTuple in set.Tuples)
    {
        Expression exp = new Expression(hierarchy.UniqueName + ".[" + currentTuple.Members[0].Caption + "]");
        Tuple newTuple = exp.CalculateMdxObject(null).ToTuple();
        sbReturn.Add(newTuple);
    }

return sbReturn.ToSet();
}

Technorati Tags: Microsoft Analysis Services, MDX, OLAP

SharePoint Designer with Forms Login using Internet Explorer 7

blog@tobo.ch — Tue, 06 Nov 2007 17:42:00 GMT

If you want do design a SharePoint 2007 Services or Server Site which uses Forms Login with Office SharePoint Designer, it is important to save the authentication cookie on the client. This is required by SharePoint Designer, because it uses pass-through authentication, which doesn't work if you have forms authentication enabled on your site. By selecting "Login automatically" at the sites login screen, your credentials get stored in an authentication cookie, which then is used by SharePoint designer.

If you use Internet Explorer 7 on your machine and your site is only reachable over the internet, you will run into an authentication problem even if you have selected "Login automatically". The reason for this lies in the trusting-mechanism of IE7. As said before, the site is reachable over the internet. Therefore it isn't a trusted site per default, and the creation of an authentication cookie isn't allowed. So the only thing you have to do is to add it to your trusted sites in the internet options dialog. After that you can login again to your SharePoint site and checking the "Login automatically" checkbox.

Technorati-Tags: Microsoft SharePoint Designer

Microsoft PhotoSynth

blog@tobo.ch — Sun, 07 Oct 2007 19:37:00 GMT

Today I read a blog entry about a new photo viewer tool from Microsoft Live Labs - Microsoft PhotoSynth. This tool allows to put-in a lot of photos from the same place (e.g. the Marcus place of venecia). PhotoSynth then aligns them in a three dimensional space, and allows to browse the pictures in this space.
I know this sounds a little bit strange, but my personal impression is, that it looks very phantastic. Take a look for your own and download the tech preview: http://labs.live.com/photosynth/

Technorati-Tags: Tools , Image , PhotoSynth , 3D , Photo

Compensation Record Strategy

blog@tobo.ch — Fri, 05 Oct 2007 09:58:19 GMT

While modeling a data warehouse with an Analysis Services OLAP cube for a customer I ran into a problem with a "special" fact table. The customer wanted to do the following tasks with his cost controlling system:

1. Forecasting fulltime equivalents, head counts and costs of employees for the next 12 months on a monthly basis.

2. Displaying the forecast history on a daily granularity.

Full time equivalent (FTE) means the amount of work an employee can do. If he has a working capacity of 100% he does 1.0 FTE. If he only works the half day, he does only 0.5 FTE.
Head counts are simply the amount of employees the customer has, not regarding the working capacity.
And costs are monthly salaries and further costs paid for an employee.

Because forecasts are a fact to change, we should actualize the forecasts stored in the fact table. But regarding the second requirement, we cannot simply change or delete the records in our fact table, because if we do that, we will lose our history. So there is a need to implement this fact table as a kind of “slowly changing fact” table, in which old forecasts will stay in the table but marked as “old”.

To design an OLAP cube that is as fast as possible we should use facts that can be aggregated and as less as possible calculated measures. With our “slowly changing fact” table we must have selection logic to only select the needed members, because we cannot simply summarize or count old and current records. This can only be done implementing this logic in a calculated measure.

To avoid a performance bottleneck and using pre-aggregated measures, I used a special fact table design strategy called “Compensation Record Strategy”. This strategy allows to easily pre-aggregate all measures of this fact table, and to provide access to historical forecasts.

The compensation record strategy works with the principle to eliminate a whole fact record without deleting it before adding the current one. The following table shows a sample of that:

Employee	Submission Date	FTE	Head Count	Cost
Jon	Jan 01, 2007	1.0	1	3000
Jon	Jan 06, 2007	-1.0	-1	-3000
Jon	Jan 06, 2007	0.5	1	1500
Jon	Jan 15, 2007	-0.5	-1	-1500
Jon	Jan 15, 2007	0.8	1	2400

The first record says at January the first, that Jon works with 1.0 FTE for a salary of 3000. On January the 6^th a correction was done, that says that Jon now only works for the half day. To do that, we just eliminate the record from January the first doing a kind of contra entry, which leads to zero summarizing the first two records. Immediately after that, we will just enter a new record with the new facts. Summarizing all three records from January 1^st and January 6^th leads to the result of 0.5.

On January 15^th Jon’s employer decided to let him work for a working capacity of 80%. We compensated the old facts using a further compensation record and adding the new fact record to the table.

Conclusion

Implementing a changing fact table which allows access to historical data isn’t as easy as it looks like, if you keep performance requirements in mind. To address that you can use the described compensation record strategy which allows using the full performance of pre-aggregated measures in OLAP cubes. If you decide to use that modeling strategy you have to consider that the ETL logic will be more complicated and needs more development effort.

Technorati Tags: Data Warehouse, Data Modelling, Database, Analysis Services, OLAP

Microsoft Office PerformancePoint Server 2007 CTP4 released!

blog@tobo.ch — Thu, 16 Aug 2007 08:42:00 GMT

What a joy this morning as I looked at the connect site of PPS. CTP4 has been released this night (europe ;-))! I'm currently downloading all components and then I will try to bring them up on my virtual machine. More about my experiences with CTP4 later...

What are the key-advantages of CTP4? The following listing is a copy from the official "Whats new"-document downloaded from connect.

Planning Server

Business Modeler

· Business Rule Templates. Several additional rule templates have been added.

· PPSCmd. The command-line tool had some additions to provide ability for scripting of overall system processes. For example, the ability to deploy and reprocess models and data.

· Migration. Additional feature work was done to enable migration of an application between two server environments.

· Jobs. Improved user experience for executing and managing jobs.

· Data Types. Dimension member properties now support more data types.

Excel Client

· Offline Cache. Improved management of locally cached objects.

· Templates. Additional report templates have been added.

· Review and Approval. Reviewers and Approvers can now manage submissions through a single dialog. Support has been added for bulk operations.

· Filters. Form and report filters now support dimension properties.

Server

· Performance. Several changes were made to improve overall server performance. Additionally, Financial Job and calculations have specific performance enhancements.

· System Information. Error message reporting and system logging has been improved from prior releases.

· Associations. Improved capabilities for working with complex associations. Better performance handling large associations and movement of large data set.

· Security. Public interfaces have improved resistance to any potentially malicious attacks.

Monitoring Server

Dashboards

· OLAP View Sorting/Filtering. Users can now sort (by column) and filter empty rows/columns in Analytic Charts and Grids.

· OLAP View Types. Users can now switch between grids and charts and also change chart types (includes bar charts, stacked bar charts, stacked 100% bar charts, line charts and combined bar/line charts).

· OLAP Member Properties in Grid. Users can now add attributes of a member into the OLAP grid.

· Cell Level Actions. Users can now see the cell level actions behind a value in an OLAP grid or chart.

· Export to PowerPoint. SharePoint users can now export dashboard views to Microsoft Office PowerPoint.

· Multiple Filters. Users can now pass multiple dashboard filters to scorecards and report views.

· Dashboard Viewer for SharePoint Services Users can now add PerformancePoint Monitoring dashboard items to an existing SharePoint page through a new PerformancePoint Web Part.

Dashboard Designer

· Dashboard Designer Ribbon Changes. Usability improvements within the Dashboard Designer Ribbon, which is part of the Microsoft® Office Fluent™ User Interface.

· OLAP View Configuration. Enables light configuration options on charts and grid (fonts, formats, chart legend placement, grid layout).

· PAS Integration. Users can now add PAS report views into their dashboards.

· Time Intelligence KPI Filters. Users can now add Time Intelligence expressions to individual KPIs.

· Scorecard Filters. Users can now pass members from scorecards into report views. (Example: Passing the KPI Name to an SSRS report.)

Excel Services Support

· Excel Services as a Data Source. Users can now import tables or named ranges from Excel Services spreadsheets.

Technorati Tags: Microsoft PerformancePoint Server, Business Intelligence, CTP4

Installing ProClarity Analytics Server on the same machine as PerformancePoint Server CTP3 with SharePoint

blog@tobo.ch — Wed, 15 Aug 2007 15:09:18 GMT

If you install ProClarity Analytics Server on the same machine as your are running a PerformancePoint Server CTP3 environment with Office SharePoint Server 2007 (for Monitoring) you will run into problems after the installation. While opening the Analytics Server Administration Tool a login-dialog appears. Whatever you type-in, a successful login isn't possible.

The problem has a simple reason. SharePoint Server creates a new Web Application for localhost Port 80, and stops the Default Web in Internet Information Services. But the setup of ProClarity Analytics Server installs its Virtual Directories into the stopped Default Web. So the Administration Tool cannot connect to the required services on http:///PAS.

To solve this problem, you just have to create the virtual directories manually in the Sharepoint - 80 Web Application. Use an own Application Pool derrived from the DefaultAppPool for these Web Applications, because the one of Sharepoint uses very complex configurations from web.config, what leads into several problems. Just use a new one.

Technorati Tags: ProClarity Analytics Server, Business Intelligence, Microsoft PerformancePoint Server, Analysis, Problem

Adventure Works Light

blog@tobo.ch — Wed, 15 Aug 2007 13:52:18 GMT

I've downloaded the new lighweight sample database "Adventure Works LT" (LT stands for light) from codeplex (http://www.codeplex.com/MSFTDBProdSamples). This database was introduced from Microsoft as a small and handy sample without the complexity of the full Adventure Works OLTP database. Compared with the full AW database which has 70 tables with a size of 183MB the light version just contains 12 tables with a size of 7MB. As you can see in the following diagram, it is more overviewable and allows a better understanding, specially if you use it for your demonstrations.

Adventure Works LT

Adventure Works OLTP

Source: http://www.codeplex.com/MSFTDBProdSamples

Technorati Tags: Microsoft SQL Server, Adventure Works, Database, Sample

Problems installing Planning Server Samples

blog@tobo.ch — Tue, 14 Aug 2007 15:50:09 GMT

Today I've hardly tried to install the Planning Server Samples of the CTP2 release. I've done all the settings in the DeploymentConfig.xml correctly, but everytime I've started the Install.cmd Script I've got the following error message:

-----------------------------------------
C:\Program Files\Microsoft Office PerformancePoint Server\2007\Samples\Alpine Ski House>install.cmd

C:\Program Files\Microsoft Office PerformancePoint Server\2007\Samples\Alpine Ski House>..\Scripts\DeploymentPrepare.vbs
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.

Creating files with tasks to be executed
C:\Program Files\Microsoft Office PerformancePoint Server\2007\Samples\Scripts\DeploymentPrepare.vbs(112, 17) Microsoft VBScript runtime error: Path not found
--------------------------------------------

After researching this problem I could locate the real cause in a bug of the DeploymentPrepare.vbs Script. The script first copies all DeploymentTasks to the temporary-folder which lies in the Local Settings of the user profile. Because I was logged in as the Administrator of my domain PERFPOINT, this resultet in the following path: C:\Documents and Settings\Administrator.PERFPOINT\Local Settings\Temp. But the script used the user profile of the local administrator in C:\Documents and Settings\Administrator\Local Settings\Temp for building the temporary file structure. Despite the processing of this temporary files used the first path.

The solution of this problem was quite easy. I used the sysinternals tool "junction" to create a junction between this paths. That means that the Temp-Folder of Administrator.PERFPOINT points to the Temp-Folder of Administrator.
junction "C:\Documents and Settings\Administrator.PERFPOINT\Local Settings\Temp" "C:\Documents and Settings\Administrator\Local Settings\Temp"

PerformancePoint Server CTP4 announced

blog@tobo.ch — Tue, 14 Aug 2007 10:54:09 GMT

Yesterday I was very glad to see that there are some CTP4 bits of PerformancePoint Server available for Microsoft employees. http://bimvp.com/blogs/bsm/archive/2007/08/10/performancepoint-server-2007-monitoring-and-analytics-got-ctp4-today.aspx
That means that the CTP4 release is not far away. It hope that it happens this week.