SQL Server Manageability Team Blog : Custom Reports in Management Studio

 

Implementation

Custom reports stored as report definition (.rdl) files are created by using Report Definition Language (RDL). RDL contains data retrieval and layout information for a report in an XML format. RDL is an open schema; developers can extend RDL with additional attributes and elements. Reports may execute any valid tsql statement within the report.

If Object Explorer is connected to a server, custom reports can execute in the context of the current Object Explorer selection if they reference that node's report parameters. This enables a report to use the current context, such as the current database, or a consistent context, such as specifying a designated database as part of the tsql statement that is contained in the custom report.

SQL Server Manageability Team Blog : Custom Reports in Management Studio

Aaron Bertrand : A custom report for Management Studio : Show Blocking

Quick and easy way to see blocked processes. 

So guess what I set out to do first? I created my own blocking report that includes the information I deem most useful to finding the root cause and stamping it out. Why should I spend all my time typing out sp_who2, and sp_lockinfo, and select * from sys.dm_exec_requests, and DBCC INPUTBUFFER, when I can create a report that does most of that work for me in a couple of clicks?

Based loosely on the procedure I created in my article, "Can I create a more robust and flexible version of sp_who2 using SQL Server 2005's DMVs?", I wrote the following stored procedure that would be consumed by a custom report:

Aaron Bertrand : A custom report for Management Studio : Show Blocking

Passed 70-446 BI Exam

 
After passing the required exams, I'm now a Microsoft Certified Technology Specialist in Microsoft SQL Server 2005 Business Intelligence (MCTS: SQL Server 2005 BI) which won't fit on my business cards but that is okay.

The last exam was brutally long (5 case studies, 9-10 questions per) and I will probably have nightmares tonight about Datum corp.

The funniest part was I got a perfect mark in the Data Mining category, and that was the one I was most concerned with. 

My studying strategy for this one was similar to 70-445, though I skimped on some of the research because of work commitments:

1. Get the outline from MS's site.
2. Export it to Excel and select the items I'm not familiar with.
3. Bring them into One Note.
4. Create tabs for each item to expand on.
5. Cross them out as I get the research.
6. Google each outline item with the category or "MSDN" or "Technet" to get more info.
7. Search Blogsearch & Technorati.
8. Search for the specific items I'm not familiar with using the same wording as the ms outline.
9. Read the books.  (The Kimball Data Warehousing book was excellent)

The exam took about 2 1/2 hours.  At the very end of the exam, after I found out my mark, the application hung and wouldn't print the score.  We spent about 10 minutes trying to get it to print, and I came home with 10 copies of my result.

Here's the outline of the exam.

1.    Select appropriate BI technologies.
2.    Specify the appropriate SQL Server edition.
3.    Design dimensional models.
4.    Design dimensions for each subject area.
5.    Design fact tables for each subject area.
6.    Identify current dimensions that can be reused.
7.    Identify elements that must be added to existing dimensions or fact tables.
8.    Design new physical objects based on a logical model.
9.    Design an indexing strategy.
10. Design a surrogate key strategy.
11. Identify appropriate business keys.
12. Design a partitioning strategy.
13. Identify design constraints.
14. Identify changed data in the source system.
15. Decide the strategy for decoding textual values.
16. Decide whether to implement fast load.
17. Design appropriate destination components to handle new and updated records during incremental loads.
18. Identify appropriate transformations and transformation options.
19. Design data flow.
20. Identify appropriate control flow items.
21. Design the control flow sequence.
22. Identify appropriate uses and placement of event handlers.
23. Identify appropriate uses and placement of checkpoints.
24. Identify appropriate uses of logging.
25. Identify appropriate uses of data flow error handling.
26. Select appropriate uses of shared data sources.
27. Select appropriate uses of stored procedures or user-defined functions.
28. Define appropriate security roles.
29. Specify folder security.
30. Specify field-level security.
31. Select appropriate uses of Report Designer.
32. Select appropriate uses of Report Definition Language (RDL).
33. Select appropriate uses of Report Builder.
34. Decide appropriate uses of datasets.
35. Decide appropriate uses of subreports.
36. Decide the appropriate placement of extensive business logic.
37. Identify appropriate uses of report snapshots.
38. Identify appropriate uses of on-demand reports.
39. Identify appropriate uses of on-demand-from-cache reports.
40. Identify appropriate uses of standard subscriptions.
41. Identify appropriate uses of data-driven subscriptions.
42. Identify appropriate report-delivery methods for subscriptions.
43. Identify appropriate uses of the Reporting Services Web Service library.
44. Identify appropriate uses of the Reporting Services Configuration tool.
45. Select appropriate uses of named queries.
46. Select appropriate uses of named calculations.
47. Select appropriate uses of denormalization strategies.
48. Identify appropriate uses of attribute relationships.
49. Identify appropriate uses of column binding to support a user-defined reporting hierarchy.
50. Select a design for implementing a ragged hierarchy.
z51. Select an appropriate strategy to implement member properties.
52. Identify appropriate uses of calculated members.
53. Identify appropriate uses of actions.
54. Identify appropriate uses of key performance indicators (KPIs).
55. Identify appropriate uses of perspectives.
56. Identify appropriate uses of translations.
57. Identify appropriate uses of drillthrough.
58. Identify a relationship type.
59. Identify appropriate uses of role-playing dimensions.
60. Choose an appropriate strategy to handle unknown dimension members.
61. Define appropriate security roles.
62. Design dimension security.
63. Design cell security by using Multidimensional Expressions (MDX).
64. Design a partitioning strategy for optimal data availability.
65. Decide whether proactive caching is an appropriate solution.
66. Design partition storage settings.
67. Select a dimension storage mode.
68. Identify appropriate algorithms to meet requirements.
69. Classify data as input, key, predict, and ignore.
70. Select appropriate uses of SSRS Data Mining Extensions (DMX) queries.
71. Select appropriate uses of ActiveX Data Objects (Multidimensional) (ADOMD).
72. Select appropriate uses of SSIS Data Mining Query tasks.
73. Select appropriate uses of data mining viewer controls for Microsoft .NET Framework-based applications.
74. Select appropriate uses of full processing.
75. Select appropriate uses of structure processing.
76. Select appropriate uses of default processing.

The exam focused heavily on Integration Services, data model & data mining exhibits, with quite a few questions around transactions and security.

My next exam will probably be the PerformancePoint beta, or maybe Sharepoint... or ... Oracle?

Source: MCTS: SQL Server 2005 Business Intelligence

Open Source Project Management Tools in C#

 

User Story.NET

This project is a tool for Extreme Programming projects in their User Story tracking.

Go To User Story.NET

Project Portal

Project Portal is a multi-lingual, multi-user web-based groupware suite for Programme & Project Management.

Go To Project Portal

SharpForge

SharpForge supports collaborative development and management of multiple software projects. Similar to SourceForge or CodePlex but for your own team or organisation. The software is written in C# for .NET 2.0, uses Subversion for source control and is released under the New BSD License. Home page: http://sharpforge.org/ Features: - Multi Portal - Multi Project - Subversion Administration - (planned)Work Item Tracking - (planned)Project Forums - (planned)Release Management - (planned)Subversion based content management - (planned)News Feed Aggregation Requirements: - Windows 2000 / WinXP + SP1 or Windows 2003. - IIS - .NET 2.0 - Sql Server Express SP1 - Apache 2.0.54 - Subversion 1.3

Go To SharpForge

Source: Open Source Project Management Tools in C#

Cool Text: Logo and Graphics Generator

 

Cool Text is a free graphics generator for web pages and anywhere else you need an impressive logo without a lot of design work. Simply choose what kind of image you would like. Then fill out a form and you'll have your own image created on the fly.

Source: Cool Text: Logo and Graphics Generator

Open Source Rules Engine for .NET

 

NxBRE -The open-source rule engine for the .NET

NxBRE is the open-source rule engine for the .NET platform and a lightweight Business Rules Engine that supports two different approaches:

• The Inference Engine, which is a forward-chaining (data driven) deduction engine and that supports concepts like Facts, Queries and Implications and like Rule Priority, Mutual Exclusion and Precondition (as found in many commercial engines). It is designed in a way that encourages the separation of roles between the expert who designs the business rules and the programmer who binds them to the business objects.
• The Flow Engine, which uses XML as a way to control process flow for an application in an external entity. It is basically a wrapper on C#, as it offers all its flow control commands (if/then/else, while, foreach), plus a context of business objects and results. It is a port of JxBRE to .NET's C#.

NxBRE is released under LGPL license in order to allow users to legally build commercial solutions that embed NxBRE.

Source: .Net Adventures

Microsoft Excel : CUBE Functions 3: Formula AutoComplete revisited

 

Formula Auto-Complete for CUBE function arguments
Formula AutoComplete for CUBE function arguments has some special behaviours relative to the other functions in Excel.   As a brief refresher, Formula AutoComplete is a feature that provides a list of values from which to choose as you write formulas … here is a blog post that describes this in detail.  In most Formula AutoComplete scenarios, Excel knows the list of values (formulas, named ranges, table names) that it should display because those values are part of the Excel application.  For example, when you start typing a function name, Excel can give you a list of all the other functions that start with the same character(s), as is shown in this screenshot.

Microsoft Excel : CUBE Functions 3: Formula AutoComplete revisited

The Helper Table Workbench

 

Sometimes, when writing TSQL code in functions or procedures, it
is tempting to do iterations, or even worse, a cursor, when it isn't
really necessary. Cursors and iterations are both renowned for slowing
down Transact SQL Code SQL Server just isn't designed for it.
However, there is usually a way to do such operations in a set-based
way. If you do so, then your routines will run a lot faster, with speed
at least doubling. There are a lot of tricks to turning a problem that
seems to require an iterative approach into a set-based operation, and
we wish we could claim we'd invented one of them. Probably the most
useful technique involves that apparently useless entity, the 'helper'
table. This workshop will concentrate on this, because it is probably
the most widely used.
The most common Helper table you'll see is a table with nothing but the
numbers in a sequence from 1 upwards. These tables have a surprising
number of uses. Once you've understood the principles behind helper
tables, then you'll think of many more. We'll be providing several
examples where a helper table suddenly makes life easier. The objective
is to show the principles so that you'll try out something similar the
next time you have to tackle a tricky operation in TSQL.

As always, you're encouraged to load the example script into Query
Analyser or Management Studio, and experiment!
Our examples include:
Splitting Strings into table-rows, based on a specified delimiter
Encoding and decoding a string
Substituting values into a string
Extracting individual words from a string into a table
Extracting all the numbers in a string into a table
Removing all text between delimiters
Scrabble score
Moving averages
Getting the 'Week beginning' date in a table
Calculating the number of working days between dates.

Source: The Helper Table Workbench

SQL Server 2005 DDL Trigger Workbench

 

How about automatically tracking and logging all database changes, 
including changes to tables, views, routines, queues and so on? With SQL
Server 2005 it isn't that hard, and we'll show how it is done. If you 
haven't got SQL Server 2005, then get SQL Server Express for free. It 
works on that! While we're about it, we'll show you how to track all
additions, changes and deletions of Logins and Database Users, using
a similar technique.

Source: SQL Server 2005 DDL Trigger Workbench

Robyn Page and Phil Factor

Some excellent articles on Reporting Services & SQL programming. 

Robyn Page is a consultant with Enformatica and USP Networks. She is also a well known actress, being most famous for her role as Katie Williams, barmaid in the Television Series Family Affairs.

Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 20 years of experience with database-intensive applications. Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

Source: Robyn Page and Phil Factor

PDF from .NET, Reporting, BIRT, Telerik, Orcas, URL Data Services and Astoria! - Sam Gentile

Experiences from the front - adhoc reporting of the future. 

About this time, we both started to realize that what a lot of these reporting tools wanted was an "XML feed" if you will, particularly Actuate. It made much more sense to have it "point to" XML rather than tight-binding directly to database tables. Then we started to talk about what really needed was to have URI-addressable sources of XML formatted data.

That's when both cried "Astoria!" We finally get to use an Orcas thing! -) So we downloaded Astoria, fired up Orcas, and pretty much, without looking at any doc, were able to get a prototype working that used a REST-style interface over our SQL Server data returned via an Entity Framework model and URIs to point to it. I think, it's fair to say, that we were both quite impressed with Astoria's power and ease of programmability in this regard. It just seems so natural to me now that data and resources being surfaced through URIs and HTTP verbs.

Source: PDF from .NET, Reporting, BIRT, Telerik, Orcas, URL Data Services and Astoria! - Sam Gentile

Rapid Application Development: Referential Integrity - Data Modeling Mistake 1 of 10

Catch up - Lee is on number two. 

Referential Integrity - Data Modeling Mistake 1 of 10

In my mind data models are like the foundations of a house. Whether you use ORM or a more traditional modeling tool, they form the base of the entire rest of your project. Consequently, every decision you make (or don’t make) regarding your data model during the design phase(s) of your project will significantly affect the duration of your project and the maintainability and performance of your application.

Rapid Application Development: Referential Integrity - Data Modeling Mistake 1 of 10

Lucian's weblog : Retrieve data from Wikipedia using C#

This could be useful, since Wikipedia is now the source of all information in the world, along with Google & Facebook! 

To get other pages, you can simply use the direct link. The link is composed (as you’ve maybe already noticed) from http://en.wikipedia.org/wiki/Special:Export/ followed by the name of the data you want to retrieve. So, if you want data about William Shakespeare, the direct link to the XML file will be http://en.wikipedia.org/wiki/Special:Export/William_Shakespeare.

Knowing all this, it’s now simple to write a program to deal with the set of data provided by Wikipedia. The program looks something like this (and I’ll only give you the important part of the code, I’m sure you know where to put it):

private void button1_Click(object sender, EventArgs e)

{

System.Net.HttpWebRequest webRequest = (HttpWebRequest)System.Net.WebRequest.Create("http://en.wikipedia.org/wiki/Special:Export/William_Shakespeare");

webRequest.Credentials = System.Net.CredentialCache.DefaultCredentials;

webRequest.Accept = "text/xml";

try

{

System.Net.HttpWebResponse webResponse = (HttpWebResponse)webRequest.GetResponse();

System.IO.Stream responseStream = webResponse.GetResponseStream();

System.Xml.XmlReader reader = new XmlTextReader(responseStream);

String NS = "http://www.mediawiki.org/xml/export-0.3/";

XPathDocument doc = new XPathDocument(reader);

reader.Close();

webResponse.Close();

XPathNavigator myXPahtNavigator = doc.CreateNavigator();

XPathNodeIterator nodesText = myXPahtNavigator.SelectDescendants("text", NS, false);

while (nodesText.MoveNext())

textBox1.Text = nodesText.Current.InnerXml + " ";

}

catch (Exception ex)

{

textBox1.Text = ex.ToString();

}

}

Lucian's weblog : Retrieve data from Wikipedia using C#

More on What is Astoria

On to "What is Astoria?"

Astoria is the cloudiest city in North America. Astoria is also the codename for an incubation project started some months ago attempting to answer the following questions: if you could provide a dead-simple way of programming against a relational data store that resides on the internet, what should the programming model look like? Could it be simpler than SOAP-based data access programming?

Microsoft Codename "Astoria": Data Services for the Web - Alex Barnett blog

Pablo Castro's blog - death of a database

Pablo offers a great distinction of a data service vs. a database. 

"...in a service you'll want the schema to be optimized for its target use, so semantics tend to drive it. In a database schema will be about data organization and performance."

A service is a different beast in this sense as well. In a service you just put the data "up there". The service will choose the appropriate physical organization for the data, regardless of the visible service schema. (for example, in the Astoria experimental online service you describe your data as entities and associations and the system figures out a logical/physical schema to support it, along with a mapping to translate between them). The system supporting the service may or may not use a relational database, or even a database at all (there are many large-scale storage systems that use other models instead of traditional relational to avoid the impact of global metadata and the complexity of partitioning highly structured schemas for scale-out).

Pablo Castro's blog

Project Astoria Team Blog : Transparency in the design process

Excellent article around the design processes one team at Microsoft uses. 

How transparent is transparent? I want to be completely clear about the scope of the information we are sharing. One of the things we need to learn both from the Microsoft side and from the community side is whether the model works within a practical set of restrictions. We would post as much of our discussions as it is practically possible. However, we have to make sure we don’t compromise the interests of Microsoft as a company. There are certain things that can range from ideas to specific implementation details that we could consider trade secrets, high-value Microsoft intellectual property or something along those lines. It *will* happen that in some cases we will not discuss a topic publicly, either for a certain term (e.g. until a proper IP protection mechanism is in place) or until we ship or ever. This is nothing new, but I haven’t seen folks from large companies discuss this explicitly before, so I wanted to make sure it is clear here.

Project Astoria Team Blog : Transparency in the design process

Passed 70-445 BI Exam

 When the beta exams for Business Intelligence showed up in January, I immediately signed up, wrote both of them, and found out after 4 months that I failed both. :)  Considering I didn't have any time to study (I was on a heavy client engagement), didn't have a lot of knowledge around the data mining aspects of Analysis Services, whipped through the questions at light speed, and ate a big lunch beforehand, I was more surprised that I didn't fall asleep than failing.  Plus, since they were beta, I'm sure some of my answers were correct and it was the questions that were wrong!

Today, after putting a bit of effort in, I passed 70-445, the implementation & maintenance exam with flying colors.  The betas were brutally long - this one didn't seem half as bad, though as usual the test computer was slow and there was a bunch of outside distractions & banging ductwork in the room. 

My studying strategy:

1. Get the outline from MS's site.
2. Export it to Excel and select the items I'm not familiar with.
3. Bring them into One Note.
4. Create tabs for each item to expand on.
5. Cross them out as I get the research.
6. Google 70-445, 70-445 filetype:ppt (pdf, doc, etc.) to get things like course outlines & demo exams.
7. Search Blogsearch & Technorati.
8. Search for the specific items I'm not familiar with using the same wording as the ms outline.
9. Read the books.

My pretesting strategy:

1. Always write the exam on a Friday at 1:15pm (a happy day!)
2. Have a light lunch.
3. Have a coffee and a quick jog around the block.
4. Show up a bit early.

My testing strategy: 

1. Don't spend more than 1 -2 minutes per question, mark it for later. 
2. When in doubt, pick the answer that matches the text of the question, or is the opposite of the question, or C.
3. Watch for the yahoo question writers who stick in those trick questions involving percentage calculations or not logic.

Here's one statistic that blew me away.  I didn't realize that BI was such a niche.  There have been only 71 MCITP + BI certifications since June.  Perhaps because the exams are only a couple of months old?  Next will be the BI Requirements & Design exam 70-446.  Sounds like case studies to me....

The Microsoft Certified Professionals (MCP) program was established in 1992. To date, more than 2 million people have achieved Microsoft Certification worldwide.

Based on customer feedback, we have updated this list to include both the credential and the number of professionals on each release or certification. Because the MCP program is constantly growing, you may not see your most recent certification listed. We will add accurate data as it becomes publicly available. These numbers are current as of June 27, 2007.

Source: Number of Microsoft Certified Professionals Worldwide

Shailan - Data for the Masses : Online Training for Performance Point Server

 

Online training courses available fro Performance Point Server

Comprehensive online training can be accessed through the links below. Microsoft Office Live Meeting must be installed before viewing any of the training sessions. If Live Meeting is not installed at the time you register for a training event, the system will direct you to a location where the application can be downloaded.

To gain access to the training sessions, click the following links. After you navigate to a site, you must register for the event; the system will send you an e-mail message with a link to the training session, which you then view in Microsoft Windows Media Player.

Monitoring

• Microsoft Office Business Scorecard Manager 2005: Introduction
• Microsoft Office Business Scorecard Manager 2005: Scorecard Builder Fundamentals
• Microsoft Office Business Scorecard Manager 2005: Scorecard Builder Advanced
• Microsoft Office Business Scorecard Manager 2005: Report Views
• Microsoft Office Business Scorecard Manager 2005: Scorecard Views
• Microsoft Office Business Scorecard Manager 2005: Deploying Scorecards to SharePoint SQL Server Reporting Services
• Microsoft Office Business Scorecard Manager 2005: Scorecard Server and Security
• Microsoft Office Business Scorecard Manager 2005: Extensibility

Analytics

• ProClarity Professional Client End User Training Part 1
• ProClarity Professional Client End User Training Part 2
• ProClarity Web Standard Client End User Training
• ProClarity Business Logic Server Training
• PAS Environment and Authoring Training
• ProClarity Dashboard End User Training
• ProClarity Dashboard Administrator Training
• What's New in 6.1
• ProClarity for SharePoint Portal Server Training

Source: Shailan - Data for the Masses : Online Training for Performance Point Server

SQL Server Best Practices

More on the site... 

Technical White Papers

Deep level technical papers on specific SQL Server topics that were tested and validated by SQL Development.

• XML Best Practices for Microsoft SQL Server 2005
• Performance Optimizations for the XML Data Type in SQL Server 2005
• SQL Server 2005 Security Best Practices - Operational and Administrative Tasks
• SQL Server 2005 Full-Text Queries on Large Catalogs: Lessons Learned
• Scale-Out Querying with Analysis Services
• SQL Server 2005 Analysis Services Processing Best Practices
• Predeployment I/O Best Practices
• Partial Database Availability
• Comparing Tables Organized with Clustered Indexes versus Heaps
• SQL Server 2005 Deployment Guidance for Web Hosting Environments
• Resolving Common Connectivity Issues in SQL Server 2005 Analysis Services Connectivity Scenarios
• Implementing Application Failover with Database Mirroring
• OLAP Design Best Practices for Analysis Services 2005
• DBCC SHOWCONTIG Improvements and Comparison between SQL Server 2000 and SQL Server 2005
• TEMPDB Capacity Planning and Concurrency Considerations for Index Create and Rebuild
• Loading Bulk Data into a Partitioned Table
• Database Mirroring Best Practices and Performance Considerations
• SQL Server 2005 Performance Tuning using Waits and Queues
• Analysis Services 2005 Performance Guide
• SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Performance, and Scalability
• Microsoft SQL Server 2005 Tuning Tips for PeopleSoft 8.x
• Troubleshooting Performance Problems in SQL Server 2005

---

SQL Server Best Practices Toolbox

• Scripts and Tools for Performance Tuning and Troubleshooting SQL Server 2005
  • Buffer Cache
  • CPU and Optimization
  • Indexes and Indexing
  • Input/Output
  • Performance
  • Query Plan Re-Use
  • Retrieving SQL Text and XML Plans
  • SQLOS: Schedulers, Runnable Queue, Waiter List
  • Tempdb
  • Transactions and Locking
  • Wait Statistics
• SQL Server 2005 Best Practices Analyzer
• DMV Stats

---

Top 10 Lists

Summary lists (usually consisting of 10 items) of recommendations, best practices and common issues for specific customer scenarios by the SQL Server Customer Advisory Team.

• Analysis Services Query Performance Top 10 Best Practices
• Top 10 Best Practices for SQL Server Maintenance for SAP
• Top 10 Hidden Gems in SQL Server 2005
• Top 10 SQL Server 2005 Performance Issues for Data Warehouse and Reporting Applications
• OLTP Top 6 Performance Issues for OLTP Applications
• Storage Top 10 Best Practices

---

Best Practices in SQL Server Books Online

• Best Practices for Replication Administration
• Replication Security Best Practices
• Best Practices for Recovering a Database to a Specific Recovery Point

Source: SQL Server Best Practices

Microsoft SharePoint Products and Technologies Team Blog : Microsoft SQL Server 2005 SP2 Reporting Services integration with WSS 3.0 and MOSS 2007

 

 

SQL Server 2005 Service Pack 2 (SP2), which will be released any minute now :-), enables deep integration between Reporting Services and SharePoint technologies (Windows SharePoint Services 3.0 and Office SharePoint Server 2007). This integration enables an end-user to view and manage reports completely from within a SharePoint environment. The following is an excerpt from the upcoming 2007 Microsoft Office System Business Intelligence Integration with SQL Server 2005 whitepaper. [Update March 17: Added the hyperlink for the whitepaper, which is 1.5 MB in size and in Word 2007 .DOCX format.]

Source: Microsoft SharePoint Products and Technologies Team Blog : Microsoft SQL Server 2005 SP2 Reporting Services integration with WSS 3.0 and MOSS 2007

SQL Server 2008 Improvement Pillars

I'm in the middle of a webchat on SQL 2008 and this chart looks useful for tracking it's progress...

The chart (linked)below depicts significant improvements coming online with each CTP. Below, you will see the 10 new improvements in the July CTP. Click on any improvement in the chart below to explore and learn more about it.  Want to find out more about the improvement groups?  Each group will open to an informative video on what each means.

Link to SQL Server 2008 Improvement Pillars

OneNote Web Exporter - Home

 

Project Description
Plug-in to export your OneNote 2007 notebooks as an interactive web site.

Source: OneNote Web Exporter - Home

Coming out of the cloud

 

Could this be MS's answer to Amazon EC2?

Microsoft SoftGrid^® Application Virtualization

Microsoft SoftGrid^® Application Virtualization is the only virtualization solution on the market to deliver applications that are never installed, yet securely follow users anywhere, on demand. It dramatically improves IT efficiencies, enables much greater business agility and a superior end-user desktop experience.

Source: Microsoft SoftGrid® Application Virtualization

Run IT on a Virtual Hard Disk

How soon before MS releases it's own Amazon-style Cloud initiative with VPCs?

 

Using the power of virtualization, you can now quickly evaluate Microsoft and partner solutions through a series of pre-configured Virtual Hard Disks (VHDs). You can download the VHDs and evaluate them for free in your own environment without the need for dedicated servers or complex installations. Start now by selecting a lab from the VHD catalog below.

Source: Run IT on a Virtual Hard Disk

Sharing OneNote

 The example is from a BI course, however it can apply to any onenote sharing system.

1. Create a folder on your C drive called Ranger.

2. Create a folder under the C:\Ranger for: Notes, Labs (for the lab instructions), and Sessions

3. Under the notes folder create a folder with your name

4. Create a OneNote 2007 notebook in that folder.

5. In the OneNote2007 folder, create a section per day (Day 1, Day 2...Day 20)

6. Create a page under each day for each session.

7. Then "print to OneNote" or copy the Presentation for each session in the session page.

8. Write your notes on each page along with the presentation. (THERE IS NO WAY YOU WILL RETAIN ALL YOU ARE TAUGHT WITHOUT THESE STUDY AIDS).

9. Under the Sessions folder create a folder per session and copy in the content from the Ranger SharePoint site for that session. (This gives you a logical place to put presentations, links, whitepapers and other content you find on the web).

10. Use FolderShare (man, I love this tool), or Groove, and setup a sync with your study group so you are all in sync and you can leverage each other's notes and content.

Source: ...more notes from the field

Useful links from Dan's blog

 

SQL ISV blog: http://blogs.msdn.com/sqlisv

SQL CAT Blog: http://blogs.msdn.com/sqlcat

Best Practices Site: http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.mspx

SQL Server AlwaysOn Partner program: http://www.microsoft.com/sql/alwayson/default.mspx

SQL Server Urban Legends: http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx

SQL Server 2000 I/O Basics whitepaper: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx

Disk Subsystem Performance Analysis for Windows: http://www.microsoft.com/whdc/device/storage/subsys_perf.mspx

Microsoft Windows Clustering: Storage Area Networks: http://www.microsoft.com/windowsserver2003/techinfo/overview/san.mspx

Windows Server System Storage Home: http://www.microsoft.com/windowsserversystem/storage/default.mspx

Microsoft Storage Technologies – Multipath I/O: http://www.microsoft.com/windowsserversystem/storage/technologies/mpio/default.mspx

Windows 2003 Storport Driver: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/storage/hh/storage/portdg_88563b10-e292-48f3-92de-65bf90530455.xml.asp

Virtual Device Interface Specification: http://www.microsoft.com/downloads/details.aspx?FamilyID=416f8a51-65a3-4e8e-a4c8-adfe15e850fc&DisplayLang=en

SQL Server Consolidation on the 64-Bit Platform: http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/64bitconsolidation.mspx

SQL Server Consolidation on the 32-Bit Platform using a Clustered Environment: http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/32bitconsolidation.mspx

INF: Support for Network Database Files: http://support.microsoft.com/default.aspx?scid=kb;en-us;304261

824190 Troubleshooting Storage Area Network (SAN) Issues: http://support.microsoft.com/?id=824190

304415 Support for Multiple Clusters Attached to the Same SAN Device: http://support.microsoft.com/?id=304415

280297 How to Configure Volume Mount Points on a Clustered Server: http://support.microsoft.com/?id=280297

819546 INF: SQL Server support for mounted volumes: http://support.microsoft.com/?id=819546

304736 How to Extend the Partition of a Cluster Shared Disk: http://support.microsoft.com/?id=304736

325590 How to Use Diskpart.exe to Extend a Data Volume: http://support.microsoft.com/?id=325590

Updated Books Online for SQL Server 2005: http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Shared Scalable Database: http://support.microsoft.com/?kbid=910378

Sunil gave us all we need to know and more on the TEMPDB. Check out the whitepaper he references: http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

In addition, do not forget to review Kalen Delaney's book as she goes into detail on the TempDB in her book on the Database Storage Engine.

Source: ...more notes from the field

Acropolis Parts, Views, and xaml files. - MSDN Forums

 

Web Parts for Windows?  Here's Acropolis. 

Acropolis provides a mechanism which allows you to cleanly separate your business logic from any associated UI. The Part component contains the business logic, while the View contains the user interface for the part. Both of these are technically components in that they exist as separate pieces of functionality and in that you can replace either one of them as long as you implement the right interfaces.

But the Part component is somewhat different in that it is the main component that gets composed into the application. You can have Parts that have zero, one or more Views, but you can't have a View without an underlying part. If you did want some UI but didn't want to split out the business logic into a separate component, you would build a standard control.

This separation between business logic and UI exists all the way up to the main application itself. You can think of the entire application as a hierarchy of Parts and Views. The Application.xaml file defines the root level (i.e. application level) business logic for the application. The Window1.xaml file defines the UI for the application (i.e. the application's main window or Shell). I have seen people rename Window1.xaml to Shell.xaml but the Application.xaml file usually keeps that name. You can't really delete them because you need something to contain the other Parts and Views in the application.

Source: Acropolis Parts, Views, and xaml files. - MSDN Forums

Why every program should be like One Note

 Two things every program should have - Instant on/instant off automatic load/save and unlimited undo/history.

A useful feature for complex sites would be the ability to click to view the path you took through a web site (history) and recreate it the next time you're there (Journal).

Solution: Archy never loses your work. This shouldn't be a groundbreaking innovation in computer design, but it is. You never have to save because it's done for you automatically. Your data is stored in such a way that if your computer crashes, your information will still be there the next time you start Archy up.

You don't have to worry if you make a mistake. The UNDO command can reverse your steps all the way back to the first thing you ever did on Archy. Quitting Archy has no effect on UNDO's elephantine memory.

Source: Core Principles - Raskin Center

Conceptual Design Ideas

 Interface Concepts provides a good summary of conceptual design concepts:

The process of conceptual design involves a set of steps for translating requirements into a user interface design. The process begins by getting at the core of an application--the central concept--and proceeds by organizing the functionality from the users' point of view. Along the way, a deeper understanding of users and their requirements is developed. The result is an outline or model of the user interface that may be further developed during the detailed user interface design phase.

The conceptual design process involves the following steps:

1. Define a central concept.
   A concise statement of what the application is and what it is not. It clearly defines the boundaries of the application and characterizes the overall users' view of the application.
2. Describe user roles and their requirements.
   A list of who the target users are, what their roles are in their use of the application, and what is important to them--such as getting work done quickly, being in charge, and feeling successful.
3. Define and prioritize measurable objectives and constraints.
   Objectives for the user interface are the designers' intentions such as:
   • to reduce repetitive tasks,
   • to have users feel in control, and
   • to provide satisfactory feedback on results.
   
   Operational definitions specify how the design will be measured against the objectives, for example, in usability testing. Constraints define the design space within which the objectives may be achieved, such as display resolution, response time, and availability of a pointing device.
4. Design the user's object model.
   A table of all objects the user needs along with their attributes, actions, contents, and relationships among objects.
5. Design the user's task model.
   A list of all tasks the user needs to perform with procedures on how to perform each task using the application.
6. Synthesize a user interface model.
   The user interface model organizes the functionality according to the object and task models. This is a rough outline of the user interface that guides the detailed design phase.
7. Evaluate results against the objectives.
   Various evaluation methods, such as heuristic evaluation and usability testing are selected to measure how well the objectives have been achieved..

Source: Interface Concepts

Free SharePoint Web Parts (3rd Party) - The Boiler Room - Mark Kruger, Microsoft SharePoint MVP

 

Free SharePoint Web Parts (3rd Party)

Free SharePoint Web Parts (3rd Party)

• Konrad Brunner - UGS's Web Parts (broken link 8/25)
• Document Library Browser v1.2 (Stramit)
• Sig Weber's Playground of Web Parts
• Jan Tielens' SmartPart
• SharePointCustomization's Webparts
• Jan's Essential Web Parts (Navigation, MyAlerts, Breadcrumbs, WhatsNew)
• Program's Unlimited - Calendar Webpart
• Web Part ToolKit (BlueDogLimited )
• CSeg - SharePoint List Rollup (csegRollUp version 2)
• Image Web Part (FluidNature)
• SmartPart User Controls
• Upload via FrontPage RPC
• SharePoint List Browser/Copier
• What's New Web Part (Fitz)

Source: Free SharePoint Web Parts (3rd Party) - The Boiler Room - Mark Kruger, Microsoft SharePoint MVP

Great set of SharePoint Web Parts - Eli Robillard's World of Blog.

 

Great set of SharePoint Web Parts

UGS contains one of the nicest web part collections I've seen:

Breadcrumbs - Fairly standard.
Connected Page Viewer - More IFrame attributes than the Page Viewer, and works with connected web parts.
Event Rollup - Aggregate calendars from subsites. Nifty!
Flash Movie - Just what it says, clean implementation.
Google Search - Displays results in-place, and can be configured to search a particular site.
Google Translator
Importable WebParts
Outl2TccSyncer - Much potential, the goal is to synchronise Outlook and SharePoint calendars.
Poll - Looks more like what you're used to than the built-in survey.
QuickTime Movie - Just what it says, clean implementation.
RateThisPage - Short and sweet, again more like what you're used to.
Ressource Plan
Site Navigation - Clean display, and many display options (from root, from child, etc.)
TccFeeder
TccRoleSyncer
User Alerts - Lets an administer add, remove or change alerts for any site user. One way to push alerts rather than wait for users to subscribe. 
Welcome - Display personalised welcome messages.
What's new - A concise list of New items on a team site.
World Time - If your users work in different time zones, it helps to show clocks for each.

Source: Great set of SharePoint Web Parts - Eli Robillard's World of Blog.

SQL Server 2005 Best Practices...

 

SQL Server 2005 Best Practices…

30 juillet 2007 | Integration Services, Analysis Services, Reporting Services, SQL Server

Cet article a pour but de regrouper à un même endroit l'ensemble des best practices publiés autour de SQL Server et de la suite BI

Source: SQL Server 2005 Best Practices...

Julie Lerman Blog - Mike Pizzo on LINQ to SQL vs. Entity Framework

 

Here are some highlights from Mike's reply:

• LINQ to SQL is targeted more toward rapidly developing applications against your existing Microsoft SQL Server schema, while the Entity Framework provides object- and storage-layer access to Microsoft SQL Server and 3^rd party databases through a loosely coupled, flexible mapping to existing relational schema.
• LINQ to SQL has features targeting "Rapid Development" against a Microsoft SQL Server database.
• The Entity Framework has features targeting "Enterprise Scenarios". 
• The Entity Framework is more than LINQ to Entities; it includes a "storage layer" that lets you use the same conceptual application model through low-level ADO.NET Data Provider interfaces using Entity SQL, and efficiently stream results as possibly hierarchical/polymorphic DataReaders, saving the overhead of materializing objects for read-only scenarios where there is no additional business logic. 

Source: Julie Lerman Blog - Mike Pizzo on LINQ to SQL vs. Entity Framework

Excel 2007 can now do two-way sync to a SharePoint List

In Excel 2003 you were able to sync amendments both from a Excel 2003 spreadsheet to a SharePoint List and from a SharePoint List to an Excel 2003 spreadsheet. But in Excel 2007 this is no longer the case... Well it was until Microsoft released an Excel 2007 Add-in. This is a must install if you are upgrading to Office 2007 from Office 2003, otherwise your users will cry.

The Excel 2007 Add-in, can be downloaded from: http://www.microsoft.com/downloads/details.aspx?FamilyID=25836e52-1892-4e17-ac08-5df13cfc5295&DisplayLang=en

There is also an accompaning MSDN article "Publishing and Synchronizing Excel 2007 Tables to SharePoint Lists": http://msdn2.microsoft.com/en-us/library/bb462636(office.11).aspx

Creating a SharePoint list from within Excel 2007 is known as exporting an Excel table. In Excel 2003, this was known as publishing an Excel list. Once the table data is placed on the SharePoint site, users can see the Excel 2007 data without having to opening Excel 2007. You can maintain a link between the SharePoint list and the Excel 2007 data, but without the Excel 2007 Add-in, any changes in the Excel 2007 spreadsheet are not reflected in the SharePoint list. Without the Excel 2007 Add-in, you can only synchronize changes in the SharePoint list to the Excel 2007 spreadsheet - but you cannot save the workbook in the new Office Open XML Formats. Instead, to retain the functionality, you need to save the workbook in the Excel 97-2003 file format.

Source: Excel 2007 can now do two-way sync to a SharePoint List

Collaborative Writing Tools And Technology: A Mini-Guide

Google Docs seems to have the most bang for the buck ($0) 

The key features that characterize collaborative writing tools and technologies include:

• File types supported:which are the supported file formats for the files that can be edited collaboratively
• Text chat: the presence of a text chat that users can utilize to communicate while editing
• Revisions:the capability to track all changes made to the original document and go back to older versions
• RSS: support for RSS feeds, allowing users to get real-time notifications when changes are made.
• Email updates:the capability to receive email updating users when there are changes to the documents that are being edited.
• Public/Private:the possibility to set up private or public collaborative editing sessions.
• Max Editors:the max number of editors allowed
• Real-Time - Co-editing:the possibility to collaboratively edit in real-time
• Software/Web-based: the type of collaborative system
• Comments:the possibility to add comments to the document
• Spell check:the availability of a spell checking tool

Source: Collaborative Writing Tools And Technology: A Mini-Guide

Elsasoft SqlSpec - Database Schema Documentation

Generates documentation for SQL schemas and Analysis Services...really well.

SqlSpec

Do you wish you had comprehensive, uniform, and easily navigable documentation for all your databases, regardless of platform?

Are you a developer or DBA that just inherited a huge, complex database with no supporting docs, and don't know where to start learning how it's put together?

Are you an independent contractor working with databases? Your clients would LOVE you if you could provide comprehensive documentation about the databases you build.

Are you a manager who is tired of your developers making changes to databases on live servers and not updating the corresponding spec?

SqlSpec will make sense of it all and generate easy-to-read docs for you for any database on the following platforms:

• SQL Server 2000/2005
• Oracle 9i and above
• DB2 8.2^* and above
• Sybase ASE 12.0 and above
• PostgreSQL 8.0^* and above
• MySQL 5.0 and above
• Analysis Server 2005
• Access 97 and above

Read more about the many features of SqlSpec here.

Source: Elsasoft SqlSpec - Database Schema Documentation

MDX to SSIS

 

I've seen two techniques described for getting the results of an MDX query into an SSIS data stream. 1 - Use the OLE DB Source Adapter and ignore the whiny warning. This seems to work when you Preview, but crashes SSIS (even SP2) when you run the job. 2 - Use a linked server and an OpenQuery command. This works but has a lot of overhead. My colleague Pablo Guzman and I came up with a pretty slick way to do it. - Create a new ADO.Net Connection and from the .Net Providers\SQLClient Data Providers list choose the OLE DB Provider for AS9.0. - Create a DataReader Source adapter connected to the new AS connection manager. - Put the MDX into the SQLCommand property--be sure to map the external and output columns. This obviously flattens the cell set, but it doesn't limit what you put on the axes--probably a good idea to have the columns end up static. The DataReader Source adapter turns all the columns into DT_NTEXT, which you then have to convert to DT_WSTR to be able to convert to anything else, but that's standard SSIS data type manipulation. Seems a lot slicker than creating a linked server. Cheers, Reed

Source: Hitachi Consulting BI Blog :

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