Thursday, May 24, 2007

Creating a Heatmap in Reporting Services

 Ever since I found WinDirStat, the best tool for finding large files on your hard disk and cleaning up data, Treemaps and Heatmaps have been of interest.

Here is one way to replicate this functionality in Reporting Services.

I generated the colors by finding the darkest acceptable color (not quite red) that black text was still readable on top of, then creating a gradient using Paint.NET fading from this color to white, then finally using a "pixelate" effect in Paint.NET to remove the gradient smoothness and get definite colors. I'm sure you can think of better ways to do this!

Source: Thomas Williams :

TagJag! : Home

Searches feeds for keywords and generates a live OPML file. 

TagJag!

Source: TagJag! : Home

BI Blogs

 

Our Blogosphere
• Cindi Howson's BI Scorecard
• The Brain Food Blogger
• Tony Byrne's CMS TrendWatch
• Third Eye View, by Rajan Chandras
• Breakthrough Analysis by Seth Grimes
• In Context by Doug Henschen
• As the Enterprise Develops, by Nelson King
• David Linthicum on Changing the Enterprise
• Alan Pelz-Sharpe's ECM TrendWatch
• Addicted to BI, by Neil Raden
• Peak Performance by Mark Smith
• Product Maven
• Bruce Silver's BPMS Watch
• David Stodder's Mission Intelligence

Source: http://www.intelligententerprise.com/movabletype/blog/chowson.html

Home :: Revelation Technologies

 

Dali is the intelligent object-relational mapper and data access layer that can drastically reduce the amount of database code you write. Most developers experience a 50-80% reduction in data access code. Less code means less errors and less time.

Source: Home :: Revelation Technologies

:: BIScorecard® ::

 

Spreadsheet Evaluation
This evaluation discusses criteria to consider when evaluating BI suites Office add-ins and export capabilities.

The BI Market
Business intelligence and the sub markets defined. Lists vendor products per sub market, serving as a necessary guide for comparing like-for-like products and seeing the breadth of individual vendor offerings.

Blank BIScorecard®
This spreadsheet provides a summary overview of vendor scores per major functional area plus a detailed list of 100+ criteria to consider when selecting or standardizing on a BI product.

Flying High with BI
How innovation and a maturing market will finally allow BI to take off in many organizations.
Enterprise Business Intelligence: Strategies and Technologies for Deploying BI on an Enterprise Scale
Here's the Deal: Negotiating BI contracts
The BIScorecard® review series in Intelligent Enterprise
Although the product scores here are now out of date, the series provides you with a framework for evaluating BI suites and a sample of the approach used on BIScorecard® for current versions of BI suites.

Source: :: BIScorecard® ::

Certified Business Intelligence Professional (CBIP): TDWI

 

Certified Business Intelligence Professional (CBIP)

TDWI, the leading association for business intelligence and data warehousing professionals, offers the industry's most comprehensive certification program available: the Certified Business Intelligence Professional (CBIP). CBIP, a true test-based certification program, is offered in five key specialties for Business Intelligence success: Leadership & Management, Business Analytics, Data Analysis & Design, Data Integration, and Administration & Technology.

Becoming certified requires that you pass three exams; the Core exam, the Data Warehousing exam, and one specialty exam. Passing the CBIP exams is based on your knowledge and experience within your area of specialization. Prior study is not required although it is recommended to update or expand your current knowledge.

Levels of Certification

Practitioner Level: CBIP certification at the Practitioner Level is awarded to professionals who score above 50 percent on each of three exams. Practitioner Level certification demonstrates a working knowledge of concepts, skills, and techniques within a specialty, as well as the ability to assume leadership responsibilities in the area of specialization at the project level.

Mastery Level: CBIP certification at the Mastery Level is awarded to professionals who score above 70 percent on each of three exams. Mastery Level certification demonstrates that concepts, skills, and best practices have been mastered within a specialty, as well as the ability to lead a team effectively at the project and program levels, and the skills to mentor others.

Source: Certified Business Intelligence Professional (CBIP): TDWI

On BI Bake Offs and Vaporware | The Intelligent Enterprise Blog

Predictive Analytics - Something worth looking at (see Monte Carlo Analysis) 

During this last course, attendees also threw the vendors a wild card in which they got to vote for a new demo topic. The vendors only learned what that topic would be during the lunch break. Top of the list: predictive analytics (beating out other hot topics such as performance management, production reporting, and BI search). Kudos to each of the vendors for speaking about - and for some, demoing - these capabilities literally at the last minute!

Source: On BI Bake Offs and Vaporware | The Intelligent Enterprise Blog

Questions and Answers about Using Local Cubes to Improve Browsing Speed Performance

 

How can I create local cube files?
There are two ways to create local cube files in Analysis Services 2005. The easier way is to use a command called CREATE GLOBAL CUBE. A more complex (and more flexible) way is to use Analysis Services Scripting Language (ASSL). CubeSlice 9 supports two forms of ASSL – one that uses the Analysis Server cube as the data source and the other that uses the relational data source. The CubeSlice Relational ASSL usually offers more flexibility in creating local cube files.
For more information on the creation of local cube files, download the document Using Local Cubes with Microsoft SQL Server 2005 Analysis Services from this website.

Source: Questions and Answers about Using Local Cubes to Improve Browsing Speed Performance

Wednesday, May 23, 2007

Tuesday, May 22, 2007

Daily Dose of Excel » Automation

 

Nat asks:

How do I refer to a table on the web? How to I refer to specific colums/row/cells in a web table?

Here's an example that may help you out. It goes to Yahoo's finance page (where I knew there would be a table) and finds the table with the mortgage rate. Then it finds the row for 15 year mortgages and writes the whole row to an Excel sheet. MSDN has help for these HTML objects here. Look for 'document', 'table', and 'td'.

Source: Daily Dose of Excel » Automation

Wiley::MCTS: Microsoft SQL Server 2005 Implementation and Maintenance Study Guide: Exam 70-431

 

Implementation and Maintenance exam (70-431) is the first stop for everyone entering this new certification track, and serves as both a single exam certification as well as the entry exam for the MCITP-level certifications. This book provides an introduction to the development and administrative aspects of SQL Server 2005 and features practical guidance for all aspects of the exam.

Source: Wiley::MCTS: Microsoft SQL Server 2005 Implementation and Maintenance Study Guide: Exam 70-431

Download details: Microsoft SharedView Beta

 

Microsoft SharedView Beta

Brief Description

Microsoft SharedView is a fast and easy way to share documents and screen views with small groups of friends or coworkers; anytime, anywhere. Use SharedView to put your heads together and collaborate.

Source: Download details: Microsoft SharedView Beta

Download details: Teradata-Microsoft White Paper - AJI and OLAP.doc

 

Whitepaper - Improve your OLAP Environment with Microsoft and Teradata

Brief Description

This paper describes how you can leverage Microsoft SQL Server Analysis Services and Teradata technologies to improve your analytical OLAP application environment, specifically relational (ROLAP) type solution.

Source: Download details: Teradata-Microsoft White Paper - AJI and OLAP.doc

Download details: Centralizing and Sharing Business Intelligence

 

Brief Description

myBI enables users to view reports, scorecards, key performance indicators (KPIs), multi-dimensional analytics, and other metrics without having to move from one reporting site to another.

Source: Download details: Centralizing and Sharing Business Intelligence

Friday, May 18, 2007

Bill the Billionaire Buys Avenue for $6 bills.

 

..today, Microsoft announced the purchase of aQuantive, a 10-year-old, publicly traded digital marketing company in an all cash deal worth approximately $6 billion -- the biggest acquisition in Microsoft history.

So who is aQuantive? The Seattle-based company owns Avenue A | Razorfish, Atlas and DRIVEpm, which Microsoft calls "one of the industry’s most successful families of digital marketing service and technology companies." The company was originally founded in 1997 as Avenue A, and went public in 2000 under that name. It became aQuantive in 2003.

Source: Microsoft Gets Its Ad Network#comments#comments

Tuesday, May 15, 2007

KartOO visual meta search engine

 

Great site for finding patterns, related sites and referrers to a web site. 

Link to KartOO visual meta search engine

Elizabeth Vitt - Analysis Services - Importing Perfmon Data into Profiler

 

One of the greatest enhancements in Analysis Services 2005 is the ability to use SQL Server Profiler to troubleshoot performance issues during querying and processing. Even cooler is the ability to import Perfmon data into Profiler so you can analyze system and Analysis Services performance counters at every step of querying and processing.

Source: Elizabeth Vitt - Analysis Services - Importing Perfmon Data into Profiler

Automating Analysis Services Deployment

 

Automating build of Analysis Services Projects

As you may be aware, an Analysis Services project consists of the following files:

  • A project file (*.dwProj)
  • One or more  Data sources (*.ds)
  • One or more Data source views (*.dsv)
  • A database file (*.database)
  • One or more cube files (*.cube) with their partition (*.partitions)
  • One or more dimension files (*.dim)

You can right click the project in Visual Studio and choose "build". What happens now seems to be a lot of called to DLL files executed by the integration between Visual Studio and the Analysis Services. The output of this build is the bin folder in your project directory. This folder contains a database file (*.asdatabase). The .asdatabase file is the one you want - you will see why later.

Unlike other projects - which you can build using their proper compiler (Example: CSC.EXE, VBC.EXE) - Analysis Services does not seem to have a compiler executable.

You could be tempted to run msbuild. Unlike most temptation this will result in disappointment - msbuild does not understand Analysis Services files.

The only way I have found to automate the building of Analysis Services projects is to invoke Visual Studio from the command line. Say you have a project called: MyAsProject.dwProj. To build it and obtain the asdatabase file you execute the following:

devenv MyAsProject.dwProj /rebuild Relase /project MyAsProject

So far so good - now you have automated the createion of the .asdatabase file.

You are not done yet. The asdatabase file provides the input to the Analysis Services Deployment Wizard. The spell cast by this wizard allows you to transform an asdatabase filen into an XML/A file. This XML/A file can be run on your deployment target - which will create the cube database.

From the command line the Deployment Wizard can be run in two modes.

The answer mode:

Microsoft.AnalysisServices.Deployment.exe /a

In this mode, you get to choose all the server specific settings and write them back to your bin directory. Check out the BOL documentation to find out exactly how this is implemented (You will find no defense for the naming of the executable in the documentation)

Once you have your answers stored (which you will only do once) you want to automate the creation of the XML/A file. Execute:

Microsoft.AnalysisServices.Deployment.exe MyAsProject.asdatabase /d /o:MyProject.xmla

Voila! You have your xmla file, which you can copy to your deployment target and run. By the way. xmla files can be run from the command line using ascmd.exe which you can find here: http://msdn2.microsoft.com/en-us/library/ms365187....

Source: Thomas Kejsers Blog

Wednesday, May 09, 2007

Microsoft SQL Server 2005, 2000 and 7.0 Builds

 

What version of SQL Server do I have?

This unofficial build chart lists all of the known KB articles, hotfixes and other builds of MS SQL Server 2005, 2000 and 7.0 that have been released.

Source: Microsoft SQL Server 2005, 2000 and 7.0 Builds

How to Get Reporting Services & Vista to Play together...

 

After several weeks of frustration I finally got Report Manager to work with Vista.  I guess it was too much to expect of Microsoft to actually have their own software install correctly on their own OS.  For anyone else having problems this is what I had to do:

1)  In Configure Report Server|Web Service Idenity - add both Server & Manager to the Classic .Net AppPool.

2)  Even though I am the only user account on the computer I'm developing on and log in as administrator, that, apparently isn't good enough for Vista.   (Apparently in the world of Vista NOONE is an adminstrator with full rights - great big atta boy to MS).  I had to go to the control panel and add my user account and the admin account (for good measure) to the IIS user grooup.

3) Go to add/remove programs and install EVERY IIS option; again, Microsoft won't do it for you.

4) Turn off that silly user control dohicki that Apple makes fun of in their commercials (Earth to Microsoft - that thingie IS rather stupid; how about finding a way to stop hackers without crippling your OS?)

End result: I can now use Report Manager on Vista Ultimate 64bit/SQL Svr 64bit with only a few minor glitches thanks to some clues I picked up in this forum and some hair pulling sluthing.

Source: SSRS and Vista - MSDN Forums

Tuesday, May 08, 2007

Greg Galloway is Testing cube performance improvements

 

Conclusions

  • It is clear that ProcessAdd is worth the extra effort if you need to further optimize ProcessUpdate dimension processing.
  • It is clear that AttributeHierarchyOrdered=false on high cardinality attributes makes a significant difference to dimension processing. Test case H shows that AttributeHierarchyOrdered=false on high cardinality attributes can double performance of ProcessAdd.
  • ProcessUpdate is by far the most expensive option available. It is so much more expensive than ProcessFull that you should definitely verify whether processing scheme #1 actually performs slower than processing scheme #2 on your dataset.

Source: Greg Galloway

Windows driver examples

No more copying large ISO files around? 

HttpDisk is a virtual disk driver for Windows that uses HTTP to mount disk or CD images from a web server.
Latest news: Support for persistent connections to improve performance.

Source: Windows driver examples

Plus, a virtual disk drive file, basically TrueCrypt without the encryption.  Useful for maintaining drive letters between systems, and possibly as a siloed work area for source control systems.

FileDisk is a virtual disk driver for Windows NT/2000/XP that uses one or more files to emulate physical disks. A console application is included that let you dynamically mount and unmount files. An example of use for this driver is if you have made plans spending the weekend writing an RAID driver for NT but find you are short of disks. FileDisk can also use CD-images.

Monday, May 07, 2007

Info about SSAS - Home

 

This website is designed for the developers and administrators who work with Microsoft SQL Server Analysis Services 2005 (SSAS). Here you will be able to find latest news, MDX FAQs, OLAP design FAQs, links to articles, BI blogs, webcasts, forums, job postings, products and any other information that is related to SSAS. As the registered user you can submit your own links or articles. They will appear on our site after website administrators approval (usually the same day). We also encourage you to subscribe to our newsletter that will keep you informed about latest additions to our website.

Source: Info about SSAS - Home

Thursday, April 26, 2007

Powershell & MDX

Darren Gosbell talks about Powershell & MDX 

I have been experimenting a bit more recently to see what I can do with Powershell and Analysis Services. The following small script executes an MDX query using an XMLA connection. I have borrowed the xsl files from one of Chris Harrington's excellent ThinOlap samples.

  1 [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Xmla") 
2 [Microsoft.AnalysisServices.xmla.xmlaclient]$xmlac = new-Object Microsoft.AnalysisServices.Xmla.XmlaClient
3 $xmlac.Connect("localhost\sql05")
4 write-output "Connected to server"
5 $XmlResult = "" # Initialise the variable so that it can be passed by [ref]
6 $qry = "SELECT {Measures.[Internet Sales Amount], Measures.[Internet Order Quantity]} ON COLUMNS, Product.Category.Members ON ROWS FROM [Adventure Works]"
7 $props = "<PropertyList><Catalog>Adventure Works DW</Catalog><Format>Native</Format></PropertyList>"
8 $xmlac.executestatement($qry,[ref] $xmlresult,0,$props,"")
9
10 write-Output "Query Executed"
11 $x = [xml]$xmlresult #cast the string result to an xml document
12
13 [System.Xml.Xsl.XslCompiledTransform] $xsl = new-Object System.Xml.Xsl.XslCompiledTransform
14 $xsl.Load("c:\data\xamd.xsl")
15 [System.Xml.XmlWriterSettings] $xws = new-Object system.Xml.XmlWriterSettings
16 $xws.ConformanceLevel = 'Auto'
17 [System.Xml.XmlWriter] $xw = [System.Xml.XmlWriter]::Create("c:\data\output.htm",$xws)
18 $xsl.Transform($x.CreateNavigator(),$xw)
19
20 # Cleaning up
21 $xw.Close
22 $xmlac.Disconnect()
23 $xmlresult = ""
24 $x = ""
25 write-Output "Operation Complete"

Now this is interesting and produces nicely formatted html results, however there are many different ways of running MDX queries against Analysis Services. One of the things I found interesting was the possiblity of running some of the XMLA discover commands.

Consider the following script:

  1 [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Xmla") 
2 [Microsoft.AnalysisServices.xmla.xmlaclient]$xmlac = new-object Microsoft.AnalysisServices.Xmla.XmlaClient
3 $xmlac.Connect("localhost\sql05")
4 $XmlResult = "" # Initialise the variable so that it can be passed by [ref]
5 $xmlac.Discover("DISCOVER_CONNECTIONS", "", "", [ref] $XMLResult, 0, 0, 1)
6 $x = [xml]$xmlresult
7 $xmlac.Disconnect()
8 #output discovered connections
9 $x.return.root.row |Format-Table CONNECTION_ID , CONNECTION_USER_NAME, CONNECTION_LAST_COMMAND_START_TIME

Source: Darren Gosbell [MVP] - Random Procrastination

Index of /~mlearn/databases

A whack of useful datasets from ages ago, but some are up to date.

 

How about Poker Hand Training?

http://www.ics.uci.edu/~mlearn/MLRepository.html

Link to Index of /~mlearn/databases

John C. Hancock's blog - Facebook data mining

 

These techniques involve applying complex algorithms to large sets of data.  What we need is a platform that can store the data, give us a framework to implement the required logic, and a flexible way of presenting the data to users.  SQL 2005 has all the necessary components, so I've attached a paper that describes how to start tackling this area.  The paper walks you through creating the data mining model using the Microsoft Association Rules algorithm, developing the stored procedures using C#, and then reporting on the data mining model.

Source: John C. Hancock's blog

Analysis Services Stored Procedure Project

 

  • AsymmetricSet - AsymmetricSet
  • CellTimings - TimeToCalculate
  • CubeInfo - GetCubeLastProcessedDate
  • EfficientToDate - GetEfficientPeriodsToDateSet, GetMostGranularHierarchyCurrentMember
  • FindCurrentMembers - FindCurrentMember, FindCurrentMemberVerbose
  • Multiplication - Multiply
  • SetOperations - Order, ReverseSet, RandomSample, InverseHierarchility, AsymmetricDrillDown
  • StringFilters - RegExFilter, Like
  • ListFunctions - ListFunctions
  • Parallel - Parallel
  • LinkMember - HierarchyLinkMember, LevelLinkMember
  • ClusterNaming - AutoNameClusters, DistinguishingCharacteristicsForClusters
  • Partition - CreatePartitions
  • XmlaDiscover - Discover, ClearCache, Cancel
  • MemoryUsage - SnapshotMemoryUsageTotals
  • Source: Analysis Services Stored Procedure Project

    Tuesday, April 24, 2007

    Cubegeek: Essbase going away?

     Some interesting speculation around the roadmap for Oracle/Hyperion merger:

    That's going to take years, and lots of extentions to OWB, but systems like Star Analytics' SIS could facilitate all that. Now that Oracle owns Essbase, we'll find out price/performance wise if that's a good direction to head, but I'm convinced that the two companies are going with the market leading technologies as a guide. I don't know if Express is behind Oracle BI EE, but Kurian was talking about Essbase as a source on the same level as Hyperion IR and Oracle BI EE. Meaning that pure Essbase apps may be just considered point BI solutions which don't play a major role in the EPMS world going forward.

    Source: Cubegeek: Hyperion Solutions Conference General Session

    Chris Webb's BI Blog

    Chris's blog has some good tools for performance testing Analysis Services. 

    I do a lot of performance tuning as part of my consultancy work, and quite often when I start looking at a customer's cube I find that for any given query that needs to be tuned there are several (sometimes hundreds) of calculations which affect the cells in the query and which could be the cause of performance problems. To help me work out which calculations are the ones that need to be looked at I put together a tool - the MDX Script Performance Analyser - which I've just got round to putting up on Codeplex so it can be shared:

    http://www.codeplex.com/mdxscriptperf

    Basically what it does is this:

    • First of all, you connect to the cube that your query runs against
    • Then you enter your query in the text box at the top of the screen and hit 'Run Query'
    • This then starts the following process:
      • The tool reads the cube's MDX Script and splits it up into its constituent statements, storing them in an array
      • It executes a Clear Cache command to ensure that all queries are run on a cold cache
      • It executes a Clear Calculations command so that for the current session the cube appears as though its MDX Script contains no commands
      • For each statement in the array of statements from the MDX Script, it then:
        1. Executes the first statement in the MDX Script within the session, so that the cube now acts as though its MDX Script contains only this statement and all previously executed statements
        2. Runs the query you entered in the textbox
        3. Stores how long the query took to run, plus other interesting metrics
      • Once the query has run on the equivalent of the entire MDX Script in the cube, a report is generated which contains graphs and charts illustrating the data captured earlier

    Source: Chris Webb's BI Blog

    Thursday, April 19, 2007

    Roadkil's Unstoppable Copier

     

    Recovers files from disks with physical damage. Allows you to copy files from disks with problems such as bad sectors, scratches or that just give errors when reading data. The program will attempt to recover every readable piece of a file and put the pieces together. Using this method most types of files can be made useable even if some parts were not recoverable in the end.

    Source: Roadkil's Unstoppable Copier

    Wednesday, April 18, 2007

    Geeky Storytelling : Expanding VHDs

     

    Expanding VHDs

    Published 02 April 07 02:27 PM

    I ran out of room in my VHD image (for VirtualPC 2007) I've been using while building a new image for my current project. Blogging it so I don't waste too much time looking for it the next time I need to do this.

    I was able to expand my VHD using the following:

    Use VHDResize to increase the size of the VHD disk. Important note, which I missed the first time (since I didn't RTFM :()), this does not extend the partition.

    Use DISKPART to extend the partition to use the new disk size.

    Source: Geeky Storytelling : Expanding VHDs

    Monday, April 16, 2007

    Saturday, April 14, 2007

    instructables : Save $200 in 2 minutes and have the worlds best writing pen

     

    Go out and find a Mont Blanc pen you like. Ask the salesperson to let you write with it...nice, huh? Now ask the price. When you've gotten over the sticker-shock, leave and go back to your good old G2. Remember what life was like before G2? The pens were cheap and the ink was like cheese. G2s were the best thing since clickable mechanical pencils. Even after we all had G2s, I still admired the uber-extravagant Mont Blanc people. Their pens were so smooth, they nearly wrote by themselves. Alas, at $200-$2000 a pen, that miraculous ink was out of the reach of the common man.....until now.

    Source: instructables : Save $200 in 2 minutes and have the worlds best writing pen

    Wednesday, April 11, 2007

    tlbox - Programming Tools

    hundreds of links for programmers. 

    111 users
    4GuysFomRolla.com

    The website contains articles and tutorials with regard to ASP.NET 2.0 ranging from accessing & updating data and application that can be derived from using the software technology. The website also offers weekly newsletter, links & resources to other related websites, memberships, etc. to help fellows programmers understand the the software and its applications.

    80 users
    ASP.NET 2.0

    A free download with technical support that allows users to create interactive websites that work across all modern browsers.

    61 users
    The Code Project

    Articles on Visual Studio and .NET

    49 users
    CodePlex

    Microsoft's open source project site featuring downloads, discussion forums, and extensive links for multiple project tags.

    35 users
    Mono

    Mono is an open development initiative by Novell to develop an open source version of Microsoft's .NET development platform for UNIX developers.

    16 users
    W3Schools .net

    A complete list of tutorials and code sample very well formatted.

    Source: tlbox - Programming Tools

    Photoshop Tutorials and Flash Tutorials

    Over 12,000 tutorials for anything and everything here. 

  • Tutorials: 12,708
  • Categories: 555
  • Users Online: 602
  • Source: Photoshop Tutorials and Flash Tutorials

    GigaSize.com: Host and Share your Files

     

    Gigasize is probably the best way to store and share big files online. Why you ask ? Let us show you !

    Gigasize offers you virtually limitless space to store any kind of file (Documents, Photos, Music, Video ...)

    You can use our services to :

    • Share your files with your friends/family
    • Use our e-mailing services to to send your files to your Instant Messenger (IM) contact lists
    • Access your files from anywhere in the world
    • And much more ...

    Essentially, GigaSize is the new way to share anything with anybody, without the hassles of traditional ways like e-mail. And the best part is that basic accounts are free !

    Source: GigaSize.com: Host and Share your Files

    How to obtain a Microsoft Hot Fix

    First, how to obtain a Microsoft Hot Fix – go to http://www.microsoft.com/services/microsoftservices/supp.mspx and find the area that fits your current need. Call the support number, and select the option for hot fixes. Someone from Microsoft will answer; tell the support person you are calling for a hot fix. After you give your information and state your case, they send you a download link. It’s just that easy.

    Source: Windows Server Clustering & PCNews : How to obtain a Microsoft Hot Fix - or should you?

    Monday, April 09, 2007

    Dan Evans' VBA Scripts - Outlook Attachment Checker etc

     

    This macro checks for when you mention Attach in your email, and then prompts you if you forgot an attachment.  See posting for details on installation. 

    Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    ' VBA program for Outlook, (c) Dan Evans. dan at danevans.co.uk
    ' Will check if your outgoing email mentions an attachment, but you've
    ' forgotten to attach it
    ' v1.03 of 10/8/04 - Modified to search through subject line as well as message body
    ' v1.02 of 16/10/02 - No change to code, but tested works with Outlook 2002 as well as Outlook 2000
    ' v1.01 of 23/9/01 - OK for "Attach" as well as "attach"
    ' v1.00 of 21/9/01 - Initial working version
    Dim intRes As Integer
    Dim strMsg As String
    Dim strThismsg As String
    Dim intOldmsgstart As Integer
    intOldmsgstart = InStr(Item.Body, "-----Original Message-----")
    ' intOldmsgstart is the location of where old/re/fwd msg starts. Will be 0 if new msg
    If intOldmsgstart = 0 Then
    strThismsg = Item.Body + " " + Item.Subject
    Else
    strThismsg = Left(Item.Body, intOldmsgstart) + " " + Item.Subject
    End If
    ' The above if/then/else will set strThismsg to be the text of this message only,
    ' excluding old/fwd/re msg
    ' IE if the original included message is mentioning an attachment, ignore that
    ' Also includes the subject line at the end of the strThismsg string
    If InStr(LCase(strThismsg), "attach") > 0 Then
    If Item.Attachments.Count = 0 Then
    strMsg = "Dan Evans' Attachment Checker:" & Chr(13) & Chr(10) & "Your message mentions an attachment, but doesn't have one." & Chr(13) & Chr(10) & "Send the message anyway?"
    intRes = MsgBox(strMsg, vbYesNo + vbDefaultButton2 + vbExclamation, "You forgot the attachment!")
    If intRes = vbNo Then
    ' cancel send
    Cancel = True
    End If
    End If
    End If
    End Sub

    Source: Dan Evans' VBA Scripts - Outlook Attachment Checker etc

    Wednesday, April 04, 2007

    Elenco prodotti Datawarehouse e Business Intelligence

     

    Huge list of OLAP products here: 

    This page contains links to companies that sell software products in the areas of  Business Intelligence and Data Warehousing.
    It's not easy to categorize Data Warehousing products, especially since nowadays many companies have expanded their offers in an effort to present themselves as suppliers of complete BI and BPM solutions.
    This page has been recently rearranged, the main change being that the first block now contains all those companies that appear in the Gartner BI magic quadrant.

    Source: Elenco prodotti Datawarehouse e Business Intelligence

    A Common Architecture for Loading Data

    Here is one generic approach to loading data files into SQL. 

    The advantage of the "Plug In" approach is that it simplifies the future addition using only metadata, this table contains unique names of extracts, names of stored procedures for uploading data from staging to production tables, unique key position in the data file and so on.

    Source: A Common Architecture for Loading Data

    Monday, April 02, 2007

    SQL Scripter

     

    Sql Scripter is a free utility to generate Sql metadata scripts, and also Reporting Services RDL scripts. 

    The April, 2007 release now exports data to flat files!

    Link to SQL Scripter

    Friday, March 30, 2007

    The BizSharpie PerformancePoint Blog

     

    Link to The BizSharpie PerformancePoint Blog

    SSW Rules to Better SQL Reporting Services 2005

     

    Rules to Better SQL Reporting Services

    1. Do you know the 4 user experiences of Reporting Services: Vanilla, Website, Email, Windows?
    2. Do you know when to use Reporting Services?
    3. Do you check that "RS Configuration Manager" is all green ticks?
    4. Do you check out the built-in samples?
    5. Do you know your 2 migration options to show your Access reports on the web?
    6. Layout - Does your report print and display on the web correctly?
    7. Layout - Do you include a useful footer at the bottom of your reports?
    8. Layout - Do you avoid using word 'Report' in your reports?
    9. Layout - Do you underline items with Hyperlink Action?
    10. Layout - Do you show errors in Red?
    11. Layout - Do you have consistent report name?
    12. Data Layout - Do you show the past 6 months of totals in a chart?
    13. Data Layout - Do you show data and chart in one?
    14. Data Layout - Do you avoid using a single chart when you need it to be scaled?
    15. Data Layout - Do you use expressions to show the correct scale on charts?
    16. Data Layout - Do you show change in your reports?
    17. Data Layout - Do you avoid showing change as a percentage?
    18. Data Layout - Do you use alternating row colors?
    19. Data Layout - Do you have nodes count like Outlook?
    20. Data Layout - Do you avoid displaying decimal places?
    21. Data Layout - Do you have consistent height of table row across all your reports?
    22. Data Layout - Do you display zero number as blank in your reports?
    23. Data Layout - Do you know the best way to show your data?
    24. Data Layout - Do you show time format clearly?
    25. Data Logic - Do you use de-normalized database fields for calculated values?
    26. Parameters - Do you avoid showing empty reports by at least setting Default parameters?
    27. Parameters - Do you avoid showing empty reports by the most intelligent default?
    28. Parameters - Do you use the DateTime data type for date parameters?
    29. Parameters - Do you have consistent parameter names?
    30. Performance - Do you cache popular reports for better performance?
    31. Performance - Do you schedule snapshots of slow reports for quicker access?
    32. Internationalization - Do you keep use regional friendly formatting?
    33. Internationalization - Do you make sure your language follows the user's regional settings?
    34. Internationalization - Do you make sure your language rule has an exception for Currency Fields?
    35. Admin - Do you validate all your reports?
    36. Admin - Do you create a separate virtual directory for Admin access?
    37. Admin - Do you take advantage of 'Integrated Security' to do Payroll reports?
    38. Admin - Do you remove ExecutionTime in Subject of Subscription email?

    Source: SSW Rules to Better SQL Reporting Services 2005

    Parse delimited string in a Stored procedure

     

    Parse delimited string in a Stored procedure

    Sometimes we need to pass an array to the Stored Procrdure and split the array inside the stored proc. For example, lets say there is a datagrid displaying sales orders, each sales order associated with an orderid (PK in the Sales table). If the user needs to delete a bunch of sales orders ( say 10-15 etc)..it would be easier to concatenate all the orderid's into one string like 10-24-23-34-56-57-....etc and pass it to the sql server stored proc and inside the stored proc, split the string into individual ids and delete each sales order.

    There can be plenty of other situations where passing a delimited string to the stored proc is faster than making n number of trips to the server.

    CREATE PROCEDURE ParseArray (@Array VARCHAR(1000),@separator CHAR(1))
    AS

    BEGIN
    SET NOCOUNT ON

    -- @Array is the array we wish to parse
    -- @Separator is the separator charactor such as a comma

    DECLARE @separator_position INT -- This is used to locate each separator character
    DECLARE @array_value VARCHAR(1000) -- this holds each array value as it is returned
    -- For my loop to work I need an extra separator at the end. I always look to the
    -- left of the separator character for each array value

    SET @array = @array + @separator

    -- Loop through the string searching for separtor characters
    WHILE PATINDEX('%' + @separator + '%', @array) <> 0
    BEGIN
    -- patindex matches the a pattern against a string
    SELECT @separator_position = PATINDEX('%' + @separator + '%',@array)
    SELECT @array_value = LEFT(@array, @separator_position - 1)
    -- This is where you process the values passed.

    -- Replace this select statement with your processing
    -- @array_value holds the value of this element of the array
    SELECT Array_Value = @array_value
    -- This replaces what we just processed with and empty string
    SELECT @array = STUFF(@array, 1, @separator_position, '')
    END
    SET NOCOUNT OFF
    END

    GO

    Source: Parse delimited string in a Stored procedure

    David Francis Blog : PerformancePoint CTP1 - Planning Assignment doesn't render issue and solution from Microsoft

     

    PerformancePoint CTP1 - Planning Assignment doesn't render issue and solution from Microsoft

    Here is a useful bit of info from Mark Yang at Microsoft if you ever get the following issue in the CTP1 build of PerformancePoint Planning.

    I had been working/playing with the assignment and workflow functionality of PerformancePoint Planning and had created a number of form templates.

    I'd set up a basic assignment using a form to be accessed by only one user and approved by another. The assignment appeared in the list but when the user clicked on the assignment they got the following:

    'Error retrieving latest timestamps from Analysis Server'

    'Internal Error Message: System.Web.Services.Protocols.SoapException: Failed to retrieve timestamp from workflow data from table Assignments'

    at the Caching Assignments stage and the form retrieval stage.

    When I ran the scenario with SQL profiler on, it appeared to be running a stored proc called bsp_BizFormGetByFormId with the GUID of the very first form I ever created in the system (different name etc etc).

    Apparently according to Mark this is a known issue with CTP1 and is to do with the storing of information about assignments in the Offline Cache.

    So here is his solution:

    Try manually deleting your assignment cache by deleting the files from \Documents and Setttings\<user_name>\Local Settings\Application Data\Microsoft\PerformancePoint\OfflineCache and restart your Excel Client.

    I have to admit I had to actually reboot my server for it to work, but work it did.

    Thanks again Mark.

    I'd highly recommend if you aren't signed up to the PerformancePoint newsgroups (microsoft.beta.office.performancepoint.planning and microsoft.beta.office.performancepoint.monitoranalyze) that you do so.

    Source: David Francis Blog : PerformancePoint CTP1 - Planning Assignment doesn't render issue and solution from Microsoft

    SQLskills.com | Immerse Yourself In SQL Server

    http://www.sqlskills.com/whitepapers.asp

    SQL Server 2005 Whitepapers

    ÞAdvantages of a 64-bit Environment
    ÞBatch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
    ÞConnectivity and SQL Server 2005 Integration Services (written by Bob Beauchemin of SQLskills.com)
    ÞDatabase Administrator’s Guide to SQL Server Database Engine .NET CLR Environment (written by Kimberly L. Tripp of SQLskills.com)
    ÞDatabase Engine Tuning Advisor (DTA) in SQL Server 2005
    ÞDatabase Mirroring in SQL Server 2005
    ÞDatabase Mirroring: Alerting on Database Mirroring Events
    ÞDatabase Mirroring: Best Practices and Performance Considerations
    ÞEnterprise Strategy Group Information Security Brief: SQL Server Runs the Security Table
    ÞHow SQL Server 2005 Enables Service-Oriented Database Architectures
    ÞImproving Performance with SQL Server 2005 Indexed Views
    ÞOnline Indexing Operations in SQL Server 2005
    ÞPartitioned Tables and Indexes in SQL Server 2005 (written by Kimberly L. Tripp of SQLskills.com)
    ÞPerformance Tuning Using Waits and Queues
    ÞPhysical Database Storage Design
    ÞReporting Services: Planning for Scalability and Performance with Reporting Services
    ÞScalability: Internals, Troubleshooting, and Best Practices for use of Scaleout Technologies in SQL Server 2005 (written by Bob Beauchemin of SQLskills.com)
    ÞScalability: Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005 (written by Bob Beauchemin of SQLskills.com)
    ÞScalability: Solutions for Highly Scalable Database Applications: An analysis of architectures and technologies (includes details/comparisons with Oracle RAC)
    ÞSQL Server 2005 Failover Clustering White Paper
    ÞSQL Server 2005 for Oracle Professionals
    ÞSQL Server 2005 Management Tools Overview (written by Kimberly L. Tripp of SQLskills.com)
    ÞSQL Server 2005 Security Best Practices - Operational and Administrative Tasks (written by Bob Beauchemin of SQLskills.com)
    ÞSQL Server 2005 Security Overview for Database Administrators
    ÞSQL Server 2005 Snapshot Isolation (written by Kimberly L. Tripp of SQLskills.com)
    ÞSQL Server I/O Basics
    ÞSQL Server I/O Basics, Chapter 2
    ÞStatistics Used by the Query Optimizer in Microsoft SQL Server 2005
    ÞStrategies for Partitioning Relational Data Warehouses in Microsoft SQL Server
    ÞTroubleshooting Performance Problems in SQL Server 2005
    ÞWorking with Tempdb in SQL Server 2005

    SQL Server 2000 Whitepapers

    ÞDatabase Architecture: The Storage Engine
    ÞImproving Performance with Microsoft SQL Server 2000 Indexed Views
    ÞIndex Tuning Wizard for Microsoft SQL Server 2000
    ÞMicrosoft SQL Server 2000 Index Defragmentation Best Practices
    ÞQuery Recompilation in SQL Server 2000
    ÞSQL Server 2000 Incremental Bulk Load Case Study
    ÞStatistics Used by the Query Optimizer in Microsoft SQL Server 2000
    ÞUsing Partitions in a Microsoft SQL Server 2000 Data Warehouse

    Group links, blogs links to links and other useful links to links of links

    ÞWhitepapers on Microsoft.com (many NOT Listed above)
    ÞWhitepapers on MSDN - the Developer Center Whitepapers (many NOT Listed above)
    ÞWhitepapers on TechNet - SQL Server 2005 Technologies (many NOT Listed above)
    Þ"Previous Versions" Page - Information, links and resources related to previous versions of SQL Server (many NOT Listed above)

    Important Note

    ALL of these links were valid on March 26, 2007 and may no longer be valid. If companies (who shall remain nameless :) would stop breaking links ;), it would make our lives easier (well, at least for finding content :) BUT we're always going to have this issue as it's just the general nature of the web! So, I'm sorry if you hit a link and don't get where you want to go. Use the whitepaper title to search for it and then be sure to let us know if you find it. Regardless, please let us know that the link is broken and we'll try to find the current link for you as well.
    Also, if you find a whitepaper not listed (or linked to) OR you think there's a whitepaper that should specifically be called out on this list, please let us know!
    You can reach us at info@SQLskills.com and for specific questions: questions@SQLskills.com. Thanks!
    March 29, 2007: Added a few more links, fixed a few broken ones (one via a cut/paste error and the other was one I missed). All of this was thanks to comments on my whitepapers blog post here.

    Source: SQLskills.com | Immerse Yourself In SQL Server