June 15th, 2010 – ITA Roundtable slides

Slides are linked here.

Topic was “late-breaking news from the MS BI Conference”

Posted in Uncategorized | Leave a comment

Microsoft Business Intelligence Conference Recap – Day 1

Well, the grandiose plan was to blog each session as they happened, but between keeping my existing clients happy and keeping up with all the events of TechEd/MSBIC, that did not happen.

I was really on the fence about attending the Microsoft Business Intelligence Conference, based in part on existing client demand, but largely on the fact that there have been conferences that I attended where I didn’t learn very much and felt it was a waste of time. This generally was due to the fact that I had been using a particular product in beta as part of a TAP program or was somehow pretty plugged in to the goings-on, and the conference didn’t raise the bar very much. I definitely did NOT feel that way about the recent MS BI Conference in New Orleans; I was very glad I chose to attend.

Here’s a chronological recap of my experiences, kept as brief as possible. If available, I’ll include links to the presentations I attended.

Day 1

06 June 2010 – 23:30 – Touch down at MSY airport

07 June 2010 – 01:00 – Arrive at Hotel, which turned out to be nowhere near any of the events. Expedia told me 2.5 blocks. Turned out to be a mile away. It was a really nice converted mid-1800’s house. Quiet, with a good bed.

07 June 2010 – 07:45 – Registration. Went smoothly. Glad the lines for BI were shorter than for TechEd.

07 June 2010 – 8:30 – coffee at the excellent LaDivina Gelateria stand in the convention center.  I could not believe people were passing this place up to wait in line at Starbucks. This became a morning ritual.

07 June 2010 – 9:00 – Keynote, which I blogged already here. Short assessment: A reasonably effective, but uninspired (and uninspiring) keynote. All head and no heart. I did find myself engaged by the section in which Ariel Netz demoed the BI technologies. I was especially pleased by the use of the term “wickedly fast” to describe PowerPivot.

07 June 2010 – 11:00 – There were no sessions for BI folks to attend, even though there were for other tracks. Upon review of the schedule, there were some “foundational” sessions, but these were not publicized or communicated very clearly.

07 June 2010 – 12:00 – Lunch.

07 June 2010 – 1:00 – 2:15 – Attempting to attend the Interactive Session “BIE04-INT Building Custom Extensions the the PowerPivot Management Dashboard” with Dave Wickert. Here’s where the trouble started:

I was told the session was full. The red-shirted people wranglers were not letting anyone in. OK, I could understand how this might be a popular session. OK, I’ll just go to my 2nd choice:

“BIO101 – Business Intelligence Overview: Decisions, Decisions, Decisions”. I was told this session was full, too. The red-shirted people wranglers were not letting anyone in. OK, I could understand how this might be a popular session. OK, I’ll just go to my 3nd choice:

“BIE201 – Configuring PerformancePoint Services in Microsoft SharePoint Server 2010”. I was told this session was full, too. The red-shirted people wranglers were not letting anyone in. OK, I could understand how this might be a popular session. OK, I’ll just go to my 4th choice:

“BIU202 – Business User Productivity via Report Part Galleries in SQL Server Reporting Services”. I was told this session was full, too. The red-shirted people wranglers were not letting anyone in. OK, I could understand how this might be a popular session. OK, I’ll just go to my 5th choice”

“BIP03-INT – Creating an EPM Environment Using Microsoft SQL Server 2008 R2 and SharePoint 2010″. I was told this session was full, too. The red-shirted people wranglers were not letting anyone in. OK, I could understand how this might be a popular session. OK, I’ll just go to my 6th choice”

“BIC205 – How Partners Healthcare, a Worldwide Leader in Radiology Medicine, Uses Microsoft BI to Improve Patient Quality”. I was told this session was full, too. The red-shirted people wranglers were not letting anyone in.

So, here I am, after planes, trains, and automobiles, a several thousand dollar outlay, standing in the hallway of the New Orleans Morial Convention Center, not learning about Business Intelligence. Clearly not acceptable. The capacity planning for the BI sessions was clearly not effective. While the event planners did take some measures to address the issue, this remained an issue throughout the rest of the conference.

In the end, I was able to get in to the last 25 minutes of Dave Wickert’s presentation, in which he demonstrated extending the PowerPivot Management dashboards, using PowerPivot. The business case was to add a priority category to data sources. So in effect, he demoed using PowerPivot to manage PowerPivot. Very cool. He predicted that there would be a community-created set of extensions as we have seen with various artifacts on CodePlex like the Slowly Changing Dimension component or the SSRS Management reporting.

The interactive sessions do not appear to be posted. Dave’s blog site is here, the MS whitepaper describing the technique is here

07 June 2010 – 2:45 – 4:00 “BIE203 – Introducing Microsoft SQL Server 2008 R2 Master Data Services” The product seemed like  it’s not ready for prime time, and the presenter seemed like he was not ready for the session. I walked out.

07 June 2010 – 4:30 – 5:45 – “BIP301 – So Many BI Tools, So Little Time” This session was awesome. Dan Bulos laided out all the different options for implementation available with the MS BI toolset. It was clear from his presentation that Dan had deep experience and had given a lot of thought to what works well where. Definitely in my Top 5 sessions of MSBIC. I definitely recommend watching the video; session linked here:

07 June 2010 – 5:45 – 9:00 Partner Expo – I enjoyed browsing the booths, and have been to enough of these that I don’t play the “gimme swag” game very much. It seems like the mood was upbeat and commerce, or at least the potential for commerce, was in full swing.

07 June 2010 – 9:00 – ?? Take care of emails, take care of the  “day job”, then collapse. No night-time socializing today – the late-night flight & arrival took its toll.

Posted in Uncategorized | Leave a comment

#MSBIC Keynote

Ted KummertMS Uses BI to ship, track defects.

Recollection of BI Conference 2008 – the fairy tale.

Managed self-service business intelligence.

State of BI today

20% have BI.

There are many things that end users should be able to solve on their own. e.g. Add some data to a report.

BI practitioners can then focus on strategic development and implementations.

  • Familiar tools
  • Collaboration
  • Managed

Scenario #1: HR Professional, doing salary analysis, a mixture of internal data and data from the wild. IT is too busy, so it’s built in Excel.

Solution: PowerPivot, powerful in-memory columnar database. Can also render in SharePoint without additional download. Sharepoint is both point of collaboration and management

Scenario #2: Self-service reporting

Solution: Report Builder 3.0, Grab and go Reporting – component library.

Q: “Hey Microsoft, how do I get BI?” A:  Office, SharePoint, & SQL Server. This should turn bi into Utility, like electricity.

Micheal Tejedor

  • Demo of SharePoint
  • Demo of Search
  • Demo of SilverLight report catalog viewer.

“PowerPivot extends Excel with an in-memory database add-in”

DAX – similar to Excel expression language

Now showing SharePoint workflow

Now showing the management workbooks that track system usage activity. Example, a report that has grown in frequency can be moved into DW.

Ayad shammout

Case study – Healthcare Harvard University’s Teaching Hospital – 4 hospitals
PowerPivot removes the lines between users and IT
Medical data needs to be protected. Used SQL2008 audit feature
44 million rows in excel

“Dashboard” built in about 6 hours. Also uses mapping. Uses power pivot data as a source. Uses mapping control and report builder 3.0

Analysis: readmission – find out who should was released too early. 2 days manual work, new Powerpivot report takes about 4 hours, 30 gig compresses down to 5 Meg

Kummert: manual process convert to efficient automation

Donald Farmer

alpha geek challenge

Competition
Entry 1-employee morale Dan coming ore
Entry 2-flights
Entry 3-personality characteristics
Great visualization
Entry 4-(joke) applause by

A look into the future
“not announcing features or schedule”
The cloud (say it: cloud, cloud, cloud)
Where are you headed in terms of providing BI in the cloud?
Intent is to offer all the capabilities of SQL in the cloud, including reporting.
Commoditization of IT into the cloud. Search, social media.
Mapping
Compliance- data quality. Problem: what is the key reference data
Data cleansing- machine learning
Lineage, impact analysis. Requires thinking deeply about a metadata strategy
Increasing data volumes. More transactions, more detail from those transactions
Parallel data warehouses coming soon
Working with hardware vendors on reference implementations
Codename Dallas. Reference content.

Amir Netz

Alpha code demo:
How were unifying the models
New data visualization & powerpivot
EPG Waterfall analysis – redacted data
Professional BI
KPI’s in PowerPivot. They’ve been in the engine, but not exposed via UI
@amirnetz just demoed PowerPivot based KPI’s at #msbic keynote. They look slick, better than performancepoint

Way cool! PowerPivot Calculation impact analysis tracing diagram demoed by @amirnetz at #msbic http://tweetphoto.com/26228397

2 Billion rows in Excel. sub-second response.

Wow. Awesome keynote. More later…

Recording is now available

Posted in Uncategorized | Leave a comment

TechEd Keynote – brief comments & impressions

I am keeping these brief on purpose – just my impressions in 5 sentences or less (I hope).

Bob Muglia

Cloud

Apparently there is this thing called the cloud . . . Say it:  Cloud, cloud, cloud, cloud. In the cloud, magic elves create software that works automagically. Oooo … Cloud, cloud, cloud, cloud.

Seriously though, this is a big emphasis for MS. My sense is that both the technology and implementations are early and still somewhat immature.

The Chicago Tribune case study piqued my interest (coming from Chicago), but didn’t provide huge details.

Testing

Demo of an automated testing app that pinpoints error, captures screenshots, and allows you to step backwards. Cool demo. I wonder what softwares need to be in place for this to work,

Virtualization

It’s hard to demo virtualization, since it’s all, well, virtual.

Application management

The main concept I came away with is the idea of saving state, provisioning updates and dropping the preserved application state on top of the patched virtual OS.

Danger phrase during this demo: “It’s just a check-box”
Translation of key phrase: “Available for download” ≠ “released”

Office Communication server

720 videoconferencing using commodity hardware
Cool demo, but demo gremlins messed up the effect. The background floor plan took forever to load, so the collaborative white-boarding took place on an empty white rectangle.

Windows Phone 7

Way too many animations in Windows Phone 7 OS.
Cool auto-suggest feature
Sync w/ Sharepoint demo was cool.

IE 9 –Mentioned, not demoed

Windows intune – New security suite? Mentioned, not demoed

BI in the cloud ?

(I think that’s what Mr. Muglia said, but the demo really didn’t have anything to do with the cloud)
Great demo by Amir Netz, who called PowerPivot’s sorting 100 Million rows “wickedly fast”
Code name Dallas
Odata format to integrate data from the cloud (ahh, there’s where cloud come in. Say it: cloud, cloud, cloud)
Demo of uploading to SharePoint
Demo of reporting on this with SSRS
PivotViewer – very cool demo of stop-light color-coded thumbnails in a silverlight based power-pivot like display.
Also demoed Mapping linked with geo-tagged photos

Avatar – wow ms software used to create Avatar?

Gaia asset tracking application – Used SQL, tracked production assets.

Mr. Muglia’s closing thought

Cloud creates opportunities and responsibilities

Edit: Link to the actual keynote is here

Posted in Uncategorized | 1 Comment

Inexpensive iPad holder – less than $5

I just needed a temporary solution to hold my iPad at my desk. It’s worked out pretty well. Thought I’d post the tip:



It’s an Office Depot Plate Holder, Clear Item # 544474

Posted in Hardware | Tagged | Leave a comment

Resetting "Windows XP Mode" to initial settings

Under Windows 7 64-bit ultimate edition, I’ve had the need to reset my “Windows XP Mode” to initial settings several times over the past few days. Initially I did this because as I used the XP virtual environment, the VHD file grew to around 10 Gig. Not huge, but I wasn’t using it for anything except to run a client’s 32-bit only VPN software, so it was wasting a fair amount of space. My main machine has a 256 Gig SSD for the main drive, so I am conservative with space consumption.


The second reason was that I was helping my fiancée (a high-school teacher) obtain some (public domain) video for use in her classroom. Unfortunately this video seemed only to be available in a heavily watermarked version on YouTube (ugh) or in a Real Media stream (double-ugh). So I wound up needing to use a mixture of open source and very old software to convert this video to a format that was playable in the classroom. As a rule I don’t install software like that on my production machine – a virtual environment is a great “sandbox” for momentary needs like this one. After I converted this video, I no longer needed all these (rather buggy) utilities, so I wanted to start clean.


I didn’t see much on the web on how to reset the “Windows XP Mode” to initial settings. I tried this experiment, which worked pretty well.


To reset Windows XP to factory new condition:

  1. Right click the vmcx file. Select Settings. (On my machine the file is called Windows XP Mode.vmcx and is located in C:\Users\Tom\Virtual Machines)
  2. Note the location of Hard Disk 1 (On my machine this location is called C:\Users\Tom\AppData\Local\Microsoft\Windows Virtual PC\Virtual Machines\Windows XP Mode.vhd)
  3. Exit the Settings Dialog
  4. Delete the files associated with your “Windows XP Mode” environment. On my machine, these are located in C:\Users\Tom\AppData\Local\Microsoft\Windows Virtual PC\Virtual Machines\Windows XP Mode.vhd and are called 
    1. Windows XP Mode.vhd
    2. Windows XP Mode.vmc
    3. Windows XP Mode.vmc.vpcbackup
    4. Windows XP Mode.vsv
  5. Now, find the original Start Menu shortcut for Windows XP Mode. As long as you’ve left the Parent Disk in place, the system will prompt you to recreate a new environment:


voilà! – New XP environment (after a few other dialogs and a few minutes building)

Standard cautionary notes: 

    • I haven’t researched whether this is the “proper” way to do this. But I’ve done this multiple times on 2 different machines and it takes less than 10 minutes to reset, install anti-virus, and reinstall VPN software
    • Obviously when I suggest you delete files on your system, I am assuming you know the implications of this and have everything backed up or have determined that you no longer need anything from the files you are deleting
    Posted in Operating Systems, Virtualization | 11 Comments

    Burn the Ships

    Although it’s probably an apocryphal story, legend has it that Cortez instructed his men to “Burn the Ships” upon encountering the New World. This has become (in my parlance, anyway) a catchphrase for being irrevocably committed to a particular course of action.

    In that spirit, yesterday I blew away v1 of my website and started up this version. This will remain a work in progress for a while, since other client commitments will have me quite busy through June.

    Anyone using WordPress who has themes or plugins that they love, please leave a comment.

    Posted in Web | Tagged , | Leave a comment

    Time (Not Date) Dimension Table SQL code

    I am seeing more call for Time Dimensions. By that I mean Time-of-Day Dimensions. So now I have to retrain myself to call Date Dimensions “Date” and Time Dimensions “Time”.


    As a follow up to this post, here’s some DDL and a quick routine to generate a time of day table. The resolution is to the second, which so far has proved sufficient for my clients’ purposes.


    Enjoy


    SET nocount ON
    /*
    CREATE TABLE [dbo].[dim_Time](
      [TimeId] [int] NOT NULL,
      [Time] [time](7) NULL,
      [Hours] [tinyint] NULL,
      [Minutes] [tinyint] NULL,
      [Seconds] [tinyint] NULL,
     CONSTRAINT [PK_dim_Time] PRIMARY KEY CLUSTERED
    (
      [TimeId] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    TRUNCATE TABLE [dbo].[dim_Time]
    GO
    SELECT * FROM dbo.dim_time
    */

    DECLARE @second1 INT = 0
    DECLARE @currtime TIME
    DECLARE @msg VARCHAR(30) = 


    WHILE @second1 < 86400
      BEGIN
          SELECT @second1 = @second1 + 1
          SELECT @currtime = Dateadd(ss, @second1, ’00:00.00′)
          —  SELECT   
          –DatePart(hh, @currtime) * 10000 + 
          –DatePart(mi, @currtime) * 100 +
          –DatePart(ss, @currtime) AS TimeID
          –, @currtime AS [Time]
          –, DatePart(hh, @currtime) AS [Hours]
          –, DatePart(mi, @currtime) AS [Minutes]
          –, DatePart(ss, @currtime) AS [Seconds]
          INSERT dbo.dim_time
                 ([TimeID],
                  [Time],
                  [Hours],
                  [Minutes],
                  [Seconds])
          VALUES (
                  Datepart(hh, @currtime) * 10000 + 

                  Datepart(mi, @currtime) * 100 + 
                  Datepart(ss, @currtime),
                   @currtime,
                   Datepart(hh, @currtime),
                   Datepart(mi, @currtime),
                   Datepart(ss, @currtime) )
          IF @second1 % 1000 = 0
            BEGIN
                SELECT @msg = ‘Now Processing ‘ + CAST(@second1 AS VARCHAR(12))
                PRINT @msg
            END
      END 

    Posted in Modeling/Architecture, OLAP | Leave a comment

    Adventures with Virtualization

    Those of us needing to run BI software in virtual environments are well familiar with the challenges of getting things running smoothly, especially if you are using a laptop as your lab or demo environment. We have a new set of challenges with Windows 7, and with SharePoint 2010.

    Challenge #1 – SharePoint 2010 requires 64 bit OS, and the only MS virtualization software that allows for 64-bit is Hyper-V.
    Only problem with running Hyper-V is that you cannot sleep a machine with Hyper-V, so if you’re using you laptop as a lab machine, you need to retain an OS that will run productivity apps and sleep. I set up my old laptop using the technique described here and it works well. I haven’t repeated the process for my new laptop yet, but I plan to.

    Challenge #2 – Windows 7 Virtual PC requires Hardware Assisted Virtualization
    Apparently this is no longer true, but I’ve wrestled with this one for months, in fact in early March I returned a machine that didn’t support hardware assisted virtualization because I needed to run Windows 7 VPC. . MS just released a version of Virtual PC that does not require hardware assisted virtualization

    Other tips that have worked well for me:
    • Use a fast disk – I was using a 7200 RPM eSATA external drive – faster than the 5400 disk commonly found on laptops. Now I am using an SSD drive- even better.
    • Store the VHD in a compressed folder. This one struck me as counter-intuitive but anecdotally seems to work.
    Posted in Operating Systems, Virtualization | Leave a comment

    Brilliantly stupid MDX debugging technique

    Whenever I do something really stupid, rather than keep it to myself, I prefer to blog about it for the entire world to see. Even better – I came up with a debugging technique to save myself from my own, er, stupidity.


    The scenario is this: I am developing some SQL Server Analysis Services Date Calculations in MDX for a client. They have three different Date Hierarchies, and wanted the standard, Previous Period, Period to Date, etc calculations. (For a very good explanation of the techniques involved, read this PDF first, then study this refinement from Mosha Pasumansky. )


    The MDX looks like this: 

    Scope([Calculation].[Calculation].[Previous Period]);      
        ([Calendar].[Calendar].[Date]) = ([Calculation].[Current Period], ParallelPeriod([Calendar].[Calendar].[Calendar Year],1));      
        ([Calendar].[Week].[Calendar Week].MEMBERS) = ([Calculation].[Current Period], [Calendar].[Week].Lag(52));      
        ([Calendar].[Calendar].[Calendar Month].MEMBERS) = ([Calculation].[Current Period], [Calendar].[Calendar Month].Lag(12));      
        ([Calendar].[Calendar].[Calendar Quarter].MEMBERS) = ([Calculation].[Current Period], [Calendar].[Calendar Quarter].Lag(4));      
        ([Calendar].[Calendar].[Calendar Year].MEMBERS) = ([Calculation].[Current Period], [Calendar].[Calendar Year].Lag(1));      
        ([Calendar].[NRF].[NRF Week].MEMBERS) = ([Calculation].[Current Period], ParallelPeriod([Calendar].[NRF].[NRF Week],1));
        ([Calendar].[NRF].[NRF Period].MEMBERS) = ([Calculation].[Current Period], [Calendar].[NRF Period].Lag(13));
        ([Calendar].[NRF].[NRF Quarter].MEMBERS) = ([Calculation].[Current Period], [Calendar].[NRF Quarter].Lag(5));
        ([Calendar].[NRF].[NRF Year].MEMBERS) = ([Calculation].[Current Period], ParallelPeriod([Calendar].[NRF].[NRF Year],1));
    End Scope;      

    Pretty standard stuff (sorry about the line wraps): 

    The symptom was that my Calendar Data Hierarchies were working fine, but the other two (Week and NRF [National Retail Federation]) were not. I worked my way through the data, and it all looked correct. Even a dynamic MDX query in SSMS worked fine. But it was wrong in the cube.

    When debugging MDX, I find that it pays to go in small increments and to do patently obvious things like setting Scoped statements to constant values, as in: 


    Scope([Calculation].[Calculation].[Previous Period]);      
        ([Calendar].[Calendar].[Date]) = 7777;
    End Scope;  


    I had done this with this MDX code in an attempt to see what was going on, and it showed me that the scoped overwrites were working, but the calculations they were over-writing with were not. Then I got an idea to change how I was debugging: 


    Scope([Calculation].[Calculation].[Previous Period]);      
        ([Calendar].[Calendar].[Date]) = 1;
        ([Calendar].[Week].[Calendar Week].MEMBERS) = 10;
        ([Calendar].[Calendar].[Calendar Month].MEMBERS) = 100;
        ([Calendar].[Calendar].[Calendar Quarter].MEMBERS) = 1000;
        ([Calendar].[Calendar].[Calendar Year].MEMBERS) = 10000;
        ([Calendar].[NRF].[NRF Week].MEMBERS) = .0001;
        ([Calendar].[NRF].[NRF Period].MEMBERS) = .001;
        ([Calendar].[NRF].[NRF Quarter].MEMBERS) = .01;
        ([Calendar].[NRF].[NRF Year].MEMBERS) = .1;
    End Scope;      

    Essentially I am using a bit-mask, one position for each level. I split the two different hierarchies at the decimal. This allowed me to see which scoped overwrite statement was in effect as I browsed the cube, and it led me to the solution.

    It turns out I had defined my scoped overwrite assignment statements in reverse order. (i.e. from highest level (Year) to lowest (day)) Once I switched them so the highest level statement was evaluated last, everything worked as desired. This was a very stupid mistake, and once it was defined as part of the cube MDX script, was a difficult one to spot. Luckily the debugging technique described above made it pretty obvious what was happening.

    Posted in Analysis Services, MDX, OLAP, SQL Server | Leave a comment