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
• 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)
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 :
- How fast you are going (airspeed indicator)
- The orientaton of the plane in the sky (artificial horizon)
- How high you are (altimeter)
- 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.
- Which direction you are going (compass)
- Whether you are going up or down (vertical speed indicator)
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
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
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.
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
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/bihttp://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:
...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
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.
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
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?
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.
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...
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 BlogLive Search:
Live Search Team Blog
Live Search Maps Team Blog (and Virtual Earth)
Live Search QnA Team Blog
Live Search Expo Team BlogWindows 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 BlogMicrosoft Channels:
Channel 8
Channel 9
Channel 10Microsoft Expression & Silverlight:
Microsoft Expression Team Blog
Microsoft Silverlight Community BlogsMicrosoft 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
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:
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
An interview with the Program Manager of SQL Server Analysis Services.
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.
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
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
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.
· 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.
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
Source: Document Management Systems
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 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
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 20The 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.
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.
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 usedcreate 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
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:
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.
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.
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.
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.
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 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.
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.
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}
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
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
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.
