Sharepoint Lists & Reporting Services

 

I needed a way to generate a report from list items in SharePoint. In Reporting Services, there is no way to select a SP List as a data source --- unless you create an extension.
Well, I found some great code to start with here à http://www.teuntostring.net/blog/2005/09/reporting-over-sharepoint-lists-with.html
I was able to modify this code and get it to work.
Some modifications from what the web site tells you:
The configuration files you will need to edit are as follows:
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\rsreportserver.config
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\RSReportDesigner.config
The assembly seems to want to be in 4 locations (2 for design and 2 for runtime); I understand the 2 for design as it needs one for the IDE setup of the data source and one for the Preview (probably), but anyway once the DLL lived in these locations, life was great:
C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PublicAssemblies\
I can see this coming in handy across our MOSS 2007 projects….

RDA Corp - Business Intelligence and SQL Server

88 Million Copies, What can you say?

– There are 10,000 customers in the Customer Technical Preview (CTP) program for PerformancePoint Server, its new business intelligence offering.

88 Million Copies, What can you say?

If the purchase rate based on these customers is 5%, Microsoft is looking at $15-$30 million dollars worth of license sales for PerformancePoint.  Something tells me it will be much higher... now that Proclarity is in the mix for "free"

Microsoft won a patent battle in the spring against Timeline.  This may affect them in the future though, if Timeline decides to appeal.  Plus they're still going after Proclarity, now owned by Microsoft.

More info:

Federal Court Grants Partial Summary Judgment in Favor of Microsoft in Timeline Suit

06/4/2007

Timeline Inc. announced that the United States District Court for the Western District of Washington granted a partial summary judgment in Timeline Inc. vs. ProClarity Corporation. The import of the court's decision is that Timeline's Patent License Agreement (Patent Agreement) with Microsoft Corporation cannot be terminated, and that Timeline's August 2006 termination of the license based on Microsoft's breach of it was therefore ineffective. The court held that Timeline's remedy for any alleged breaches would be to seek damages. Consequently, the court dismissed claims of patent infringement against Microsoft. The federal court also then declined to exercise jurisdiction over state law claims between Timeline and Microsoft, including Timeline's claims for breach of contract, and dismissed them without prejudice to refilling of the claims in state court. This allows Timeline to pursue its claims of material breaches of the Patent Agreement in Washington State courts and to seek damages for breach rather than damages for patent infringement. Timeline had also claimed damages against Microsoft for breach of a separate agreement, under which Timeline wrote the Microsoft Small Business Financial Manager (SBFM Agreement). This cause of action was also dismissed without prejudice to refilling in state court by the federal court's ruling. The ruling does not affect Timeline's claims in the same suit for patent infringement against ProClarity Corporation, which is now a subsidiary of Microsoft Corporation. Those claims are set for trial in October 2007.

Artful Blog

 Useful tip...

One simple way to connect to Terminal Server/Services is to run following from the command line:

mstsc -v:MyServer /F -console

Alteratively, you can install Windows Server 2003 Administration Pack and use Remote Desktops (note s at the end - this is different from Remote Desktop (no s) from Windows XP).

Artful Blog

The Microsoft PerformancePoint Team Blog

 

The PerformancePoint Planning & Architecture Guide is now complete and live!

The PerformancePoint Planning & Architecture Guide is now complete and live!   Updates have also been make to the Deployment Guide and are now reflected out on TechNet.  The next refresh of the documentation will be in November.  http://technet.microsoft.com/en-us/library/bb794633.aspx

To recap, the roadmap to the PerformancePoint documentation is as follows:

• On the base install:  All context-sensitive help and base product documentation including conceptual information and procedures. Also contains links to Office Online, TechNet, and MSDN.

• On Office Online:  Links to README and other useful information
  • http://office.microsoft.com/en-us/performancepoint/FX102380591033.aspx
• On TechNet: For the IT pro - Deployment Guide, Operations Guide, Whitepapers, and Planning & Architecture Guide
  • http://technet.microsoft.com/office/performancepoint/default.aspx  or http://technet.microsoft.com/en-us/library/bb794633.aspx
• On MSDN: For the Developer - Planning Server, Business rules development guide and Monitoring SDK
  • http://msdn2.microsoft.com/en-us/office/bb660518.aspx

The Microsoft PerformancePoint Team Blog

Staging data into PerformancePoint Planning

 The documentation is intentionally vague on this subject.  We have implemented staging from our relational data sources (Accounting, Timesheet, Project Tracking) using SQL Server Integration Services. (SSIS)

One simple way for loading fact data is to create an SSIS Package with a Data Flow Task.  The DFT contains an OLEDB Source pointed to your data store & OLEDB Destination pointed to the fact label table within PerformancePoint's staging database for your application.  The procedure bsp_DI_CreateLabelTableForMeasureGroup is executed using a SQL Script Task after the DFT.

Here is a posting from the newsgroups that may help more. 

Hi Kurinji,
The ETL process to the Staging DB is such a custom process that
detailed steps can't really be given.  What you will have to do is to
create your dimensions, hierarchies, and models in the Business
Modeler, and then synchronize them all to the Staging Area.  Pay
special mind to the built-in dimensions, because they may have
properties you have to define, such as Account and the AccountType.
At any rate, what you'll need to do is load the following columns for
each dimension:
MemberId = NULL (This will be changed when it gets loaded to App)
Label = Your choice (This must be a unique identifier.  Be careful,
however, as the Label is used as an end-user identifier as well.
Make sure that it's both user-friendly and unique.)
Name = Your choice (Supposed to be the friendly name, so make it that
way)
Description = Your choice
SequenceNumber = NULL (PPS will take care of this)
SourceMemberId = The key of the source record (Use this to refer back
to your source table.  Usually you map the primary key to this.)
OwnerID = 0 (Tells PPS Admin owns it)
CreateDatetime = getdate()
ChangeDatetime = getdate() (Both of these are fairly
self-explanatory.)
LoadingControlID = NULL (PPS will take care of this)
BizSystemFlag = 200 (Needs to be 200 so PPS knows to add it. 300 is
update, and 400 is delete. 100 is that it's already in the database,
0 is do nothing with it and 900+ is an error code.)
BizSystemErrorDetails = NULL (PPS will flip this to the column names
where an error occurred if on did in fact occur.)
You may have a property you need to update if you've added any
dimension properties or you're using a built-in dimension.  If you're
using a built-in dimension, what you'll need to do is make a
cross-reference to the  corresponding AG_ table for its MemberId.
For example, if you wanted an Account to be an asset, for the
AccountTypeMemberId column in D_Account in Staging, you'd use (SELECT
MemberId FROM AG_AccountType WHERE Label = 'Asset').
Side note: All dimension tables are labeled D_, while measure tables
are labeled MG_.  Hierarchy tables are labeled H_.  Check the
documentation for specific details of these tables.
Also note that you should make heavy use of SSIS and the PPSCmd.exe
utility in your ETL process.  This way, you'll be able to make a
repeatable and scheduled process to do the ETL.  For PPSCmd.exe
documentation, look in the Business Modeler documentation.
As for MeasureGroup tables, you'll need to use the MemberIds of your
Dimension tables, so make sure that you load your Dimension tables
into the App DB BEFORE staging the fact data.
Your process in SSIS should look roughly like:
Sync Dimensions->Sync Models->Stage Dimensions->Load Dimensions->Stage
Hierarchies->Load Dimensions->Stage Fact->Load Fact->Deploy Model
This way, you'll have all the right IDs available when you need them.
Note that loading a dimension also loads all of its hierarchies.
Hopefully this is helpful!
Cheers,
Eric
Kurinji wrote:
> Hi all,
> Am new to PPS planning area. I would like to know the steps to
> load the staging database from the relational database(SQL Server)
> which i already have. In the documentaion, i read its a manual
> process to load the staging DB, but nowhere am able to see that
> manual process steps. Can somebody help me in setting up the staging
> db and loading it with the Relational database.
> Thx

Microsoft BI - Get the new resource kit

 

Explore the products and capabilities of Microsoft BI in the newly updated BI Resource Kit – now featuring the latest information on Microsoft Office PerformancePoint Server 2007. Discover the rich business potential of Microsoft BI through hands-on labs and demos, whitepapers, Web seminars and a comprehensive collection of technical resources when you request your complimentary BI Resource Kit. Learn how your organization can benefit from this complete, integrated BI solution from Microsoft that now includes the comprehensive performance management capabilities of PerformancePoint Server 2007. Information about the 2007 Microsoft Office System and SQL Server 2005 is also included. Quantities are limited, so register to get your BI Resource Kit now!

Microsoft BI

Charts And Graphs: Modern Solutions | Developer's Toolbox | Smashing Magazine

Everything you wanted to know about web-enabled charts and graphs for data visualization. 

Charts are supposed to visualize data in order to give a more profound understanding of the nature of a given problem or recent developments. Whatever type of data presentation you prefer (pie charts, bubble charts, bar graphs, network diagrams etc.), you can create charts in graphic editors manually or use special desktop-software instead. In both cases you have a major problem: once you’d like to update an old chart, or create a new one, you have to run the application and create new images over and over again. That’s not flexible. That’s also not usable — e.g. if you’d like to update your chart live.

Charts And Graphs: Modern Solutions | Developer's Toolbox | Smashing Magazine

Dan on eScience & Technical Computing @ Microsoft - Web Data Add-in for Excel 2007

 

The Excel 2007 Web Data Add-In makes it easy to use a Web page as a data source in Excel. The add-in plugs into Excel 2007 seamlessly, its entry point located on the Data Tab under the From Web option. The system extracts data by learning from a user’s selection of data they wish to capture into Excel. The more selections, the more the system is trained. An example scenario: You wish to import and track data from MSN's weather page. Visit the site using the tool, enter Data Capture mode, and select a row or two of data from the table. Then click Select Similar, and the system will find similar data based on your previous selections. You then can click Import and leverage Excel's rich data-editing capabilities, including the Refresh command, which will revisit the Web page and extract potentially new, updated data.

Dan on eScience & Technical Computing @ Microsoft

Sharepoint Integration & Navigation Links

I just noticed that the new protocol for accessing PerformancePoint doesn't work in a Sharepoint Navigation URL (fails validation)

The PerformancePoint Server protocol handler enables PerformancePoint Server users to open planning assignments through hyperlinks. This protocol handler is automatically installed on every client computer where the PerformancePoint Add-in for Excel is installed.
The PerformancePoint Server protocol handler is automatically registered when PerformancePoint Add-in for Excel is installed and appropriate registry keys are created.

PerformancePoint:http://server:46788&application=myapp&assignment=4119

One question I have is why didn't they just link to an Excel Worksheet with some parameters, or a web page that exports a worksheet that fires the PerformancePoint add-in, instead of adding the complexities of a protocol handler?

Here's more info on the protocol handler:

http://technet.microsoft.com/en-us/library/bb660547.aspx

Export XML data - Excel - Microsoft Office Online

Some solutions may require exporting XML feeds from Excel spreadsheets to feed SQL databases.  One alternative is to use linked servers, however this may not be available in some environments.

Here is some info on mapping XML to Excel 2007.

After you have imported XML data, mapped the data to cells in your worksheet, and made changes to the data, you often want to export or save the data to an XML file. There are two ways to export XML data from a Microsoft Office Excel 2007 worksheet:

Export XML data - Excel - Microsoft Office Online

Pranav ... Blogging : Command-line, the old beast ..

Command line switches for Office apps. 

Here is a small list about the command line switches, although it might not be a complete list, but i'll update my blog after collecting the remaining pieces.

Office 2007 : http://office.microsoft.com/en-us/word/HP101640101033.aspx

Office 2000 -2003 : http://support.microsoft.com/kb/210565

/a & /safe switch: http://support.microsoft.com/default.aspx?scid=KB;EN-US;813589

Excel: http://office.microsoft.com/en-us/excel/HA101580301033.aspx

Visio: http://support.microsoft.com/kb/314392

Powerpoint: http://office.microsoft.com/en-us/powerpoint/HA101538891033.aspx

Playlist files .. (this is something that I never knew about, even it's existence!): http://support.microsoft.com/kb/176678

Source: Pranav ... Blogging : Command-line, the old beast ..

Bill Morein's Weblog : Visio Active Directory Topology Diagrammer

 

One of the most common requests I get from IT users of Visio is for a way to draw out an Active Directory topology map. Visio has Active Directory shapes, but until now it didn't have the ability to actually go out and create a diagram from AD. We've put up a free download that does this -- the Microsoft Visio Active Directory Topology Diagrammer is now available. This runs as a separate application and is installed in the Start menu. Here is a screenshot of the UI:

Source: Bill Morein's Weblog : Visio Active Directory Topology Diagrammer

How do I export data with an outbound rule?

 

How do I export data with an outbound rule?

Outbound rule sets make it possible for you publish Planning Server data to specified external data stores called Data Destinations. Modelers can create sets of customized outbound rules to fit a particular business scenario.

This procedure provides guidelines that can help a modeler develop outbound rules and rule sets.

What you can do when you finish

When you create and run an outbound rule set, your Planning Server data is available to an extended business user community. Your data can be used by applications other than the Planning Server tools. For example, at the end of the budget cycle, a customer might want extract new budget numbers and update an enterprise plan. Alternatively, a customer might want to create a data catalog or snapshot of results from Planning Business Modeler.

How do I export data with an outbound rule?

PerformancePoint Server Developers Site

 

PerformancePoint Monitoring SDK

Use the PerformancePoint Monitoring SDK to help customize and integrate your own dashboard object extensions such as custom reports and data sources, wizards, and user interface extensions. The 2007 release of the SDK includes overview topics, code examples, and updated references for programming with PerformancePoint Monitoring Server.

PerformancePoint Planning Business Rules Development Guide
Get information about how to use PerformancePoint Planning to develop business rules that perform complex planning, budgeting, and forecasting, or financial operations such as consolidation. Develop custom rules in the PerformancePoint Expression Language (PEL), SQL, or MDX, or take advantage of the many templates that you can use for business rules.

PerformancePoint Server

The Microsoft PerformancePoint Team Blog : Launching ProClarity Professional from a PAS View within a PerformancePoint Dashboard

 

Here is an example URL with the "uiConfig" option set:

http://MyPasServer/pas/en/src/proclarity.asp?uiConfig=tb;ht;&book={6253ECA7-E784-403F-94F0-438B9722D219}&page={E220649B-2344-409E-835C-1ABF6939A06F}&folder=root&LibID={93E72F1B-806C-49AC-9BDC-256BFFA23D51}

The Microsoft PerformancePoint Team Blog : Launching ProClarity Professional from a PAS View within a PerformancePoint Dashboard

Bill Baer : Deploying ProClarity Viewer 6.3 for SharePoint

 

Deploying ProClarity Viewer 6.3 for SharePoint

I was recently asked to deploy the ProClarity Viewer 6.3 for SharePoint Web Part - after downloading the package I realized that it was not offered in a deployable package, but rather a compressed archive of the .dwp, assembly, and resource files.  The challenge became offering a user experience similar to the previous ProClarity Viewer version so I decided to package the Web Part into a reusable solution.  For those interested or looking to create a deployable Web Part package I've documented the steps below:

Bill Baer : Deploying ProClarity Viewer 6.3 for SharePoint

UNPIVOT

For certain source systems like AccPac, you may need to convert your column-based list of periods or months into a row-based list that is friendlier for an OLAP data model with a time dimension.

The SQL 2000 solution would be to use a case statement or possibly a custom pivot function, or maybe some creative use of the isnull function.

Here is an example query for this in SQL 2005: 

SELECT
[ACCTID]
,[FSCSYR]
,[FSCSDSG]
,[FSCSCURN]
,[CURNTYPE]
,[AUDTDATE]
,[AUDTTIME]
,[AUDTUSER]
,[AUDTORG]
,[SWRVL]
,[CODERVL]
,[SCURNDEC]
,[OPENBAL]
,[PERIOD], Balance
FROM
(
SELECT [ACCTID]
,[FSCSYR]
,[FSCSDSG]
,[FSCSCURN]
,[CURNTYPE]
,[AUDTDATE]
,[AUDTTIME]
,[AUDTUSER]
,[AUDTORG]
,[SWRVL]
,[CODERVL]
,[SCURNDEC]
,[OPENBAL]
,[NETPERD1] [Jan]
,[NETPERD2] [Feb]
,[NETPERD3] [Mar]
,[NETPERD4] [Apr]
,[NETPERD5] [May]
,[NETPERD6] [Jun]
,[NETPERD7] [Jul]
,[NETPERD8] [Aug]
,[NETPERD9] [Sep]
,[NETPERD10] [Oct]
,[NETPERD11] [Nov]
,[NETPERD12] [Dec]
,[NETPERD13]
,[NETPERD14]
,[NETPERD15]
,[ACTIVITYSW]
FROM [GLAFS]
WHERE activitysw = 1 ) Balance

UNPIVOT (
Balance
FOR [Period]
IN ( [Jan], [Feb], [Mar], [Apr],
[May], [Jun], [Jul], [Aug],
[Sep], [Oct], [Nov], [Dec])
) AS unpvt
WHERE BALANCE <> 0

Upgrading from CTP4 to RTM PerformancePoint 2007

A good posting from the newsgroups on upgrading PerformancePoint to RTM.  The question was "Do you have to uninstall CTP4 to install RTM?"

  Yes, that is the upgrade path for the "Planning Server".  Note that you do *NOT* want to drop the Databases after you uninstall, as you will be re-using them.  When you run through the RTM Configuration Manager you would choose a "Distributed" installation and Un-Check the Database Install option, this will allow you to "Attach" to the DB's... if the option is selected PPS will attempt to create the DB's for you, which is not what you want, and, since yours already exist, would fail.

  Installing RTM will "upgrade" the Planning Server's System and Service Databases automatically for you, if you have any Applications, you will need to upgrade those after the fact using the PPSCMD, (which is installed with the Business Modeler), "Upgrade" option, (you can type PPSCMD help Upgrade" on the command line for more info... by default, PPSCMD is installed here: "%ProgramFiles%\Microsoft Office PerformancePoint Server\3.0\BizModeler".

  The entire process should be in the Deployment or Operations guide, (which is up on the TechNet and Office Online PPS sites).  Note that after this has been done, you "may" need to go in and re-grant your SI Account some permissions... it depends on how your present system is setup.  The reason I say you "may" need to is because a "Distributed" install is considered by PPS to be a Production Environment installation, and as such certain permissions are not granted to the SI Account during Configuration of a Distributed Environment for security purposes... much the same way that after installing SQL Server 2005 your Remote Connections are not enabled by default. :-)  The Documentation should discuss this further. :-)

loose wire blog: A Directory of Visualizing Tools

My favourite visualization is the Treemap, which is useful in many applications.  (including freeing up space on your harddrive.) 

A treemap “is a bunch of squares, arranged to form a mosaic. The size and color of each block mean something”. It’s probably easier to show it than to explain it:

(from RoomforMilk, see below)

Source: loose wire blog: A Directory of Visualizing Tools

Saveen Reddy's blog - Automating Visio 2007

 I hope this is a misprint otherwise I'm using an older version of Visio.

Saveen has some good links on colour and automation of Visio.

Automating Visio 2007 with AutoVisio - now on CodePlex

As I've been exploring automating Visio 2008 through C#, I've been placing all that code in a library called AutoVisio. It's not well-commented and is built on the beta 2 version of Visual Studio 2008, but I thought now would be a good time to let others use it.

Link to project site on codeplex: http://www.codeplex.com/autovisio

Source: Saveen Reddy's blog

Visio Guy » Blog Archive » Visio 2007 Developer Resources #1

More info on Visio from Visio Guy's blog.   The release is out so this is a bit stale now...

Well the buzz about Visio 2007 is increasing daily, and all the hard-core Visio fans are already working on solutions for the next version of Visio. If you’re itching to jump on the bandwagon, I’ve collected a set of links to point you towards as many Visio 2007 developer-related links as possible!

Enjoy!

The Beta

Download Visio 2007 Beta 2
You won’t get very far in your Visio 2007 development without an installation of the latest beta!

Books

New Book: Visualizing Information with Microsoft® Visio 2007
David J. Parker over at bVisual has a new book coming out soon that will help you to harness the power of the data-integration and visualization features coming in the next version of Visio.

Articles

What’s New for Developers in Visio 2007 (Part 1 of 2)
What’s New for Developers in Visio 2007 (Part 2 of 2)
A great breakdown of the new ShapeSheet and Automation features for those of us who roll our own Visio, as well as info on how to use the new data features in Visio 2007.

Designing Shapes for Use with Themes in Visio 2007
Expanding on the color schemes feature available in earlier versions of Visio, Theming in Visio 2007 offers a sophisticated way to customize the colors and styles of your Visio diagrams. The shapes that come with Visio 2007 rely heavily on Theming, and the results are quite impressive. This article describes how you can design your own shapes to function with Theming and introduces several new ShapeSheet functions that help you get the job done.

Integrating Visio 2007 and Access 2007
An article from Visimation for MSDN which describes how to generate a Visio diagram from data stored in an Access database. Lots of code samples in C#.

Integrating Visio 2007 and Excel 2007
Another article from Visimation for MSDN which describes how to link data in Visio to Excel using the Visio Data Selector and generate a BOM (Bill of Materials) Code samples in C#.

Building Custom Data Graphics for Visio 2007
A nice 8-or-so-page article on developing your own data-widgets and getting them to work with Visio 2007’s cool data-integration features!

Web Logs & Portals

Visio 2007 on MSDN
A good place to start. At the top, you’ve got on-line SDK Documentation and Technical articles. Once you start clicking, you’ll probably never escape!

Visio Developer Portal on MSDN
Part of Microsoft Office Developer Center this site contains articles, whitepapers, code samples, downloads and links to other developer resources, including a list of web logs from Visio’s own staff!

Visio Insights Weblog
This web log is from the folks on the Visio team and contains some very deep posts on developing for Visio. Although it’s not just Visio-2007-specific, it’s nonetheless choc-full of information that’s vital to improving the quality of your Visio solutions.

Bill Morein’s Weblog
An MS Visio team member, Bill’s blog is all about Visio and Data Visualization, and many of his articles pertain to Visio 2007.

Eric Rockey’s Weblog
Another MS Visio team member, the title of Eric’s blog: “What’s new in Visio 12″ should be a grand give-away.

Downloads

Visio 2007 SDK (Software Development Kit) Beta 2
The latest version of the Visio SDK offers a great help file, a Code Librarian database full of Visio-related snippets, several VB.NET, C#, VB6 and C++ automation examples, the Visio 2003 Viewer, and .NET templates that help you to quickly create applications and COM Add-ins for Visio.

Office 2007 Developer Map Poster
Cool Office 2007 Developer’s poster in PDF format (thanks to Bill Morein’s weblog)

Related Posts:

• Data Graphics Resources
• Integrating Visio 2007 with Microsoft Technologies
• Book Review: Visualizing Information with Microsoft Office Visio 2007
• Visio 2007 Demo Videos
• Visio & AutoCAD

Source: Visio Guy » Blog Archive » Visio 2007 Developer Resources #1

Visualizing Information with Microsoft Visio 2007

One of the components to your company's PerformancePoint solution could be data-driven Visio 2007 diagrams.

This book should help with that, or at least get you certified in Visio 2007 Application Development (Exam 70-545)

http://www.visualizinginformation.com/

Link to Visualizing Information - Home

PerformancePoint Server

 

PerformancePoint Planning Business Rules Development Guide
Get information about how to use PerformancePoint Planning to develop business rules that perform complex planning, budgeting, and forecasting, or financial operations such as consolidation. Develop custom rules in the PerformancePoint Expression Language (PEL), SQL, or MDX, or take advantage of the many templates that you can use for business rules.

Source: PerformancePoint Server

Notes from the Newsgroups on PerformancePoint Release

• Evaluation edition of the product will be available right after we RTM
(9/25) on Download Center
• Enterprise edition of the product will be available to MSDN subscribers
very early October
• Enterprise edition of the product will be commercially, widely available
on Volume Licensing very early November (11/1 as mentioned below)

Andrew Fryer's Blog - The Cockpit

 

Flying as I mentioned in my last post is a difficult business,  If you have ever seen the flight deck of a modern aircraft it's a daunting array of instruments and switches with no obvious sense to them.  The screen grab above is just from a little light aircraft and even this looks confusing.  However an experienced pilot will concentrate on just six instruments and I have magnified and numbered these :

1. How fast you are going (airspeed indicator)
2. The orientaton of the plane in the sky (artificial horizon)
3. How high you are (altimeter)
4. How the plane is turning (bank indicator). This is how a pilot can turn the plane tightly while you don't notice and your coffee doesn't end up over the next passenger.
5. Which direction you are going (compass)
6. Whether you are going up or down (vertical speed indicator)

Andrew Fryer's Blog

Personalized Excel Add-In Config

If you are looking for location where the Excel Add-In stores it's configuration info, it is here, according to a posting

in the newsgroups.

This could be useful for preconfiguring connection strings for large-scale deployments.

C:\Documents and Settings\USERNAME\Local Settings\Application
Data\IsolatedStorage\czdaxqqg.gkr\wtgggmri.bmi\Publisher.wobgdc0ahsmraeplqqlxnqg12riflrpx\AssemFiles

Blog: Shawn Rogers - PerformancePoint 2007 launch

PerformancePoint 2007 was launched last week.

KABOOM!!! Yep that sound you heard at 10:30 AM MST was the change in the business intelligence landscape that many people have been waiting for, Jeff Raikes, President of the Microsoft Business Division, announced the launch of Microsoft Office PerformancePoint Server 2007, Microsoft’s new performance management application during a press conference. It's going to be an interesting ride the next couple months as Microsoft attacks the BI space with its full line up of solutions.

Source: Blog: Shawn Rogers

Russell Christopher's Semi-Useful BI Musings

 

Mosha just announced the availability of a new tool he wrote called MDX Studio:

MDX Studio is tool which helps users of Microsoft Analysis Services to analyze complex MDX expressions, monitor performance characteristics of MDX queries and get insight into how MDX interacts with other UDM features such as attribute relationships. MDX Studio provides unique visualization representation of MDX expression and allows the user to interact with different stages of MDX execution.

 The tool itself can be downloaded here.

Source: Russell Christopher's Semi-Useful BI Musings

Office Rocker! : Clean up with MOPPS

Sounds like PerformancePoint is up on MSDN today.  What about connect users?  No notifications? 

does that mean PPS is already on the Volume Licensing pricelist?

not yet - it's available for download off msdn from tomorrow and on volume licensing from the 1st of November

Source: Office Rocker! : Clean up with MOPPS

Russell Christopher's Semi-Useful BI Musings : Dell and Microsoft engineer BI offering

 

Dell and Microsoft engineer BI offering

Today at SQL PASS, Dell and Microsoft announced a new set of Business Intelligence and Data Warehousing solutions. They include a stack of server hardware, storage arrays, operating system, database and analytics software, management and reporting tools. The entire solution is based on the hardware and software many customers are using right now.

What is really cool about these offerings is they answer the oft-asked question: “what sort of hardware do I need for my DW/BI project?”: Three reference configurations have been created: one terabyte, two terabyte and four terabyte systems for data warehousing databases using SQL Server 2005. Dell and Microsoft worked together to execute various tests and analyze performance results of a sample business intelligence and data warehousing system under load. The testing was designed to validate the platforms for business intelligence and data warehousing, to provide the stability and performance of the hardware systems, and to define the reference configurations I mentioned earlier.

From what I understand, we’ll essentially be including all the goodness in the SQL 2005 BI stack + ProClarity. Now that PerformancePoint 2007 is upon us, the offering will be reconfigured to include PPS at some point in the near future.

More official details should be available at www.dell.com/bi http://www.dell.com/sqlbi a little later today. It doesn't seem like the site is up yet, however.

Update:

Here is a link to the actual 1/2/4 TB reference configuration to see how they did it:

http://www.dell.com/content/topics/global.aspx/sitelets/solutions/software/db/microsoft_sql_2005_pr?c=us&cs=555&l=en&s=biz

 ...and another to an article on this event:

http://informationweek.com/news/showArticle.jhtml?articleID=201808018

Source: Russell Christopher's Semi-Useful BI Musings : Dell and Microsoft engineer BI offering

The Microsoft PerformancePoint Team Blog : Creating a Custom Report View Plug-in for PerformancePoint Server 2007 ~ Code Sample

 

You can use the code sample a model to create your own custom Report View plug-ins for Microsoft Office PerformancePoint Server 2007. There is more documentation on this in the PerformancePoint Server SDK; see "PPSMSDK.chm" extractable from https://connect.microsoft.com/Downloads/DownloadDetails.aspx?SiteID=181&DownloadID=8048.

The Microsoft PerformancePoint Team Blog : Creating a Custom Report View Plug-in for PerformancePoint Server 2007 ~ Code Sample

Establish. Execute. Evolve.™: On Two Rational Guides for PerformancePoint Server 2007

Two new books on the way from Adrian Downes. 

After The Rational Guide to Microsoft Business Scorecard Manager 2005 was released many people asked "so when's the PPS book coming?". Nick Barclay and I remained decidedly quiet about the idea of another book. It was, in fact, always our intention to write a PerformancePoint book, with the aim of publishing as close to RTM as possible.
Early on in the planning process we found that a single Rational Guide just didn't contain enough pages to cover the product adequately; to remedy this we wrote two books.
We are pleased to announce that the following books are soon to be published via Rational Press:

• The Rational Guide to Monitoring and Analyzing with Microsoft Office to PerformancePoint Server 2007 (available November 2007)
• The Rational Guide to Planning with Microsoft Office to PerformancePoint Server 2007 (available December 2007)

Based on the feedback from readers and bloggers, Nick and I have found that the interest in learning the product is massive. Our titles are designed specifically to help you get up to speed quickly with the Monitor - Analyze - Plan feature areas of PerformancePoint.

- Adrian Downes

Establish. Execute. Evolve.™: On Two Rational Guides for PerformancePoint Server 2007

PerformancePoint Server » Blog Archive » What are the predefined job templates available?

This site contains a great walkthrough of the various options in PerformancePoint.

Jobs are the automated tasks that your system would perform without any user interaction. In most cases, the jobs are created within a cycle, but they can also exit independently. In the Planning Business Modeler, many job templates are available that you can use to define jobs or create custom jobs. Using these templates, you can speed up your work of creating assignment, job definition and completion.

The predefined job templates available are:

Calculations rule jobs: Users with the modeler role create job templates for the Business users so that they can run a specific job. This is done since Business Users do not have the permission to run rules or rule sets. Now Business Users can make use of this created job template to run specific rules and rule set. 

Consolidation jobs: This job template is designed to convert currency by using the currency conversion model, eliminate holding company investments and subsidiary equity, purge intercompany balances and also calculate financial data.

Data Export jobs: This job template is used to export data to an external database. This job can only be started from PerformancePoint Add-in for Excel. 

Data Movement Jobs: This job template is used to move data within a model or in between models. This can also be started from the PerformancePoint Add-in for Excel. 

Opening Balance jobs: This template is used to calculate the rules of the opening balance using one of the Chart Logic rules. 

Data Load jobs: This is used to synchronize data with staging database, or loaded to application database. 

Currency translation jobs: This is used to perform the currency translation job that is based on exchange rate data. 

Intercompany reconciliation job: This is used to convert currency from source currency to the reporting currency. It also adjusts data in the defined rule or rule sets. 

Shares Calculations jobs: This is used to run any particular rule or rule set.

PerformancePoint Server » Blog Archive » What are the predefined job templates available?

Microsoft SharePoint Products and Technologies Team Blog : Customizable Everyday Productivity Education (EPE) content developed by Microsoft IT for end user training

 

Customizable Everyday Productivity Education (EPE) content developed by Microsoft IT for end user training

These simple, easy-to-use customizable job aids were created by Microsoft's internal IT group to help employees get started with learning more about using a technology or product effectively, selecting the right product or service, or reviewing best practices. The content for Windows Vista and the 2007 Office System were published a couple of months ago, and I (and likely most of you) somehow missed it. The EPE home page is at http://technet.microsoft.com/en-us/library/bb687781.aspx, but for your convenience, I've listed the SharePoint specific content below.

Microsoft SharePoint Products and Technologies Team Blog : Customizable Everyday Productivity Education (EPE) content developed by Microsoft IT for end user training

Mauro Cardarelli : BI Skills are in Demand

Comments from Mauro on PerformancePoint. 

I've been doing my PPS (PerformancePoint Server) homework for months in anticipation of launch.  This is the most powerful, most complicated tool I've seen hit the Microsoft market in some time.  It requires very different skills than the traditional .NET developer (or even MOSS implementer).  In the coming months, I'll try to balance my MOSS and PPS postings to share my stories...

Mauro Cardarelli : BI Skills are in Demand

Nick Mayhew - Official Microsoft Worldwide Team blogs

Good list of team blogs below. Except for Performance Point.

Some supporting items that you may want to look for in http://www.microsoft.com/downloads for your PerformancePoint project are the Reporting Pack for Integration Services & IIS6 Reporting Pack.  These Reporting Services report packs would be useful for the administrator of a PerformancePoint solution.

One of the Vista team in the US went to the trouble of creating a list of Microsoft team blogs - meaning those in the product groups in the US building our products. If interesting then please go back to the list as I am sure this will get updated regularly.

Source page: http://windowsvistablog.com/blogs/windowsexperience/pages/microsoft-blogs.aspx

Listing as at Sept 17th 2007:

"Listed below are official team blogs from the product teams at Microsoft which are categorized by specific product groups.

Click here to access the Microsoft Feeds Directory where you can also choose to search for a specific blog you are looking for that isn't listed below.

Windows:
Windows Vista Team Blog
Internet Explorer Team Blog
Windows Mobile Team Blog
Tablet PC Team Blog
Microsoft Update Team Blog
Microsoft RSS Blog
Microsoft Photography and Video Blog (PIX Blog)
XPS Team Blog
Windows Home Server Team Blog
Windows SideShow Team Blog
Windows Ultimate Blog
Windows Core Networking Blog
Windows Vista Security Blog
Shell: Revealed Blog (Windows Shell)
Network Access Protection Blog
Windows Sidebar Team Blog
Windows Genuine Advantage Blog
http://blogs.msdn.com/speech/ (Microsoft Speech Blog)

Windows Live:
Windows Live Wire (Windows Live Team Blog)
Windows Live Messenger Team Blog
Windows Live Mail Team Blog
Windows Live Photo Gallery Team Blog (directs to PIX Blog)
Windows Live Writer Team Blog
Windows Live Hotmail Team Blog
Windows Live OneCare Team Blog
Windows Live Spaces Team Blog
Windows Live E-Mail Support Blog (Support for Hotmail and Mail client)
Windows Live SkyDrive Team Blog
Windows Live ID Team Blog
Windows Live Alerts Team Blog
Windows Live Custom Domains Team Blog
Windows Live Safety Center Team Blog
Live.com Team Blog

Live Search:
Live Search Team Blog
Live Search Maps Team Blog (and Virtual Earth)
Live Search QnA Team Blog
Live Search Expo Team Blog

Windows Server:
Windows Server Team Blog
Windows Small Business Server (SBS) Blog
Microsoft SoftGrid Team Blog
Windows Virtualization Team Blog
Windows PowrShell Team Blog
Forefront Client Security Team Blog
Microsoft Office Communications Server Team Blog
Microsoft Office:
Microsoft Office Team Blogs at Microsoft Office Online
Microsoft Office Communicator Team Blog

Microsoft Channels:
Channel 8
Channel 9
Channel 10

Microsoft Expression & Silverlight:
Microsoft Expression Team Blog
Microsoft Silverlight Community Blogs

Microsoft for the Mac:
The Macintosh Business Unit Team Blog (Mac Mojo)

Gaming, Xbox, and Zune:
Gamerscore Blog
Zune Insider

Nick Mayhew - Reaching out to Microsoft partners in Australia

Tracking Formulas in Microsoft Excel

One of the first thing's you'll probably have to do once you get PerformancePoint Planning installed is try and reverse engineer some accountant's complex spreadsheet of formulas.  Mr. Excel has some invaluable tips and tricks like this one.

See all Formulas at Once
There is a grave accent on your keyboard. It is usually just below the F1 key. It looks like a backwards apostrophe. Hold down Ctrl and press the grave accent. You will see all formulas:

Tracking Formulas in Microsoft Excel

Create a Number Table [sql] [numbers] [udf] [monotonous]

When you're doing crazy things with data generation or math, you need a numbers table or function in Sql. 

This query generates a list of 10 new investment employees per department in the department table in Sql 2005, using the numbers function in the link.

select row_number() OVER ( ORDER BY Num.Number) +1000 as rownum, 
rtrim(ltrim(Dept.[Department Name])) + ' - Investment Employee ' + Convert(varchar(2),Num.Number) as EmployeeName
from dbo.NumberTable (1,10) Num
cross join d_department Dept
order by EmployeeName

Create a Number Table [sql] [numbers] [udf] [monotonous]

SQL Server Spotlight on Rob Zare

An interview with the Program Manager of SQL Server Analysis Services. 

SQL Server Spotlight on Rob Zare

How to Load PerformancePoint Dimension from CSV to Source Data View to App Database

 These steps will allow you to use a linked server to a directory to access CSV files generated by PerformancePoint.  These files can be hand-edited with notepad, and then loaded into PerformancePoint as a data view.  We can even create Data Destinations to create the CSV files.  Talk about convolution!

This could be useful when you want to abstract the loading of dimension data, or query CSV files from SQL Server, or use the data in multiple locations without an ETL process, or something else that you don't want to use direct CSV files for.  Please let me know what you decide to use it for....

1. Export your PerformancePoint dimensions using PPSCMD at a command prompt.

C:

MD \Data

"C:\Program Files\Microsoft Office PerformancePoint Server\3.0\BizModeler\ppscmd.exe" exportdimension /Server http://myserver:46787 /App myApp /Site MySite C:\Data

 

2. Run these steps in your Source Database.

(If PerformancePoint exposed an ODBC connection instead of a SQL Connection for the Data Source, we wouldn't need this step.)

EXEC sp_addlinkedserver txtsrv,'Jet 4.0','Microsoft.Jet.OLEDB.4.0','C:\Data',NULL,'Text'
EXECUTE sp_addlinkedsrvlogin 'txtsrv' , False, NULL, NULL, NULL
EXEC sp_tables_ex 'txtsrv'

sp_columns_ex txtsrv,'DimAccountAll Members#csv'

create view vDimAccountAllMembers as
select [] as Label, F4 as P_Parent, F5 as [Description], F6 as [Name], F7 as [AccountTypeMemberID]
from txtsrv...[DimAccountAll Members#csv]
where left([],1) not in ('!')
and [] not in ('Label')

3. Modify the CSV files to your needs.

4. Open the PerformancePoint Planning Administration site at: http://ppsite:46788/DataSources.aspx

Go to Data sources.

Add a Data Source under your model site.  Add the view we just created to the data source.

5. Open Business Modeler.  Click on Account Dimension.  Check out the dimension.  Click Load Members.  Select Load Members from Source.  Specify Source Dimension view that we just created, and map the fields.  Click Finish.

Voila!  A roundabout way of importing data and accessing CSV files directly from SQL Server, without ETL.

Adding Resource Links to the 2007 Office Fluent Ribbon Using COM Add-Ins

 

Summary: Walk through the process of creating an add-in that adds a custom tab to the existing Microsoft Office Fluent Ribbon in Excel 2007, PowerPoint 2007, and Word 2007. The tab contains controls that you use to access 2007 Office training resources. (18 printed pages)

Source: Adding Resource Links to the 2007 Office Fluent Ribbon Using COM Add-Ins

So you want to rebuild the PerformancePoint Add-In for Excel...

 

MSDN Virtual Lab: Building Custom Office Applications Using Microsoft Visual Studio Tools for Office (Part 3 of 4): Build an Excel Add-in with Ribbon and Custom Task Pane Integration

Source: MSDN Virtual Lab: Building Custom Office Applications Using Microsoft Visual Studio Tools for Office (Part 3 of 4): Build an Excel Add-in with Ribbon and Custom Task Pane Integration

Sharepoint SDK Updates

 

We are very proud to announce a major update to the MOSS and WSS Software Development Kit (SDK) downloads.  You can install them from the following locations:

· MOSS 2007 SDK 1.2.  Includes Conceptual and Class Library Reference documentation, Web Services documentation, and Developer Tools and Samples for MOSS and WSS.  See below for a detailed breakdown what’s new in this release.

http://www.microsoft.com/downloads/details.aspx?FamilyId=6D94E307-67D9-41AC-B2D6-0074D6286FA9&displaylang=en

· WSS 3.0 SDK 1.2.  Includes Conceptual and Class Library Reference documentation, Web Services documentation, and Developer Tools and Samples for WSS technology only.  See below for a detailed breakdown what’s new in this release.

http://www.microsoft.com/downloads/details.aspx?familyid=05E0DD12-8394-402B-8936-A07FE8AFAFFD&displaylang=en

New Tools Included with the MOSS SDK

We rounded out our tool set in this release to include developer tools and samples for the following areas of MOSS development.  New tools and samples are in bold.

· Business Data Catalog Samples and Utilities

o Microsoft Business Data Catalog Definition Editor

o Sample Pluggable SSO Provider

o WSHelloWorld Web Service

o WSOrders Web Service

o Excel Services User Defined Function Sample

o WSOrders Custom Proxy Sample

o Amazon Web Service Sample

o AdventureWorks Metadata Samples

o SAP Sample

· Document Management and Content Processing Samples

o Comment Scrub Document Converter

o Term Replacement Document Inspector

· Search Samples

o Sample Protocol Handler

o Custom Content Source

· Records Management and Policy Samples

o De-Duplication Router

o Document Integrity Verifier

o Records Center Web Service Console Application

o Search, Collect, and Hold Tool

o Sample Custom Barcode Generator

o IRM Document Protector

· Workflow Samples

o Custom Workflow Report Query Generator

o Custom Workflow Report XLSX Injector

o Visual Studio Workflow Templates

o Enterprise Content Management Workflow Activities

o List Item Activities

o Hello World Sequential Workflow

o State Based Approval Workflow

o Modification Workflow

o Replication and Contact Selector Workflow

o Intersystem Purchase Order

o Confidential Approval Workflow

o Group Approval Workflow

o Approval Workflow Sample

o Multi-Stage Workflow

o Server-side Collect Signatures Workflow

Full details can be found in the Welcome Guide of the SDK, accessible through the Start Menu.

SDK Documentation Update Details

We now have close to 90% completion of all Type descriptions in the SDK Class Library Reference. Nearly all of the remaining blanks in the API reference are either Internal Namespaces and Classes or low priority APIs, and we are working to get those tagged appropriately for a future release.

Updates to the MOSS SDK conceptual topics include:

· How to: Customize RSS for the Content Query Web Part (new)

· How to: Create a Minimal Master Page (revised)

· Provisioning Portal Sites (revised)

· Portal Site Template File (revised)

· Portal (Portal Site Template) (revised)

· Webs (Portal Site Template) (revised)

· Web (Portal Site Template) (revised)

· How to Create a Web Service Connection by using the Business Data Catalog Definition Editor (new)

· How to Create a Database Connection by using the Business Data Catalog Definition Editor (new)

Updates to the MOSS SDK Class Library and Web Service Reference include:

· Microsoft.Office.Workflow.ISoapServerWorkflow

· Microsoft.Office.Workflow.IWorkflow

· Microsoft.Office.Workflow.Workflow

· Microsoft.Office.RecordsManagement.PolicyFeatures.IBarCodeGenerator

· Microsoft.Office.RecordsManagement.InformationPolicy.IPolicyFeatures

· Workflow Web service

Source: Document Management Systems

FREE SHAREPOINT WEB SITE : Online Project Management, Web Based Collaboration, Data Storage

 

Free SharePoint Workspace

The solution to your project, intranet, and online collaboration needs.

Need an office tool that's easy to use, secure, and flexible? SharePoint gives you a one-stop shop for your business needs. Best of all, you don't pay for hardware or software, or a team of developers to maintain a company intranet.

TRY IT FREE

• No Time Limit
• 5 MB Disk Space
• 5 Users
• No credit Card Required

Source: FREE SHAREPOINT WEB SITE : Online Project Management, Web Based Collaboration, Data Storage

SharePoint Query Web Service Test Tool - View Release

 

SharePoint Query Web Service Test Tool Release

Release name is required

This is the SharePoint Query Web Service Test Tool. You can use it with the WSS 3.0 and MOSS 2007 search web services. It allows you to change property flags, generate the request XML, send the string to the web service and see the results in the UI. The accessible methods include Query, QueryEx, GetSearchMetaData (available for MOSS only) and GetPortalSearchInfo (available for MOSS only).

Source: SharePoint Query Web Service Test Tool - View Release

Sharepoint Camp in Toronto - October 20, 2007

 

Mark your calendar! The first ever SharePoint Camp will be in Toronto, and registration will be absolutely free.

Toronto SharePoint Camp, Manulife Centre
200 Bloor Street East
Saturday, October 20

The event will be hosted by the Toronto SharePoint User Group with a supporting cast of many fantastic volunteers and guest speakers.

We're planning three tracks: Developer, Administrator, and Champion/Architect. There will be 15-18 presentations total in 3 to 5 theatres. The space should comfortably accomodate 200 to 300 attendees. We'll follow the CodeCamp manifesto: free, volunteer-run, brand-agnostic, less talk, more rock. 

Call for speakers: We need speakers! All submissions must be delivered by September 21, 2007. Presentations and demo code is due by October 12, 2007. Read more about how you can contribute and apply by downloading the Call For Speakers today!

Call for Volunteers: We need volunteers! If you'd like to help out, mark your calendar for October 17 and 20 today. Then, watch this blog for link to the sign-up sheet as soon as the site goes live. On Wednesday, October 17th at 6:pm we'll have a pizza meeting for about an hour at 2 Bloor Street West (Nexient) to get organized. Note that this is the regular time and location of the SharePoint User Group meeting which will be replaced in October by the Organizer's Meeting (note: in September TSPUG will have a great workflow presentation by K2).

Registration: The website will be up asap, watch this blog for the announcement to be the first in line. I can't wait to see you there!

Source: Eli Robillard's World of Blog.

Radoslaw Lebkowski's Blog: Using Microsoft Office PerformancePoint Server 2007 scorecards with Reporting Services

 

In this article I would like to present how to integrate PerformancePoint Server 2007 scorecards with Reporting Services in dashboards. In this step by step guide I will show how to create scorecard in Dashboard Designer and connect it with a Reporting Services report which may contain more detailed information, for example long descriptions.

Let’s assume that we analyze progress of projects in our company. Scorecard object allows us to present basic KPIs for each project and quarter of year. Additionally our users want to see detailed descriptions about every project's stages.

So let's start creating the solution.

Radoslaw Lebkowski's Blog: Using Microsoft Office PerformancePoint Server 2007 scorecards with Reporting Services

Laurentiu Cristofor's blog : Simple demo for how to encrypt and decrypt a table column in SQL Server 2005

For my first post, I decided to make available a very simple demo I wrote recently to demonstrate how data can be encrypted in SQL Server 2005. You can paste the following script in SSMS and execute it line by line while following the comments. The script was written and tested on build 1256.73.

-- Goals of this demo:
-- Show how a column can be encrypted and decrypted and how an authenticator value can be used

create database demo;

use demo;

-- create a simple employee table
create table t_employees (id int primary key, name varchar(300), salary varbinary(300));

-- create a key to protect the employee sensitive data, in this case - the salary
create symmetric key sk_employees with algorithm = aes_192 encryption by password = 'Pufd&s@))%';

-- open the key so that we can use it
open symmetric key sk_employees decryption by password = 'Pufd&s@))%';

-- verify key was opened
select * from sys.openkeys;

-- insert some data
-- we will use the id as an authenticator value to tie the salary to the employee id
insert into t_employees values (101, 'Alice Smith', encryptbykey(key_guid('sk_employees'), '$200000', 1, '101'));
insert into t_employees values (102, 'Bob Jones', encryptbykey(key_guid('sk_employees'), '$100000', 1, '102'));

-- see the result; salary is encrypted
select * from t_employees;

-- create a view to automatically do the decryption
-- note that when decrypting we specify that the id should be used as authenticator
create view v_employees as select id, name, convert(varchar(10), decryptbykey(salary, 1, convert(varchar(30), id))) as salary from t_employees;

-- see the result, the decrypted data is available
select * from v_employees;

-- demo the authenticator role
-- copy salary of Alice and overwrite the value for Bob
-- execute next 3 lines as batch
declare @salary varbinary(300);
select @salary = salary from t_employees where id = 101;
update t_employees set salary = @salary where id = 102;

-- note that both entries have the same salary blob
select * from t_employees;

-- see the result, the decrypted data for Bob is no longer available
-- because it doesn't match the authenticator, which is his employee id
select * from v_employees;

-- now close the key
close symmetric key sk_employees;

-- verify key was closed
select * from sys.openkeys;

-- see the result, we can no longer decrypt any data because the key is closed
-- to access the data again we would need to reopen the key
select * from v_employees;

-- cleanup
drop view v_employees;
delete from t_employees;
drop table t_employees;
drop symmetric key sk_employees;

use master;

drop database demo;
-- EOD

Laurentiu Cristofor's blog : Simple demo for how to encrypt and decrypt a table column in SQL Server 2005

ETL Built-in to the Database Layer?

Sounds like BCP to me...

Lucid is another column-store database that complements Mondrain as a data warehousing tool.

LucidDB is capable of executing extract/transform/load processes directly as pipelined SQL statements, without any external ETL engine required. This is made possible via a number of features:

• Extraction of external metadata and data via SQL/MED: LucidDB comes with out-of-the-box support for extracting from flat files and JDBC data sources. Additional foreign data wrappers can be plugged in to make any data source look like a schema of relational tables. For example, a wrapper has been written for access to Salesforce.com web services; each Salesforce object can be queried as a table, with SQL expressions such as filters pushed down into the webservice call via an optimizer rule.
• Pipelined Java transformations (a.k.a. UDX): LucidDB supports the creation of "table functions" implemented as Java routines; these functions take one or more cursors as input and produce a new cursor as output, meaning they can be incorporated directly into SQL statements. For example, a data clustering algorithm can be implemented in Java and invoked as a pipelined operator in a SQL query, allowing complex ETL functions such as name and address matching to be executed without the need to materialize intermediate temporary tables. LucidDB's hybrid Java/C++ architecture is optimized for efficient batching of tuples back and forth across the JNI boundary between managed and unmanaged memory. All threads run in a single process and Java virtual machine, and calls across the JNI boundary do not require extra threads, so there is no inter-process communication overhead. For more information, see the UDX HOWTO.
• Bulk-load via INSERT and UPSERT: no separate bulk-load utility is required; instead, the SQL INSERT statement does the job directly, selecting from a query consisting of SQL operators such as join and minus, Java transformations, and SQL/MED sources. LucidDB also supports the SQL:2003 standard MERGE statement for implementing upsert. All DML operations (including DELETE) are fully recoverable via table-level locking and page-level logging; for efficiency, no record-level logging or locking is needed. Errors due to constraint violations can be written to a reject file without aborting the load.
• Read/write concurrency with snapshot consistency: readers (e.g. report execution) are never locked out by bulk load, update, or deletion.
• Sequence support: via the GENERATED clause in surrogate key column definitions.
• Application library: LucidDB comes with applib, a library of commonly used ETL and analytical functions and transformation, e.g. a calendar table generator.

LucidDB Home Page

Semantic Data Language | J.H. ter Bekke tribute site

Another item to turn SQL DBA heads, and probably the future of Data Warehousing.  No joins and no NULL values.  Semantic Data Model & SDL is the way to go.

The semantic language does not allow for join-operations; it requires applying paths really existing in the underlying data model. The first step is to derive the required number of employees per department, using the temporal attribute “department its number”:

extend department with number = count employee
where town = “Amsterdam”
per department.

value minimum = min department its number.

get department where number = minimum.

The term “per” means the same as “for each”, which most probably is easier to understand then “GROUP BY”: if an empty sub set of data is involved in a join combined with the “GROUP BY” construct then an incomplete, thus incorrect query result is possible! Here we have the strange situation that a seemingly semantically correct SQL-query produces a result of which the correctness depends on the actual contents of the database!

In the semantic approach NULL-values do not occur in the database.

Semantic Data Language | J.H. ter Bekke tribute site

Relational database pioneer says technology is obsolete

In the past I have seen databases with hundreds of columns and thought it was extremely bad practice and generally unmaintainable.

I should keep in mind that bad and good are really just opinions of the masses, and one size doesn't necessarily fit all. 

Column databases "will take over the warehouse market over time, completely displacing row stores," Stonebraker wrote. "Since many warehouse users are in considerable pain (can't load in the available load window, can't support ad-hoc queries, can't get better performance without a "fork-lift" upgrade), I expect this transition to column stores will occur fairly quickly."

Relational database pioneer says technology is obsolete

Stonebraker's blog, The Database Column, offers more on the subject of wide-not-deep databases that offer 50x the performance of relational databases for data warehousing applications.

InfoPath Team Blog : Submitting to a SharePoint List

 

As you know, submitting to a Microsoft Windows SharePoint form library from an InfoPath form is quite simple: just add a “submit” type of data connection to InfoPath and away you go. However, submitting to a SharePoint List is another matter. In this post, we will take a look at the steps necessary to enable this functionality.

InfoPath Team Blog : Submitting to a SharePoint List

General “tips” for investigating and improving performance in the PPS Add-In for Excel (in no particular order…)

 

General “tips” for investigating and improving performance in the PPS Add-In for Excel (in no particular order…)

Based on the steps we take when investigating performance issues with the PPS Add-In for Excel, I thought it may be helpful to post some of the things we check for. In no particular order, here is what we walk through when trying to improve performance for a form or report in the PPS Add-In for Excel. 

1. Compare the results of running queries in the PPS Add-In for Excel vs. directly against Analysis Services

a. Use the “View MDX” option from the PPS menu/ribbon to copy the MDX for a matrix. Use SQL Management Studio or other tool to run this MDX directly against Analysis Services*. If the time is similar then investigate optimizing or changing the way calculations are defined in the model. *Note: You will need to adjust the MDX query to return results on 2 axis to run it in SQL Management Studio by moving the ‘on pages’ portion of the query to the where clause or other approach to making the query result render-able by SQL Management Studio.

2. (Excel 2007 Only) Set the matrix style to none

a. The matrix styles feature adds additional processing time to format the matrix. Setting the matrix style to none will speed up performance by removing this step.

3.   3. Check if  the time the add-in is spending “detecting writeable regions” is a bottle neck. If so separate the writeable areas and read only areas in a matrix into separate matrixes.

a. If a matrix used in an assignment has “Allow Data Entry” set to true, then the Add-In will do additional processing to determine what cells are writeable and highlight them. If setting the “Allow Data Entry” property to false speeds up performance significantly you may consider separating the potentially writeable areas of a matrix and the read-only areas of a matrix into separate matrixes and setting the “Allow Data Entry” property to false on the read only matrix.

4. 4.  performance “gets slower” over time while entering data in an assignment it may be because the “What-If Change-List” is growing large and thus taking more time to calculate.

a. Using the "View change list" option you can view the current contents of the assignment change-list. If it appears your performance problem is occurring as this list grows, consider doing the following:

i. Break the data entry matrix in to multiple smaller matrixes. The “What-If” query will only apply over the matrixes where changes are being applied, so data unaffected by the what-if changes will return faster when in separate queries.

ii. Consider instructing users to do use the “Submit Draft” option to submit changes to the server that the user is done changing, use the “Clear Changes” option to clear the change-list, and then re-download the assignment cache. This could help keep the change-list size smaller and lower the amount of time for recalculations

5.   5. In general, the first time a query is run in a report or assignment it is usually is slower. Additionally if it is the first query executed against the Analysis Services server it will also be slower. Check and see if performance improves on subsequent refreshes.

6. Check the size of the local cube being downloaded in an assignment where the "Automatically cache assignments" option is turned on.

a. When an assignment is cached locally a local cube is created to store data and perform recalculations. If this cube is large the client machine may have difficulty downloading and querying against it.

b. To check if this is an issue clear the offline cache and disable the automatic caching feature to see if “online” performance is significantly better. If it is investigate making the size of the local cube that will be created by:

i. Reducing the read permission scope for users

ii. Disabling the “offline” functionality for the model in BizModeler which will disable the automatic caching feature for all assignments that use the model.

iii. Using the “manually define sub-cube” option to define a more specific sub-cube to download and cache locally.

7. Try to use calculated members over Design-Time formulas whenever possible

a. Rendering and processing of design time formulas by the add-in is a common performance bottle neck.

The BizSharpie PerformancePoint Blog

Creating BI Mashups

 Acronym hell is upon us, but still a good article on Event Driven Architecture (EDA) and how it will change business processes from pull to push.

When we build our BI with EDA on SOA, we essentially create the BI as a mash-up of services. We can take that even further and have the BI component itself expose its trend data and other analysis results as a service. We can then consume that data and use it in other applications. For instance, if the CEP query in Listing 2 will generate an event every time that an order exceeds $100,000, we can present a nice dashboard on the CEO's portal that will show in real time how many large orders the organization processes per hour/per day, and so on, along with a few other meaningful gauges.

Bridging the Impedance Mismatch Between Business Intelligence and Service-Oriented Architecture

Application Templates for Windows SharePoint Services 3.0

 

Application templates are out-of-the-box custom scenarios tailored to address the needs and requirements of specific business processes or sets of tasks in organizations of any size. They also provide a starting point for partners and developers looking to build deeper SharePoint-based solutions. The templates make use of Windows SharePoint Services 3.0 capabilities and are compatible with Microsoft Office SharePoint Designer 2007 to help make customization easier.

Application Templates for Windows SharePoint Services 3.0 are separated into two groups, site admin templates and server admin templates, providing the right combination of integration with the platform and ease-of-deployment within an organization.

Application Templates for Windows SharePoint Services 3.0

A Marvellous Point : Configuring Kerberos for SharePoint 2007: Part 2 - Excel Services and SQL Analysis Services

 

Configuring Kerberos for SharePoint 2007: Part 2 - Excel Services and SQL Analysis Services

This is the second of my several-part series on how to configure Kerberos for MOSS 2007. In the first article, I outlined the steps that are required in order to get Kerberos working for a basic MOSS installation. In this article I am going to address one of the most common scenarios that actually requires Kerberos in order to work; that is using Excel Services to display data from a SQL Analysis Services Cube (or a normal SQL database) via SharePoint.

A Marvellous Point : Configuring Kerberos for SharePoint 2007: Part 2 - Excel Services and SQL Analysis Services

The Microsoft PerformancePoint Team Blog : How do I use a ProClarity Analytics Server view in my PerformancePoint dashboard?

 

For the final release of PerformancePoint Server 2007, a PAS Report View will be available that will enable you to browse a PAS repository and select a view for inclusion in your PerformancePoint Dashboard. However, this functionality has not been added to the product yet so as an interim solution, you can create a URL report view as follows:

1. Navigate to the PAS page that you wish to include in your PerformancePoint dashboard. NOTE: you must open the page from the Contents tab in Analytics Server, you cannot use the navigation arrows on the page title bar.

2. From the address box, copy the page id of the view. Include the page= parameter and the trailing curly bracket.

3. In PerformancePoint Dashboard Designer create a new Web Page Report View.

4. In the URL box, construct the following URL, replacing the servername and the page id that you just copied.

http://servername/ PAS/en/src/PScorecard.html? page={11431FB6-4AA5-4F54-A4E0-24C114D71666}

The Microsoft PerformancePoint Team Blog : How do I use a ProClarity Analytics Server view in my PerformancePoint dashboard?

Enabling anonymous access in SharePoint 2007 - Fear and Loathing

 

 

Even though Microsoft has done a great job on improving the user interface in SharePoint 2007, some things are still buried and require a little “black magic” to get done. In this entry I’ll show you how to enable anonymous access on your site.

First, you need to enable anonymous on the IIS web site (called a Web Application in SharePoint land). This can be done by:

• Launching Internet Information Services Manager from the Start -> Administration Tools menu
• Select the web site, right click and select Properties
• Click on the Directory Security tab
• Click on Edit in the Authentication and access control section

Instead we’ll do it SharePoint style using the Central Administration section:

• First get to your portal. Then under “My Links” look for “Central Administration” and select it.
• In the Central Administration site select “Application Management” either in the Quick Launch or across the top tabs
• Select “Authentication Providers” in the “Application Security” section
• Click on the “Default” zone (or whatever zone you want to enable anonymous access for)
• Under “Anonymous Access” click the check box to enable it and click “Save”

NOTE: Make sure the “Web Application” in the menu at the top right is your portal/site and not the admin site.

You can confirm that anonymous access is enabled by going back into the IIS console and checking the Directory Security properties.

Now the second part is to enable anonymous access in the site.

• Return to your sites home page and navigate to the site settings page. In MOSS, this is under Site Actions – Site Settings – Modify All Site Settings. In WSS it’s under Site Actions – Site Settings.
• Under the “Users and Permissions” section click on “Advanced permissions”
• On the “Settings” drop down menu (on the toolbar) select “Anonymous Access”
• Select the option you want anonymous users to have (full access or documents and lists only)

Now users without logging in will get whatever option you allowed them.

A couple of notes about anonymous access:

• You will need to set up the 2nd part for all sites unless you have permission inheritance turned on
• If you don’t see the “Anonymous Access” menu option in the “Settings” menu, it might not be turned on in Central Admin/IIS. You can manually navigate to “_layouts/setanon.aspx” if you want, but the options will be grayed out if it hasn’t been enabled in IIS
• You must do both setups to enable anonymous access for users, one in IIS and the other in each site

Hope that helps!

Enabling anonymous access in SharePoint 2007 - Fear and Loathing

Setting up SQL 2005 and XMLA

Command line to query KPI data from Analysis Services.  I wonder if there is a PerformancePoint equivalent? 

cscript //nologo xmla.wsf /url:"http://localhost/olap/msmdpump.dll" /c:"Adventure Works DW" /e:"SELECT { [Measures].[Total Product Cost], KPIValue('Product Gross Profit Margin'), KPIGoal('Product Gross Profit Margin'), KPIStatus('Product Gross Profit Margin'), KPITrend('Product Gross Profit Margin') } ON COLUMNS, { [Employee].[All Employees] } ON ROWS FROM [Adventure Works]" /out:"aw-kpi.html" /v

Setting up SQL 2005 and XMLA

Opening Assignment Workbook from URL

To facilitate all of this the PerformancePoint Add-In for Excel ships with a protocol handler that, after registered on a client machine, allows you to specify a hyperlink such as “PerformancePoint:http:\\PPSServer:46787&Application=AdventureWorks&Assignment=1027” which when clicked will do the work of opening Excel and opening the assignment that is specified.

The BizSharpie PerformancePoint Blog

Download details: MDX Query Troubleshooting

Just in time for troubleshooting issues with Scoping & PerformancePoint scripts... 

SQL Server Best Practices Article: Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services

Brief Description

This article provides the information about the available MDX query troubleshooting tools and demonstrates how to use the most common of these tools to identify and resolve MDX query performance bottlenecks with individual MDX queries

Download details: MDX Query Troubleshooting

Data Visualization: Modern Approaches | Graphics

My favourites are Amaztype

 & NewsMap

- there are some great ones here...

Data Visualization: Modern Approaches | Graphics

Gert Fahrnberger ’s BI log » Blog Archive » Who will sell and implement PerformancePoint Server?

The Business Intelligence "mashup" that is PerformancePoint will require end-to-end understanding of many of the BI tools that Microsoft provides, as Gert discusses... 

The technical knowledge needed is vast - the components involved beside PerformancePoint Server are SQL Server along with its Analysis Services and Integration Services components, Excel and SharePoint. And so many Microsoft partners who have implemented infrastructure solutions or data-warehouses with a technical approach up to now, have started to embark on the PerformancePoint journey ramping up their technical skills.

Gert Fahrnberger ’s BI log » Blog Archive » Who will sell and implement PerformancePoint Server?

PerformancePoint Server Dashboarding

 I have used PerformancePoint's Monitoring and Analytics component since it was the Business Scorecard Accelerator add-in.   I'm looking forward to implementing the newest version of this component, to be released September 18th with the full suite.  It should offer a powerful solution for creating dashboards and scorecards with Analysis Services.

The link below offers a walkthrough of various tasks within PerformancePoint.

PerformancePoint Server

CPM Insider - Business Scorecard Manager - Partner List & CTP Code for PerformancePoint

 

This is an unofficial self-reported partner directory based on a partner listing that was started during the Microsoft Office Business Scorecard Manager 2005 beta program in summer 2005.

Get the next version - PerformancePoint on Connect

PerformancePoint Server 2007 now available

Previews of the scorecarding functionality in PerformancePoint Server 2007 are now available through the Business Scorecard Manager 2005 Customer and Partner Advisory Group program (CPAG). Originally designed for participants of the previous Business Scorecard Manager TAP program, this NDA-only community is now open to all Microsoft customers and partners interested in future updates on evaluating pre-released scorecarding functionality in PerformancePoint Server 2007.

1) Use you Passport account to log in to http://connect.microsoft.com and click on "Invitations" on the left-side menu.

2) Use invitation ID: CPAG-BX8V-XRJ3

Source: partners

Windows XP Performance

Not related to PerformancePoint, however developers using Visual Studio and XP may appreciate some of these tweaks to speed up performance.

Whenever XP reads a file, it stamps the file with the date and time of the access. This feature has its place but is not required for normal use unless you rely on the date of last access for backup purposes, which is a rare requirement for most backups. Putting a timestamp a file that has just been read means that a write access has to be made to disk, so every time a read is executed, a corresponding write is also executed.

At the command prompt, enter the following then reboot; 

FSUTIL behavior set disablelastaccess 1

Those of us who read and write English need to take note of the American spelling of the BEHAVIOR parameter.

To turn timestamps back on, repeat the command but replace the 1 with 0. 

Windows XP

Get Ready for Performance Point! - Overview

For the "C" audience reading this blog, (CFO, CEO, CIO, CTO), the management team and BI architects, there is a one day event coming up on Thursday September 20, 2007 in New York City.

Free lunch in New York... why not?

It's a New Day for BI!

Effective performance management and financial planning can help you drive better performance for your business. Come learn how companies like yours, and Microsoft itself, are benefiting from an integrated monitoring, analysis and financial planning application.

During this one-day, free event you'll hear how Microsoft Office PerformancePoint Server 2007 helps make your business better in four key ways:

• Improve business performance with an integrated application for monitoring, analyzing and planning
• Drive alignment and accountability across the entire organization
• Enable more decision makers to impact the performance of the business
• Ensure transparency, security and auditability with an enterprise grade performance management solution

What You Will Get

• A morning of business content presented by Microsoft, our customers, as well as independent business performance management experts, and in the afternoon technical information about the Office PerformancePoint Server release from Microsoft, and information about solutions from technical representatives of key Microsoft BI partners
• Chance to win a Xbox 360, Bose SoundDock system and other prizes
• Lunch and snacks
• Attendee bag with various special offers from sponsors
• Microsoft Office PerformancePoint Server 2007 giveaways

It's a New Day for BI!

Source: Get Ready for Performance Point! - Overview