Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Archive for December, 2007

Online PerformancePoint Server (PPS) 2007 Training

Posted by denglishbi on December 18, 2007

If you haven’t had a chance to check it out yet there is some free training available online (videos and downloadable slides).  In order to download the workbooks you might be prompted to install the latest version of the .NET Framework (3.5).  If I recall correctly, this install required a reboot on my laptop.  Here is what is available in regards to training and I add the duration of the videos so you can plan accordingly:

Building a planning budgeting or forecasting application

Module 1: Business integration (29:20)     video | workbook

Module 2: Architecting a PPS 2007 planning application (39:20)     video | workbook

Module 3: Fundamentals of a planning application (19:22)     video | workbook

Module 4: Building business models and data dimensions (44:04)    video | workbook

Module 5: Data Loading  (22:15)    video | workbook

Module 6: Managing roles and security in a planning application (16:51)     video | workbook

Module 7: Fundamentals of the Excel add-in for PPS 2007 (26:14)    video | workbook

Module 8: Embedding rules and calculations into a planning application (25:53)     video | workbook

Module 9: Planning process management and workflows (19:06)     video | workbook

Building a scorecard, dashboard, or analysis application

Module 10: Fundamentals of monitoring and analyzing data with PPS 2007 (19:40)     video | workbook

Module 11: Fundamentals of dashboarding, scorecarding and analytics (33:05)     video | workbook

Module 12: PPS 2007 elements for monitoring and analysis (1:13:55)     video | workbook

Module 13: Building dashboards with PPS 2007 (21:27)     video | workbook

Module 14: Managing security in dashboards, scorecards and analytic (15:44)     video | workbook

Posted in PerformancePoint | Tagged: , | Leave a Comment »

BIDS Helper released today

Posted by denglishbi on December 17, 2007

This includes a few fixes that were introduced in the 1.2 version.  There was an issue with multiple buttons being generated in the calculations tab that has been fixed.  Here is a description of the fixes:

Release Description

This release incorporates a couple of bug fixes and some minor performance improvements.

  • FIXED: Multiple copies of the BIDS Helper buttons appearing in the calculations tab of the cube designer
  • FIXED: Occasionally the Expression/Configuration highlighter would fail to highlight items correctly
  • FIXED: The performance of the highlighter component has been slightly improved.

If you haven’t installed this add-in for Business Intelligence Development Studio I would highly recommend it.  It is a lifesaver if you are trying to test out your calculations in your cube and just need to deploy the MDX script.  Find out more about the product on CodePlex.

Posted in Downloads | Tagged: , | Leave a Comment »

Microsoft Business Intelligence Screencasts

Posted by denglishbi on December 15, 2007

There were a bunch of screencasts added to the Channel9 site this past week in regards to Microsoft Business Intelligence.  If you don’t subscribe to an RSS feed to get this information or haven’t checked out this site before then I would recommend it to get you up-to-speed.  You can download the files for viewing offline or to synch up to your Zune to take on the road with you or you can view them online.


Here is what is available (looks like the second one on the list is not uploaded yet and the third one got uploaded twice):

#00: Platform Overview (25 minutes, 25 seconds)

#01a: Introduction to Microsoft BI (20 minutes, 33 seconds)

#01b: Introduction to SQL Server 2005 Tools and Sample Databases (13 minutes, 16 seconds)

#02a: Developing a Query-Driven E-mail Delivery System (17 minutes, 37 seconds)

#03a: Loading and Integrating Data from Multiple Sources (17 minutes, 13 seconds)

#04a: Identifying and Importing New Customers by Using Fuzzy Lookup (12 minutes, 5 seconds)

#04b: Implementing Slowly Changing Dimensions in the Data Flow (16 minutes, 11 seconds)

#04c: Advanced Package Design (13 minutes, 26 seconds)

#05a: Designing a Basic Report (18 minutes, 10 seconds)

#05b: Adding Interactivity to a Report (13 minutes, 59 seconds)

#06a: Developing a Report Model and Designing a Report Builder Report (15 minutes, 46 seconds)

#07a: Deploying and Accessing Reports (16 minutes, 23 seconds)

#09a: Designing Dimensions (16 minutes, 55 seconds)

#09b: Advanced Dimension Design (12 minutes, 25 seconds)

#09c: Using the Cube Wizard (13 minutes, 20 seconds)

#09d: Introducing Cube Calculations (14 minutes, 55 seconds)

#10a: Designing Reports on the UDM (15 minutes, 28 seconds)

#10b: Exploring Analysis Features of Microsoft Excel 2007 (14 minutes, 57 seconds)

#11a: More Analysis with Microsoft Excel 2007 (12 minutes, 45 seconds)

#12a: Introducing Data Mining (14 minutes, 54 seconds)

#12b: Introducing the Table Analysis Tool for Excel 2007 (15 minutes, 28 seconds)

#14a: Building a Performance Point Scorecard (15 minutes, 55 seconds)

Posted in Training | Tagged: | Leave a Comment »

Microsoft Office 2007 SP1 has been released!

Posted by denglishbi on December 12, 2007

SP1 for Office 2007 was released this week and if you have Office 2007 installed this should have been downloaded with your Windows Updates (checked my applications and it doesn’t appear that this has happened yet).

Office 2007 Service Pack 1

Download the new service packs for your Office 2007 products.

· Office 2007 Service Pack 1

· Visio 2007 Service Pack 1

· Project 2007 Service Pack 1

Not sure if you have SP1 installed yet, then check out this link – How to determine which version of a 2007 Office product is installed

Posted in Downloads | Leave a Comment »

Microsoft Business Intelligence VPC Release 5.1

Posted by denglishbi on December 12, 2007

Microsoft has posted a BI VPC that is fully loaded with all of the BI components on their download site.  There are two links and a couple of files that you will need to download to extract the VPC.  The total download is a little over 4GB and the extracted VHD is around 15GB.  According to the readme file the OS is setup to expire on 3/5/2009. 

Here is what is included on the VPC:

Microsoft Windows Server 2003 R2 Service Pack 2
Internet Explorer 7
Microsoft Office Enterprise 2007
Microsoft Office Visio 2007
Microsoft Office Project 2007
Microsoft Office SharePoint Designer 2007
Microsoft SharePoint Portal Server 2007
Microsoft Visual Studio 2005 w/ Service Pack 1
Microsoft SQL Server 2005 w/ Service Pack 2 (w/ SSIS, SSAS, SSRS)
Microsoft SQL Server 2005 Data Mining Add-ins
Microsoft SQL Server 2005 SharePoint Integration Add-in
ProClarity Analytics Server 6.3
ProClarity Dashboard Server 6.3
ProClarity Desktop Professional 6.3
ProClarity Web Professional 6.3
Microsoft PerformancePoint Planning Server 2007
Microsoft PerformancePoint Monitoring Server 2007
Microsoft PerformancePoint Dashboard Designer
Microsoft PerformancePoint Planning Add-In for Excel
Microsoft PerformancePoint Planning Business Modeler
Project REAL Reference Implementation
SoftArtisans OfficeWriter 3.8.1 and Designer
BI Demo Files
Microsoft SQL Server 2005 Samples
Microsoft SQL Server BI Metadata Samples
Performance Point Planning Samples/Labs
Visual Studio 2005 Tools for Office Second Edition

Here are the links for the downloads: Link1 and Link2.  Enjoy!

Posted in Downloads | Leave a Comment »

SSAS returning wrong results (cache related)

Posted by denglishbi on December 12, 2007

Ran into a rather odd issue today.  End-user reported that the chart being displayed to them was returning the wrong results and different filters were returning the same results.  Here is the example of two queries that I used to validate the results and I was able to reproduce the issue:

SELECT NON EMPTY { { [Date].[Quarter Year].&[2007]&[3], [Date].[Quarter Year].&[2007]&[4], [Date].[Quarter Year].&[2008]&[1] } } ON COLUMNS,

NON EMPTY { Crossjoin({ [Brand].[Brand Label].[Brand1]}, { [Measures].[TBA], [Measures].[TBA(Base)] } ) } ON ROWS

FROM [cube] WHERE ( ( [Market].[Market].[USS] ), { [State].[State].&[Georgia], [State].[State].&[Florida], [State].[State].&[South Carolina] } )


select {[Measures].[TBA], [Measures].[TBA(Base)]} on columns,

non empty([Brand].[Brand Label].[Brand1]) on rows

from cube where ([Market].[Market].&[USS], [Date].[Quarter Year].&[2007]&[3])

If I ran these queries in this order the results for 2007 Q3 were 56% and 1082.  This was correct for the first one based on the WHERE clause, but not for the second MDX statement.  I was really puzzled, but I decided to clear the cache and try again.  This time I ran the queries in reverse order and I got the correct values (second query was 49.6% and 3312.  I found a posting about setting the Cache Policy in the connection string to 9 or setting this at the server level in the msmdsrv.ini file (setting CalculationCoverPolicy to 9 instead of 0 which is the default).  When I did this modification and restarted SSAS everything worked properly no matter which order I ran the MDX statements in.  Really odd behavior still since both queries have different filters for the WHERE clause.  Luckily I saw the Cache Policy setting in Mosha’s posting  and Chris Webb also had a similar posting about this type of issue.  There was also a posting in the Microsoft Forums that I used for reference.

I tested this in SQL 2008 Nov CTP5 and similar results.  When I ran them in this order the second query actually returned 151.9% instead of 56% even though the correct result was supposed to be 49.6%.  The second value was the same in SQL 2005.  The same fix to the msmdsrv.ini file fixes the issue though.  I will have to make a call ticket with MS Support and make them aware of this bug.  I will let you know if they tell me to make a different fix to resolve this issue.

UPDATE (12/19/2007):

Just wanted to provide an update in regards to this issue with the cache reuse in Analysis Services.  I did test both these MDX statements in Mosha’s MDX Studio and when the 2nd statement is executed it will only return the results from the cache and it will not perform any calculations.  If you clear the cache and run them in the reverse order then it will perform the necessary calculations and everything is fine.  Making the ‘Cache Policy’ setting change does resolve the issue and you can run the statements in either order. 

MS Support got back to me and they did state that there is an issue with the cache reuse with the current product (and actually the next version of the product) and they have filed the issue with the development team.  The interesting thing is that another way that they reported that this can be resolved was swapping out the function being used in one of the calculations.  The current calculation is using the Aggregate function and they switched it to the Sum function and this fixed the problem also.  Really odd since these are basically setup to do the same thing.  They stated that it can be problematic using the Aggregate function to aggregate a calculated member.

In regards to the Cache Policy setting here is what they had to say:

The cache policy setting (9 instead of 0) you were using as a workaround is intended to change how the formula engine answers queries; it will cause the formula engine to lean towards using block evaluation mode more, instead of cell-by-cell mode. Generally, this can result in faster queries, but it can also slow down some queries, so there is a risk involved with it. Microsoft CSS does not usually recommend this setting as a general approach/resolution.

It’s usually a better idea to find ways to improve the queries without using cache policy; it should only be used as a last resort, and only under the direction of CSS. We generally do not make the details of the cache policy settings public, because they do carry risks, and they are intended only for special cases that should be determined by customer support.

Makes sense, but how would I have ever come to the conclusion that the issue was actually with the function we were using to perform the calculation?  Extremely odd behavior if you ask me and this is pretty scary.  Hopefully they will come up with a solution to resolve the incorrect cache reuse.  How do I know that this is the only scenario that is going to return incorrect results?

Posted in Analysis Services | Tagged: | Leave a Comment »

SQL Server 2008 Intellisense (CTP5) vs Red Gate SQL Prompt

Posted by denglishbi on December 10, 2007

I gave the intellisense in the SQL Server 2008 Nov CTP5 a test run this morning and was a bit disappointed compared to SQL Prompt by Red Gate.  I am used to all of the settings within SQL Prompt (enabling options, integration, formatting, snippets, etc.) that the CTP did not meet my expectations.  It did the basics, but in my opinion still has a ways to go.  Now I see why it was not released with SQL Server 2005.

[Click on the pictures for a larger view of the image]

Here are a few captures of what it looks like and it is similar to Visual Studio intellisense where the CTRL+Spacebar (CTRL+J will also work) will display the dropdown list.

SQL Intellisense Example

I did like the fact that it would tell you went you had an invalid column (typo) prior to execution.

SQL Intellisense Error

I was never able to get the intellisense to show up for stored procedures, but it does state that it should work with them.  There aren’t too many configuration settings either.

SQL Intellisense Configuration

Now compared to SQL Prompt the only thing that I haven’t seen yet (and maybe it is just the version I am running — 2.0) is the invalid column message, but otherwise you get quite a few more options.

Setting up how the intellisense gets activated and what applications SQL Prompt will integrate with.

SQL Prompt Options

Setting up what you want displayed in the dropdown list and formatting options.

SQL Prompt Configuration

Enabling the capitalization of keywords is kind of a picky thing, but I like it.

SQL Prompt Capitalization

And you get snippets, so you can type ‘w2’ and then TAB and it will execute sp_who2 and the results will be displayed.  Code snippets are pretty nice and save you some keystrokes and we all like that.

SQL Prompt Snippets

And the intellisense you get with SQL Prompt is more intelligent.

SQL Prompt Example1

And it works with stored procedures (the SQL Server documentation says that it should be working, but not sure how to activate this yet — if it works yet).

SQL Prompt Example2

Also SQL Prompt works with INSERT, UPDATE, and DELETE statements and it does not appear that the SQL Server version does yet.  So for now I would have to give Red Gate the advantage, we will see if the SQL Server Team decides to add more functionality and configurations.

UPDATE 2/23/2008 – I have a Feb CTP6 follow up to this posting SQL Server Intellisense (CTP6) vs Red Gate SQL Prompt – Part II

Posted in SQL Server | Tagged: | Leave a Comment »

SQL 2008 and Change Tracking (screencast)

Posted by denglishbi on December 8, 2007

Just got done watching this screencast by Mike Taulty in regards to change data tracking in SQL 2008.  I had not heard about this new feature yet and this was a great demonstration of how it works.  I would highly recommend watching this if you have or have not heard about this yet.  I believe this might require that you install Silverlight in order to watch the video.  The video is approximately 16 minutes long.

Video: SQL 2008 and Change Tracking

Short video on working with change tracking in SQL Server 2008 by Mike Taulty.

Posted in SQL Server | Tagged: , | Leave a Comment »

PerformancePoint Server MS Beta Exam 71-556

Posted by denglishbi on December 7, 2007

PerformancePoint Server MS Beta Exam:  Now Available until December 31st!   (General Release will be in early 2008)

Exam Name = TS: Microsoft(R) Office PerformancePoint™ Server 2007, Applications
Exam Beta Number = 71-556
Prometric  Promo Code = TSPPS

Registration Information

You must register at least 24 hours prior to taking the exam.
Please use the following promotional code when registering for the exam:
Receiving this invitation does not guarantee you a seat in the beta; we recommend that you register immediately.

To register in North America, please call:

•             Prometric: (800) 755-EXAM (800-755-3926)

Outside the U.S./Canada, please contact:

•             Prometric: http://www.register.prometric.com/ClientInformation.asp

Topics covered in this exam include:

Configuring Dimensions

  • Configure properties of predefined dimensions
  • Configure the time dimension
  • Configure a member property
  • Populate dimensions and member sets
  • Update dimension members and member set, and member properties

Configuring Models

  • Create a PPS model
  • Configure the current period
  • Configure model and dimension usage within sites
  • Transfer data between models

Configuring Business Roles and Process Management

  • Configure business roles
  • Configure security for users
  • Configure assignments
  • Configure an approval structure

Developing Business Rules

  • Develop an assignment rule
  • Develop a definition rule
  • Develop an allocation rule
  • Configure template rules
  • Configure rule execution

Building Excel Add-In Forms and Reports

  • Build an Excel Add-In report
  • Configure an Excel Add-In report as a form
  • Configure report filters
  • Format an Excel Add-In report

Developing Dashboards

  • Create a report by using the Dashboard Designer
  • Add a data source connection
  • Add a new Web page report
  • Configure a KPI by using Dashboard Designer
  • Create a scorecard
  • Build a dashboard

Installing PerformancePoint Server

  • Install the monitoring and planning services
  • Configure a PerformancePoint application
  • Configure the PerformancePoint server
  • Assign users to administrator roles

Posted in PerformancePoint | Leave a Comment »

CompUSA going out of business?

Posted by denglishbi on December 6, 2007

I couldn’t believe it when they closed up shop in MN, but now it looks like they are going to be done for good in the next couple of months.  Just read this posting on Engadget this morning:

So we’ve heard enough banter about CompUSA being on its last legs and possibly shuttering completely after closing more than half the chain’s stores earlier this year. Well, chalk this up to rumor (in fact, rumor with prejudice if you must), but we’re privy to a high-level internal email competitor Best Buy recently circulated which claims to have it on authority (but not "verified") that CompUSA’s holiday revenue is half what it was last year (which, as commenter Hans points out, might have to do with those store closures), store-restocking shipments are not being scheduled past February, and additional liquidators are being called in to help manage closure of the 103 remaining stores. Sure, it could be a sneaky misinformation campaign — or it could be the truth. We gave CompUSA a ring, and their PR team has yet to respond to the claim; but for what it’s worth, if we weren’t already hearing this kind of thing elsewhere (or didn’t believe it plausible) we wouldn’t waste your time.


Posted in News and politics | Leave a Comment »