Get Started with the Enterprise Library Data Access Application Block: ASP Alliance

Great resource for snippets here. 

The Enterprise Library Data Access Application Block is the new version of the Microsoft Data Access Application Block (DAAB). Whereas the previous DAAB was a stand-alone application block, the new DAAB is a part of the new Enterprise Library collection of application blocks. You can obtain the most recent version of the library from the Patterns & Practices Enterprise Library Developer Center.

Last week, I spent one whole day trying to figure out how to configure and work with the new Data Access Application Block. This was due to few resources being available on the web. I even visited the Data Access Application Block forum at the ASP.NET Forums, but found few good resources to start with. Even with the few articles I found, I was not immediately successful in learning how to use the different methods available in the new Enterprise Library.

For the above reasons, I decided to write this article; not to explain how to configure the Enterprise Library, but rather to provide sample codes that can be used in a data-driven ASP.NET website.

Source: Get Started with the Enterprise Library Data Access Application Block: ASP Alliance

Advanced Ranking and Dynamically-Generated Named Sets in MDX - FindTech Blogs

 

This executes in 2 seconds on a cold cache on my laptop, compared to 52 seconds for the equivalent query which evaluates the TopPercent for every single cell, so it's definitely a big improvement

Source: Advanced Ranking and Dynamically-Generated Named Sets in MDX - FindTech Blogs

Intelligent Insight on PerformancePoint

 

Hot Topics

Performance Point CTP3 – will be available this month!

What’s new in Monitoring and Analytics CTP3

Click here to sign up for CTP3

SQL Server 2008: “Katmai”

SQL Server 2008 Web Casts

Developers to Get Hooks into SQL Server ‘Katmai’

Bridging the Gap Between Data and Developers

Microsoft acquires Dundas’s data visualization components, & SQL 2008 CTP news

Microsoft Acquire Stratature

Microsoft Buys Data Management Vendor Stratature

Acquisition FAQ

Business Intelligence Virtual Labs

Try out a business intelligence virtual lab to test out the technology stack, including:

- Analysis Services

- Excel 2007

- Business Scorecard Manager 2005 (BSM)

- ProClarity

- Report Builder

- Integration Services

- Data Mining

Click here to access the Virtual Labs.

ProClarity Evaluation

Available through MSVL and through the following MSDN Premium subscriptions:

Visual Studio Professional w/MSDN Premium Subscription

Visual Studio Team Architect Edition w/MSDN Premium Subscription

Visual Studio Team Developer Edition w/MSDN Premium Subscription

Visual Studio Team Database Professional Edition w/MSDN Premium Subscription

Visual Studio Team Test Edition w/MSDN Premium Subscription

Visual Studio Team Suite Edition w/MSDN Premium Subscription

Academic Alliance members will also have access to these bits.

Feel free to email me for further assistance.

ERP-Link: Microsoft BI for SAP

The iNet Platform provides a SAP Certified connector to bring data from SAP R/3 & BW to SQL Server Analysis Services.

Jason Morales recently hosted a webinar with ERP-Link to present SAP BW data through ProClarity Dashboards. The session offered great insights into how iNet extends the ETL functionality of SSIS to more thoroughly integrate with SAP data sources.

Please click here to request access to the webinar recording.

Upcoming Events

PerformancePoint/ProClarity Briefing - Customer & Partner

London June 26th (AM): Click here to register 

Manchester June 28th (AM): Click here to register

An introduction to Monitoring & Analytics

London June 26th (PM): Click here to register 

Manchester June 28th (PM): Click here to register

TechNet Webcast: SQL Server Code Name “Katmai” Overview

When: Tue (June 26) @ 4pm-5pm (BST)

Click here to register

TechNet Webcast: Real-Time BI with SQL Server 2005 Analysis Services

When: Wed (June 20) @ 5:30pm (BST)

Click here to register

MSDN Webcast: SharePoint Server 2007 and Business Intelligence

When: Fri (June 22) @ 8pm (BST)

Click here to register

Training

SQL Server Training Courses

Online & Instructor led

PerformancePoint Server Training Site

Includes BSM & ProClarity self-paced online training

Resources

Microsoft BI

Whitepapers

Archived Webcasts

BI Presentations

Performance Management 101 with PPS 2007

SQL Server

Best Practices

Microsoft SQL Server 2005: Scales to your growing business needs

Evaluation Guide

Data Mining

SQLServerDataMining.com

The data miner

Microsoft BI Blogs

Microsoft BI Blog – by Patrick Husting

Nick Barclay’s BI Blog

Chris Webb’s BI Blog

Vidas Matelis BI Blog

Microsoft OLAP by Mosha Pasumansky

Prologika (Teo Lachev’s Weblog)

Charlie Maitland’s Blog

Ian Tien’s Unofficial PerformancePoint Server and Business Scorecard Manager Blog

Bruno Aziza

Russell Christopher's Semi-Useful BI Musings

Direct Reports (Brian Welcker’s Weblog)

Ben Jones SQL Blog

Randon Thoughts on MS BI products - Patrice Truong

Microsoft BI Partners

http://www.microsoft.com/bi/partners/partners.aspx

OLAP Report - summaries

Market share analysis

SQL Server Analysis Services 2005 (Microsoft)

Dimensional Relational vs. OLAP: The Final Deployment Conundrum

BI News Links & Press Releases

Ballmer on BI: Microsoft’s CEO Looks Ahead

Microsoft BI Conference

Microsoft Touts PerformancePoint, Next-Gen SQL Server at Inaugural BI Fete

Microsoft Plans BI, Security, BizTalk Managed Services

SAP and Microsoft Extend Alliance for Microsoft SQL Server 2005

Business intelligence now a given for SMBs

Next-Gen Business Intelligence: The Future Is Now

Opinion: Enterprise Search Is This Year’s Hot Topic For Business Intelligence

Source: Intelligent Insight on PerformancePoint

Display the MDX query of an Excel 2007 PivotTable

Often end users use Excel to navigate into the cube. Then they may ask you to produce a report using the same data. It would be useful to get the MDX query used by Excel. Moreover, Excel 2007 offers a good MDX quality of the produced query against Analysis Services 2005 (much better than Excel 2003).

I don't know why Excel doesn't have such a function. I created a simple Excel macro that add an item to the PivotTable menu. A code that add a button in the Ribbon would be very welcome!

If you want to add these macro for all Excel sheets, you have to modify the PERSONAL.XLS file contained into "Documents and Settings\[Username]\Application Data\Microsoft\Excel\XLSTART".

This is the macro Workbook_Open that is executed when you open a workbook.

Private Sub Workbook_Open()
   Dim ptcon As CommandBar
   'See the following for list of menus in excel
   'http://support.microsoft.com/support/kb/articles/Q213/5/52.ASP
   'Title: XL2000: List of ID Numbers for Built-In CommandBar Controls
   Set ptcon = Application.CommandBars("PivotTable context menu")
insertDisplayMDX:
   Dim cmdMdx As CommandBarControl
   For Each btn In ptcon.Controls
       If btn.Caption = "MDX Query" Then GoTo doneDisplayMDX
   Next btn
   ' Add an item to the PivotTable context menu.
   Set cmdMdx = ptcon.Controls.Add(Type:=msoControlButton, temporary:=True)
   ' Set the properties of the menu item.
   cmdMdx.Caption = "MDX Query"
   cmdMdx.OnAction = "DisplayMDX"
doneDisplayMDX:
End Sub

And this is the DisplayMDX subroutine, that you can insert in a separate module.

Sub DisplayMDX()
    Dim mdxQuery As String
    Dim pvt As PivotTable
    Dim ws As Worksheet
    Set pvt = ActiveCell.PivotTable
    mdxQuery = pvt.MDX
    ' Add a new worksheet.
    Set ws = Worksheets.Add
    ws.Range("A1") = mdxQuery
End Sub

When you right-click the PivotTable, the last item of the menu will be "MDX Query". If you click it, a new worksheet is created with the query in A1 cell. This is the fastest way to copy and paste it into other programs. I would like to be able copying directly into clipboard, but I still haven't found a reliable way to do it. If someone has some good idea, please contact me or comment to this post.

I attached a copy of a PERSONAL.XLS for the lazy of you!

Source: SQL BI : January 2007 - Posts

Integrating KPI in Reporting Services and drill down reports - MSDN Forums

 

How to include SQL Server 2005 KPI’s in Reporting Services Reports

(The following is originally a description with screendumps, which unfortunately are not supported. Hopefully usefull anyway ...)

There seems to be an issue about including SQL Server 2005 KPI's in Reporting Services Reports (in Visual Studio) - especially the images associated with the generated parameters, e.g. "-1" leads to "red gauge / traffic light" and so on.

However it is possible to make reports in Report Builder including SQL Server 2005 KPI's.

It has been suggested to include a data source and a Report Server Model in the Visual Studio Project. This should make it possible to build a report including KPI’s. However there still seems to be a problem accessing the measures even though the metadata can be viewed. The Report Wizard does not have an option for including graphics for the indicators – only the numbers to generate the images (“1”, ”-1”, ”0” etc.). Therefore the indicators are not included in the dataset and cannot be included in the reports.

This has lead to the suggestion, that there is an issue with the Report Model functionality in Visual Studio.

There is a work around though ...

First you must associate your KPI’s to measure groups in the Visual Studio project …

Establish an Analysis Services data source on your Report Server …

Note: the Connection String must be typed when the data source is established. (See next-next screen dump.)

From the data sources on the reportserver a Report Model can be generated.

(Double-click the data source and click on generate model.)

Based on this model, reports can be build in Report Builder - including SQL Server 2005 KPI's, including images.

Open Report Builder from the Report Manager. The established data model(s) will be shown as possible data sources.

Build the report …

The reports can be exported as a .rdl-file from Report Builder. Click Save to file … not Save as …

If you choose Save as … the report is saved on the server.

Import the .rdl-file in your Visual Studio project …

The layout of the report can be edited in Visual Studio. The Data and Preview panes return an error when clicked.

Save and deploy the report … Right-click the project name / Properties to view where the report is deployed to by default (e.g. http://localhost/reportserver/reports).

The described solution is definitely a work-around – it is not an optimal solution. There are two major downsides with the solution: the reports cannot be previewed in Visual Studio and the dataset cannot be changed in Visual Studio.

Source: Integrating KPI in Reporting Services and drill down reports - MSDN Forums

Eugene Asahara takes my love/hate (currently hate) stock market obsession and data mines it.

 

I’m using the association rules algorithm from Analysis Services 2005 to figure out things like: On days that INTC rises significantly, MSFT often rises significantly too. However, I don’t intend to use that knowledge to simply buy MSFT when I notice INTC going up. There’s not much value in that in itself. The value arises in realizing there is a connection between the fortunes of MSFT and INTC. So, if there is a situation where INTC announces its quarterly earnings before MSFT, I’ll have a good clue that MSFT’s quarter will be similar. This won’t work well on stocks as well-known as these two. If INTC announced a great quarter, MSFT will immediately start rising. But perhaps there are pairs that go relatively unnoticed.

I can also create a web of relationships using these pairs. For example, hypothetically, if I see INTC and MSFT go up and down together very often, and I see that INTC and HP also go up and down together very often, there lies a small web: MSFT-->INTC-->HP. 

This is the market basket analysis approach. In the normal use of market basket analysis, analysts want to know which products are purchased together (ex: milk and cereal, pizza and beer, steak and Cabernet Sauvignon) in the same "shopping basket". That way, the products could be placed near each other or promotion campaigns could be engineered for the two products. For more on market basket analysis (using OLAP), see Amir Netz's classic article, Analysis Services: DISTINCT COUNT, Basket Analysis, and Solving the Multiple Selection of Members Problem.

Source: Eugene Asahara

SQL 2008 KATMAI Features CTP

 

E D L E H M A N'S P O S T I N G

This coming Monday, June 4, we will release the June CTP of Katmai. We will also announce the official naming of Katmai as SQL Server 2008. Download links will be live on http://connect.microsoft.com/sqlserver Monday morning at 8:30am EST.

Among the 27 improvements included in the CTP are:

Declarative Management Framework (DMF) is a new policy-based management framework for the SQL Server Database Engine that delivers the following benefits:

. Ensure compliance with policies for system configuration . Prevent/monitor changes to the system by authoring policies for the desired configuration . Reduce total cost of ownership by simplifying administration tasks

Change Data Capture (CDC). Change Data Capture (CDC) is a generic component that will track database changes asynchronously and expose the changes through a relational interface which can be consumed easily.

. Through this interface, consumers can very easily track changes based on their specific requirements and consume the change data using T-SQL or other data access methods.

MERGE SQL Statement. Common data warehouse scenarios require developers to either insert information or update information. SQL Server 2008 will provide new statements within the SQL language to enable developers to provide this functionality with a single statement.

Star Join Query Optimizations. For improved query performance for common data warehouse scenarios, Star Join Query optimizations reduce query response time by recognizing data warehouse join patterns.

AS Dimension Design: Improved Supportability and Integration of Best Practices . Enhance UI for creating and editing dimensions to guide users toward designs that follow best practices.

. These include: Finish Attribute Relationship Designer, Dimension structure (presentation of attribute relationships), modification to wizards to align output with best practices, simplifying creation of composite keys, and AMO warnings (spanning all objects, not just dimensions)

Table Value Parameters. In many customer scenarios, it is necessary to pass a set of table structured values (rows) to a stored procedure/function on the server. These values may be used for populating/updating a table directly or for more complex manipulation of data for business logic purpose. Table valued parameter will provide an easier way to define a table type as well as allow applications to create, populate and pass table structured parameters to stored procedures and functions

Another important accomplishment is taking the first steps for the new setup architecture with the enablement of side-by-side installations with Yukon SP2.

Along with the release of the first Katmai CTP, we're implementing a new approach for SQL Server Samples and Community projects on Codeplex (http://www.codeplex.com/SqlServerSamples), Microsoft's open source project hosting site. Customers will now have direct access to all Microsoft SQL Server product sample source code without the need to install anything. Addressing a long-standing customer ask, customers can also install samples for specific SQL technologies from Codeplex rather having to use a single monolithic install of all samples. Microsoft SQL Server Product Samples released on Codeplex will be aligned to and tested with specific releases.

We have also started to release Community sample projects on Codeplex, which include groups of samples contributed outside of our regular product release cycle by SQL Server MVPs, Microsoft employees, or other members of the community.

The Codeplex SQL Portal page serves as a common entry point for these and other SQL Server-related projects on Codeplex. The Katmai version of SQL Server Management Studio and BI Dev Studio include a "Samples and Community Projects" link on the Community Menu that will redirect customers to the portal page.

With this approach to samples and community projects, we bring together a much broader body of code for our customers from a wide variety of sources. This new approach also enables us to take much more of a cross-product approach aligned to customer needs than the single-product focused approach we have taken in the past.

Along with the June CTP, we will be opening New MSDN Forums dedicated to Katmai located at http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=428&SiteID=1 (won't work until Monday). We are also expanding a pilot that's been working very well in the SSIS forum. Any known MVP poster can now flag replies to forum threads that need to be escalated to the Microsoft SQL team for follow-up. Member of the SQL team will be notified daily of any escalated threads. In order to flag any reply to a forum thread, just place the text [Microsoft follow-up] (including the brackets) in your reply. As always, if you have any problems with this new capability, please let me know.

Go to http://www.microsoft.com/sql/prodinfo/futureversion/default.mspx for additional info on Katmai.

Source: Darren Gosbell [MVP] - Random Procrastination

Analysis Services Processing Best Practices

 

Analysis Services Processing Best Practices

SQL Server Best Practices Article

Published: June 7, 2007

Writers: Denny Lee, Nicholas Dristas, Carl Rabeler

Contributors: Kevin Cox, Craig Utley, Eric Jacobsen, Akshai Mirchandani

Technical Reviewer: Sasha (Alexander) Berger, Dave Wickert

Applies To: SQL Server 2005 SP2

Summary: Following the best practices described in this article will help you improve the performance of OLAP database processing

Source: Analysis Services Processing Best Practices

SSAS: Office 2007 - breaks the calculation tab in BIDS

 

This probably does not apply to many people yet, but If you have installed the RTM of Office 2007 and don't yet have SP2 of SQL 2005 installed, then your calculation tab in the cube designer is probably broken.

Source: SSAS: Office 2007 - breaks the calculation tab in BIDS

Formatting Currency in Reporting Services

 

Say you have a table which contains different currency values - each row in your table contains values for stored with a different currency types (see figure 1). You want to display these values in your report and ensure that each value is displayed with the correct currency symbol (figure 2). How do you to this?

Source: Russell Christopher's Semi-Useful BI Musings

SQL Server 2008 CTP and Dundas Software

 SQL Server 2008 CTP3 has been released to the public for download.  In addition, MS has purchased Dundas Software's visualization tools for incorporation into the platform.  No more cripplecharts in Reporting Services!

Do you have passion for technology? Do you want to make a difference? Then try out a SQL Server 2008 Community Technology Preview (CTP). The SQL Server development team uses your feedback from CTPs to help refine and enhance product features.

Source: SQL Server 2008 CTP

PerformancePoint as a Managed Service

 MS is offering Exchange, SharePoint, LCS, and now PerformancePoint as a managed service.  The new flavours of BizTalk should drive the growth of these options.

"Microsoft pushing a managed services offering is more troubling at this interval than any previous occasion," said Stephen Moss, COO of NSPI, a Roswell, Georgia security solution provider. "It will be most interesting to see [Microsoft's plans] in the area of direct offerings of services or Software + Services as the case may be."

Source: Microsoft Plans BI, Security, BizTalk Managed Services - Managed Services - IT Channel News by CRN and VARBusiness

CRM News: Strategy: Microsoft's Raikes on the Role of BI in Corporate Strategizing

The complete mashup of MS BI technologies will soon be released as PerformancePoint. 

Q: Microsoft talked a lot today about the next version of SQL Server. What's on the horizon for Microsoft Bl applications, and what can users expect next?

A: The key thing is what we are doing with PerformancePoint 2007

Source: CRM News: Strategy: Microsoft's Raikes on the Role of BI in Corporate Strategizing