Hyper-V on the laptop (Compaq nc6320)

I want to run Hyper-visor and other software (SQL Server 2008) on my laptop. I consider running Team Foundation Server, Windows XP, SQL Server versions, old Visual Studio versions, Visio, BizTalk and others inside Hyper-V images on my laptop. At the same time I want the laptop to function as normally (internet explorer, media player, office, etc).

Hyper-V runs on 64-bit Windows Server 2008. The machine needs to support hardware-assisted virtualization and data execution prevention, and these must be enabled in BIOS.

1. Download driver packages for Vista x64 and save on flash-drive
   
2. Install Windows Server 2008
   
3. Rename computer
   
4. start control userpasswords2 and enable automatic logon
   
5. Install drivers and enable wireless service (not automatic on server)
   
   1. Install Intel Graphics sp37719
      
   2. Install "Integrated module with Bluetooth wireless technology for Vista" SP34275 don't care if it says "no bluetooth device"
      
   3. Replace wireless driver: \Intel Wireless sp38052a\v64
      
   4. Add feature "Wireless..."
      
6. Connect to internet
   
7. Start updates
   get updates for more programs
   automatic
   important updates, only
   
8. View available updates
   Add optional: Hyper-V
   
9. Install Hyper-V
   
10. Add role Hyper-V
    

The machine is now a Hypervisor. Next steps are to get Windows Server 2008 to act more like a client OS.

1. Install other drivers
   
   1. SoundMAX SP36683
      
2. Install Feature "Desktop Experience" (Media Player, etc)
   
3. Create shortcut "net start vmms" for starting Hyper-V and "net stop vmms" for stopping Hyper-V
   
4. Set startup for service "Hyper-V Virtual Machine Management" to manual
   
5. stop vmms
   
6. Install Virtual PC 2007 (in case I want wireless network from a vm)
   
7. Enter product key and Activate windows
   

Since I have a working version of Windows, this is the time to activate, next step would ideally be to install office. I don't have office handy (am on vacation), so I install the viewers for Word, PowerPoint and Excel

1. Word viewer
   
2. PowerPoint Viewer
   
3. Excel viewer
   

I am now ready to install Visual Studio and the servers, first out is SQL Server 2008 rc.

1. Set up IIS (with all the iis6 stuff)
   
2. Set up Windows SharePoint Services 3.0 x64 with Service Pack 1 to run "stand-alone"
   
3. (optionally) set up Windows SharePoint Services 3.0 Application Templates – 20070221
   
4. Create user ServiceAccount to run all the sql server services
   
5. Install SQL (complete) with RS in SharePoint integrated mode
   
6. Open firewall (http://go.microsoft.com/fwlink/?LinkId=94001)
   open tcp 1433 (engine), 2383 (ssas), 80 (sp), 135 (ssis), 443 (https), 4022 (service broker)
   udp 1434 (ssas)
   and "notify me when new program blocked"
   

Now get all updates.

Next step is to install Visual Studio, which should be straight-forward.

OK, I'm off to the beach,
Gorm

CMD: redirect error output

I write quite a few batch-files these days and want the error messages to be supressed. I had a vague recollection telling me that this was possible, and found it googling, so here it is:

"2>nul".

In a sample:

Del *.log /f /q 1>nul 2>nul

Deletes all log-files, hiding output (1>nul) and error output (2>nul). If I remember correctly (3>nul) was hiding printer output. In the old days. You can, of course redirect to files as well:

Del *.txt /f /q 1>out.txt 2>err.txt

Del *.log /f /q 1>>out.txt 2>>err.txt

This sample is supposed to put all the output in out.txt and all the errors in err.txt.

Gorm

ETL top-down 1 – Architecting abstraction layering

I work at a very exciting BI-program where we have been able to do things right. The project is part of a larger program that contains Business Consulting, Master Data Management and Business Intelligence and, who knows, maybe even Information Lifecycle Management.

My work on the project includes ETL on the Enterprise Data Warehouse, a central project in the program.

My trade is, and has for some time been architecting developer. This gives me advantages early in the ETL process when it comes to what I like to call "Abstraction Layering"

Abstraction Layering – customer perspective (why)

Abstraction layering helps to set the balance between "keeping things open" vs. "delivering as soon as possible".

For my current project we need to deliver quickly while handling a few issues (from the top of my head):

• Loosely defined long term goal – I think
  
• Distributed developers, both geographically, and experience-wise
  
• Many source systems
  
• Large master data management and other projects changing the environment
  
• Real-time
  
• Right time
  
• Traceability
  

When implementation is done, we need to focus on simple measurable tasks. One way to do this is to model the work on well defined levels of abstraction. This way we can design top-down by having the most abstract discussions first, then some intermediate discussions and lastly the implementation details.

Things we do interesting to ETL includes:

1. Selecting reference architecture.
   Master Data Management, Hub and spoke EDW with 2G, full archive of source systems, data marts, custom Meta Data Repository.
   
2. Create ETL "horizontal" layering – interfaces and documentation.
   Packages take data from one architecture layer to another, grouping functionality and enabling measurability.
   
3. Create ETL "vertical" layering – restrictions and grouping.
   Jobs uses "job packages" uses "aggregated packages" that groups functionality in measurable chunks.
   
4. Specify update intervals and delivery.
   We plan for nightly job, hourly job and real-time job. Monthly, weekly reports, operational BI and more.
   
5. Define deployment, operations, etc.
   Operations implements ITIL, we should interface with it as it matures.
   

We deliver.

Abstraction Layering – architect perspective (how)

Architecting abstraction layering is done to serve the data integration projects by empowering a few roles, these includes:

• Project manager
  Work breakdown structure gets easier because one for any integration task have some nice metaphors.
  
• Developer
  Gets assignments with a predictable scope.
  
• Tester
  Can reuse tests because many atomic items has the same scope.
  

The architect gets a bag of expression for reuse in the modeling of all the ETL jobs and test templates. It gets possible to create templates for kinds of functionality used often or placeholders for functionality other systems depends on.

Abstraction Layering – project manager perspective (what)

The developer lead gets measurability and some nice metaphors from the abstraction layering, in our current project they are

• Job
  Roughly equivalent to executable, e.g.: "Nightly job".
  
• Agg
  Typically one for each of the different states a job goes through, e.g.: "Source extract"
  
• Fun
  Specific function for an entity, e.g.: "Extract <<customer tables>> from source system X"
  
• Task / Tsk
  Task is part of a function, it moves, changes or collects data. Data in the warehouse knows where it comes from by reference to a such task id, a sample task might be "Extract customer address from source system X".
  

The project manager must choose when these metaphors are appropriate, of course.

Abstraction Layering – developer perspective (what)

When assigned a task a developer can by the name of the delivery see how it fits into the wide picture on three dimensions

• Job/Agg/Fun/Tsk
  Dictates the level along the low-level to high-level axis.
  
• Context
  Horizontal layers in the architecture touched, for instance SourceDsa or DmsaDm.
  
• Function
  Typically the 'T' in "ETL".
  

Most work repetitive by nature should have current templates controlled by the architect.

Abstraction Layering – test perspective (what)    

Too early to say, but it looks like a combination of the abstract layering, "data haven" and MR shall make test-driven development beyond simple testing possible. Looks like integration and performance testing shall come relatively cheap.

Fading out

OK, this grew pretty long, looks like I'll have to do more on parts of this later, with more concrete samples. Hope I find the time.

G.

Business Intelligence VPC Release 6

This is very nice indeed. You need at least 2GB RAM and you really want at least 3GB.

•	Overview
•	Business Intelligence VPC Release 6.part01.exe
•	Business Intelligence VPC Release 6.part02.rar
•	Business Intelligence VPC Release 6.part03.rar
•	Business Intelligence VPC Release 6.part04.rar
•	Business Intelligence VPC Release 6.part05.rar
•	Business Intelligence VPC Release 6.part06.rar
•	Business Intelligence VPC Release 6.part07.rar

 

1) Download and unpack (by running the .exe). This image runs fine from USB disk (for me, anyway), I never use compression (some do).

2) Download and install Virtual PC 2007 (google is your friend)

3) Configure your hardware (turn on virtualization in BIOS if you can, and the protection flag too)

4) Create your own .vmc file (choose new vm in Virtual PC), set RAM size to about 2/3 of your total RAM and remove network adapter. I'm told that the amount of RAM should be dividable with 64M, can't really back that up with knowledge or measurements, but I do it anyway.

5) Run the image

6) Log on with "Administrator" and "pass@word1", remember that you have English keyboard in the image (I use Norwegian on the mother system, so " becomes @...)

7) Enable auto logon (see older post).

8) Disable System Event Tracker (older post)

From now on just turning on the image will get you ready to play with…


Presenter Scripts

Business Intelligence "All-Up" Presenter Script

    Financial Analyst

    VP of Sales

    Sales Representative

    Chief Financial Officer

    Data Analyst

2007 Microsoft Office System Launch Demo

Data Mining Deep Dive

Enterprise Manufacturing Intelligence Demo

Banking Demo

Federal Government Demo

State & Local Government Demo

Healthcare Demo

Oil & Gas Demo

Retail Demo

Project REAL Sample Demos

SQL Server 2005 Demos

PerformancePoint Launch Demo

PerformancePoint Budgeting Demo

PerformancePoint CRM Demo

PerformancePoint MAP (Monitoring, Analytics, and Planning) Demo

PerformancePoint Planning Demo

PerformancePoint Planning Consolidation Demo

PerformancePoint Management Reporter Demo

PerformancePoint Sales Forecasting Demo

PerformancePoint Strategic Planning Demo

OfficeWriter Demo

PerformancePoint Hands-On Labs

New Account Load Demo

 

Applications Installed

Windows Server 2003 R2 Service Pack 2

Internet Explorer 7

Office Enterprise 2007

Office Visio 2007

SharePoint Portal Server 2007

SQL Server 2005 Service Pack 2 (w/ SSIS, SSAS, SSRS)

SQL Server 2005 Data Mining Add-ins

SQL Server 2005 SharePoint Integration Add-in

ProClarity Analytics Server 6.3

ProClarity Dashboard Server 6.3

ProClarity Desktop Professional 6.3

ProClarity Web Professional 6.3

PerformancePoint Planning Server 2007

PerformancePoint Planning Add-In for Excel

PerformancePoint Planning Business Modeler

PerformancePoint Monitoring Server

PerformancePoint Monitoring Scorecard Builder

OfficeWriter for Word Plug-In

OfficeWriter for Excel Plug-In

Project REAL Reference Implementation

Business Intelligence Metadata Samples

 

Sample Databases / Cubes

AdventureWorks

AdventureWorks_Mfg (for Lean Manufacturing)

AdventureWorksDW (for AdventureWorks data warehouse)

Camden (Local Government)

Contoso (for PerformancePoint Planning)

EP

Government_AppDB (PerformancePoint Planning)

HC

Healthcare_AppDB (PerformancePoint Planning)

OSS (Healthcare)

PDW (for PerformancePoint Planning)

PeoplSoft Financials_AppDB (PerformancePoint Planning)

PPS Launch (for PerformancePoint Planning)

PSFIN_Data_Mart

REAL_Source_Sample_V6 (for Project REAL)

REAL_Warehouse_Sample_V6 (for Project REAL)

 

Personally I'll jump on "Business Intelligence Metadata Samples" first (as it is most relevant to what I do now). Hopefully I'll blog more about that later

 

Enjoy until August 17. 2009
Gorm Braarvig

Enable auto-logon

Enabling automatic logon for Windows can be done in two ways (that I know of) for installations connected to workgroup and one way for installations connected to domain.

This works for Windows XP, 2003, Vista, 2008 and most likely NT 4 and Windows 2000. This will not work for Windows 9x/ME.

1. The mouse-clicking way (should work with workgroup installations, only)

Start -> Run -> "control userpasswords2"

Opens up a GUI where you should be able to turn off "Require users to log on".
This also works on Windows Server 2008 Core.

2. Edit registry

This can be done with regedit.exe or reg.exe (Start -> Run -> "cmd /kreg.exe").

The values to change or add are under:

HKLM\Software\Microsoft\Windows NT\CurrentVersion\Winlogon

They are:

AutoAdminLogon REG_SZ "1"
DefaultUserName REG_SZ "Username"
DefaultPassword REG_SZ "Password"
DefaultDomainName REG_SZ "Domain or machinename"

One easy mistake to make here is to define AutoAdminLogon as REG_DWORD (if you are used to program against registry, that is): This will not work; AutoAdminLogon should be of type REG_SZ.

Obviously you should replace "Username" with a username and "Password" with a password.

"Domain or machinename" should be replaced with name of machine if you log in with a local account (always if you use workgroup) or shorthand domainname if you log in to a domain.

Warning: Your system administrator might not be impressed with having your username and password here. Always adhere to company policy in this regard.

 

Remember that if your machine often fails with blue screen, power failure or disk removal (typical when you run virtual from external drive and disconnect the drive), you want to disable the shutdown event tracker warning, see post bellow.

 

Regards,
Gorm Braarvig

Disable shutdown event tracker warning

Problem: unwanted display of shutdown event tracker

Working with vpc images, I some times turn off the images without caring about "clean" shut down. This enforces a warning the next time I boot. Since I some times use autologon (later post), this is annoying.

Solution: Disable "Display Shutdown Event Tracker"

1. Start "Group Policy Object Editor"

Start -> Run -> gpedit.msc:

Navigate to the setting:

Select "Disabled" and press OK:

Result: "Shutdown Event Tracker" is not displayed anymore.

Gorm Braarvig

Coconuts and seeded grapes

If you can't see the picture: stop reading, you are wasting your time.
img src=xksd.com

I used to do "custom bi", creating my own import programs (in a multitude of languages with more technologies than I care to recall), using Excel and print outs for analysis.

I now do Microsoft BI.

Loose analysis of Gartners quadrant should place custom BI close to coconuts (bellow to the left of the visible range) and MS BI close to the seeded grapes (moving towards peaches).

 

Gorm Braarvig

SQL Server - Drop database, drop current connections first

Hi!

I write scripts to generate my database, I want my scripts to run from command line using SQLCMD and (at the same time) use SSMS to edit the scripts and some times run them there. This creates a problem when dropping databases. Databases will not be dropped while they have open connections.

The solution is easy; here goes:

ALTER DATABASE x SET SINGLE_USER WITH ROLLBACK IMMEDIATE

In a sample:

PRINT 'Recreate database...'
IF EXISTS (SELECT name FROM sys.databases WHERE name='
Some_DB')
BEGIN
    ALTER DATABASE Some_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE Some_DB
END
GO
CREATE DATABASE Some_DB
GO

Gorm Braarvig

SQL Server 2005: High definition timer (technical)

Timing with extreme precision

I) Preface

I played with writing a generic metadata repository (prototyping) to prepare for ETL at Nortura. I'm not doing the meta data design, but want to understand what we talk about, and to get some experience it's one of two ways I could think of doing it:

1. Adaption - "Monkey see monkey do": Watch other do stuff and try to copy the submodalities (did I write this)
   
2. Brute force - "Trial and error and success": Try, fail, try again, fail a little less, try again, succeed.
   

I want to do both.

a) Brute force

I started with the hands-on and created a generic meta data table with support for versioning (of the model) and hierarchy:
(
GenMetumId
,GenMetumType
,GenGrupperendeMetumId
,GenMetumForrigeId -- GenMetumPreviousId
,GenMetumGyldigFra -- GenMetumValidFrom
,Navn -- Name
,EkstraXml
,StartTid -- StartTime
,SluttTid -- EndTime
,Suksess -- Success
,Nyeste -- Newest
)
then I created some stored procedures and tried to insert some rows and update them with status etc. surprise surprise:

The timestamps were mostly equal!

BOL reveals that datetime has a resolution of some 3 ms, but this does not account for the results I got. My resolution was 10-15 ms, which to me is an etarnity (I used to do low-level stuff)

This needs to be fixed. By me. Off hours. (as in hobby).
See solution in point II).

b) Adaption

I am so lucky that I am to implement ETL on one of Europes largest BI initiatives, so I'll get to be good with operational or technical meta data (I'm not versed in the jargon, yet)

 

II) Implementation (my favourite)

(I know design preceedes implementation in real life, but we're playing here)

 

a) A better .NET "DateTime.Now"

I know DateTime.Now is sufficient (10M ticks per sec). but we are playing, so let's do it properly.

WINAPI QueryPerformanceCounter

I've written some timing routines far back for Windows CE, because the .NET timer routines does not work very well on Pocket PC and Smartphone (resolution is not good). QueryPerformanceCounter and QueryPerformanceFrequency are the WINAPI methods of reading the OEM high resolution counter, which is the lowest level of time-taking on modern computers.

#region "Timer routines"
[DllImport("kernel32.dll")] static extern public bool QueryPerformanceCounter(out UInt64 i64);
[DllImport("kernel32.dll")] static extern public bool QueryPerformanceFrequency(out UInt64 i64);
static protected UInt64 i64Freq = 0;
public static UInt64 GetNowI64()
{
UInt64 i64;
if (!QueryPerformanceCounter(out i64))
i64 = 0;
return (i64 * 10000000) / GetFreq();
}
public static UInt64 GetFreq()
{
if (0 == i64Freq)
if (!QueryPerformanceFrequency(out i64Freq))
i64Freq = 1;
return i64Freq;
}
protected static DateTime dtBaseline = DateTime.MinValue;
protected static UInt64 i64Baseline = 0;
public static DateTime GetNowDt()
{
UInt64 i64Now = GetNowI64();
DateTime dt;
if (dtBaseline == DateTime.MinValue i64Baseline == 0 i64Baseline > i64Now)
{ // We have no reference point, or reference point is no longer sufficient: create one.
dtBaseline = DateTime.Now;
i64Baseline = i64Now;
dt = dtBaseline;
}
else
{ // We have a sufficient baseline, return baseline plus timespan for diff
dt = new DateTime(dtBaseline.Ticks + (Int64)(i64Now - i64Baseline));
}
return dt;
}
#endregion "Timer routines"

Converting these routines to SQL CLR is a non-brainer.

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlDateTime GetTime()
{
//return (SqlDateTime)DateTime.Now;
return (SqlDateTime)GetNowDt();
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString GetTimeAsString()
{
return (SqlString)(GetNowDt().ToString("O"));
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt64 GetFrequency()
{
return (SqlInt64)(Int64)GetFreq();
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt64 GetTickCount()
{
UInt64 i64;
if (!QueryPerformanceCounter(out i64))
i64 = 0;
return (SqlInt64)(Int64)i64;
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt64 GetMs()
{
return (SqlInt64)(Int64)GetNowI64();
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt64 GetTicksPast(Int64 zero)
{
UInt64 uiRes = GetNowI64() - (UInt64)zero;
return (SqlInt64)(Int64)uiRes;
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt64 GetMicrosPast(Int64 zero)
{
UInt64 uiRes = GetNowI64() / 10;
uiRes -= (UInt64)zero;
return (SqlInt64)(Int64)uiRes;
}

 

SQL Server import:

CREATE ASSEMBLY MeasureTime AUTHORIZATION [dbo] FROM ... WITH PERMISSION_SET = UNSAFE
CREATE FUNCTION Meta.GetFrequency() RETURNS bigint AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetFrequency
CREATE FUNCTION Meta.GetMicrosPast(@zero bigint) RETURNS bigint AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetMicrosPast
CREATE FUNCTION Meta.GetMs() RETURNS bigint AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetMs
CREATE FUNCTION Meta.GetTickCount() RETURNS bigint AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetTickCount
CREATE FUNCTION Meta.GetTicksPast(@zero bigint) RETURNS bigint AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetTicksPast
CREATE FUNCTION Meta.GetTime() RETURNS datetime AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetTime
CREATE FUNCTION Meta.GetTimeAsString() RETURNS nvarchar(MAX) AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetTimeAsString

Testing with some simple queries shows me that it worked. (hope link to image is not broken)



Green arrows
These demonstrates that you can get a higher granularity on timestamps than using GetDate() with Meta.GetTime(). GetDate jumps from ...573 to ...587, while Meta.GetTime() jumps 2, 4 and 6 ms. Two other facts stands out: times differ, so .NET-time seams not equal to T-SQL time, update of 4ms means that the granularity of datetime can't be exactly 3ms.

Blue arrows
GetTicksPast() (10 000 000 pr sec) and GetMicrosPast() (1 000 000 pr sec) works as expected. Calling CLR, getting the time and putting it into a cell in temp looks to take less than 50 microseconds or less than 1/20000 second, which is faster than I expected (but we have very desent laptops).

Red arrows
Putting the same functions multiple times on the same SELECT ensured me that they are called every time (without caching). I particulary liked GetTimeAsString() which measured time in readable format exact with seven digits after secounds... higher granularity than GetDate(), no?

III) What's the business case
You tell me, I just needed to write som code and wanted to share the result.

IV) Where is the code
http://www.codeplex.com/SqlClrMeasureTime
this got kind of nerdy, next one will be better
have a nice week end

Gorm Braarvig

SQL Server 2005: High definition timer (technical)