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

    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