Cloud Data Integration

by Administrator 4. May 2014 12:15
Just as the internet unified access to data, Cloud Data Integration will unify changes to data.

Today we expect to lookup anything instantly, but being able to change or contribute to that data is another story. Ignoring security and ownership issues, tagging, annotation, and consolidation of data remains elusive due to protocol and system boundaries.

Cloud Data Integration will enable data sharing on a global scale for the first time. True data integration for data, process, applications, and architecture are now possible by leveraging commodity infrastructure from companies like Amazon and Google.

For business, historical data exchange has been about local integration and point to point gateways and networks. Now Service Oriented Architecture, Infrastructure as a Service, Platform as a Service, and Software as a Service are compelling leading organizations to plan and execute their move into the global process space of the cloud.

The economies of scale found in the cloud are unprecedented. Standards like multi-tenant elastic infrastructure, operational subscription model pricing, and single connection integration will ensure a fundamental change in business technology and profit.

Technology is a means to an end. The migration away from owned infrastructure will happen over time, encouraged by changes in risk, expense, and competition. Hybrid integration will be critical path, and synergies across on-premises and cloud to cloud tenets will be leveraged.

Common cloud integrations scenarios like mail, calendar, HR, and CRM point the way toward a near future where data integration, transformation, security, and management are truly transparent.


Adding ID and ParentID Attributes to XML with XSLT

by Administrator 17. September 2013 10:10
When transforming XML between applications,
sometimes it's necessary to explicitly disambiguate node paths.

This stylesheet adds id and parentid attributes:

<xsl:stylesheet version="2.0" xmlns:xsl="">
<xsl:template match="@*|node()">
    <xsl:attribute name="id">
      <xsl:apply-templates select="." mode="number"/>
    <xsl:attribute name="parentId">
      <xsl:apply-templates select="parent::*" mode="number"/>
    <xsl:apply-templates select="@* | node()"/>
<xsl:template match="*" mode="number">
  <xsl:number level="any" count="*"/>

Note that this stylesheet works well as V1 XSLT as well.



by Administrator 30. August 2013 11:40
Some thoughts on NoSQL


- Term coined in 1998, Mostly originated out of a Web 2.0 need
- Mostly re-packaged \ re-branded existing technology
- Used in one way or another by:
- Facebook, Twitter, Digg, Amazon, LinkedIn, Google, Microsoft


Is (generally)

- For interconnected data (links, pingbacks, tags etc..)
- For hierarchical nested data
- Designed for horizontal scale (scale out, distributed)
- For leveraging commodity hardware
- Open Source
- Designed to complement traditional SQL

Is Not (generally)

- Relational
- A replacement for relational
- Turn key

No SQL Design Goals

Structured storage and retrieval of data
- Un-structured data storage, e.g. text files, is generally not scaleable
Looser consistency model than relational

- Data corruption may occur compared to 2-phase commit depending on implementation
- May not be appropriate where consistency is required, e.g. financial

Motivations (not necessarily justifications)

- Design simplicity
- Horizontal scaling
- Availability control
- Optimized for read
WW compressed data currently at 1K ExaBytes

- One thousand billion gigabytes
- Scalability and consistency now equal in priority

Common Related Architecture Patterns

Map / Reduce
- Mapping data across a distributed cluster
- Reduce data to a summary for output
- Very hard to avoid contention at consolidation points
- Scale out not well supported in traditional SQL databases
REST (Representational State Transfer)
- Design pattern which maps a standard set of verbs to resources \ methods
- Simpler than SOAP, which requires custom implementation on both client and server
Thrift (Apache)
- Cross language service development
- Software stack + code generation engine
- Used by many No SQL vendors to provide cross language support and RAD
XPath \ XQuery \ XSLT
- XPath - Language for selecting nodes from an XML document
- XQuery \ XSLT Languages for transforming XML to other formats, e.g. HTML
- General default data format
No SQL Database Types

Column Store
- Entire column indexed, serialized and compressed
Document Store
- BLOB storage of document as object
Key Value
- Schema-less name value pair storage
Graph Databases
-Index free adjacency of directly related nodes
Multimodel Databases
- Designed to support multiple data models and use-cases
Object Databases
- Data stored and used as objects
Cloud Databases
- Deployment model in cloud, can be SQL or No SQL
XML Databases
- Data stored as XML
Multidimensional Databases
- Relational data aggregated across dimensions
MultiValue Databases
- Relational with a focus on list type parameters

Column Store

All the values of the column are serialized together
Leverages locality of reference \ proximity
Columns are sparse, no data = 0 bytes
When combined with dynamic compression this can result in very fast data retrieval, e.g. find all employees with name = ‘smith’
Efficiency vs. row store depends on the workload
Can be less efficient than traditional row store when all columns are returned
Compression leverages repeating bytes, and common column data type
Compare SQL Server Column Store index type
Examples: Hadoop, BigTable, Cassandra, Hypertable

Document Store

Designed for document-oriented information
Aka semi-structured data
Main object is the ‘document’
Readable encodings
XML (hierarchical tags)
YAML (indentation based)
JSON (key \ value pairs)
Binary encodings
BSON (binary JSON)
MS Office
Compare SQL Server FileTable table type
The next great DW challenge
Examples: MongoDB, Elasticsearch, CouchDB

Key Value

Aka Associative Array
Schema-less storage, reduced code impact of changes
Operations: pair add, pair delete, pair update, key lookup
Traditional implementations
Hash table (using hash function to map keys to values, and handle collisions)
Binary search trees (size, depth, root id, leave ids)
B-Trees (balanced tree, keeps data sorted), e.g. SQL Server index
Compare to SQL Server XML data type column (sort of)
Examples: DynamoDB, Azure Table Storage, LevelDB

Graph Databases

Use graph structure with nodes, edges, and properties to represent and store data
Any storage system that provides index-free adjacency
Each node contains a direct pointer to it’s adjacent node
Edges are the connections between nodes
Most important information in contained in the edges
Examination of edges, nodes, and properties yield meaningful patterns
Examples: Neo4J, Infinite Graph, InfoGrid, Trinity

Multi-model Databases

Support characteristics of multiple databases
Presents a variety of logical models \ views to the user
Uses industry-standard interfaces, e.g. XML
Schema agnostic
True multi-model databases designed for multiple models and use-cases
Examples: FoundationDB, Aerospike, OrientDB, ArangoDB
General-purpose databases with multi-model options
Examples: MySQL, DB2, Akiban, PostgreSQL

Object Databases

Data stored as objects
Processed using object-oriented programs
Database is integrated with the programming language
Competing design directions
Inheritance like C++
Encapsulation like XML
Example languages: Delphi, Ruby, Python, Perl Java, C#, C++
Examples: Versant, db4o, Objectivity, Starcounter, Perst

Cloud Databases

A distributed deployment model rather than a database type
Can be SQL or No SQL
Could be a VM, or hosted storage
May focus on throughput rather than storage
Example vendors: Amazon EC2, GoGrid, Rackspace, MS SQL Azure
SQL cloud service examples: Amazon (MySQL), MS Azure, Heroku
No SQL cloud service examples: Amazon DynamoDB, Google App Engine, MongoDB

XML Databases

Data stored as XML
Can be verbose
Supports select, export, serialization
Usually associated with Document databases
XML Enabled : converts to and from XML, e.g. SQL Server XML data type
Native XML : uses XML documents as the unit of storage, which may be text files

Multidimensional Databases

Aka OLAP (on-line analytical processing)
Variants of relational databases
Data represented as cubes with N dimensions
Each cell in a cube is an intersection \ aggregate of data along it’s dimensions
Uses relational data as the data source
MOLAP : (M = multi-dimensional), filters and aggregates pre-calculated
ROLAP : (R = relational), filters and aggregates created at run-time

MultiValue Databases

Support and encourage the use of attributes which can take a list of values
Data model pre-dates the relational model
Developed for the PICK OS in 1960, still in use
Fits in No SQL because SQL is not required
Examples: Rocket Software (U2), TigerLogic, jBase, Revelation


Polyglot persistence can’t persist
Companies must consolidate
No SQL vendors must add SQL and transactions
SQL vendors must add document and key-value support


Server Site Web Part IFrame Rendering

by Administrator 19. January 2013 18:02
Need an easy way to render an IFrame from the server ..

Check out this example:

protected override void Render(HtmlTextWriter writer)

string frame = "outputFrame";

writer.AddStyleAttribute("display", "inline");

writer.AddAttribute(HtmlTextWriterAttribute.Id, frame);

writer.AddAttribute(HtmlTextWriterAttribute.Name, frame);

writer.AddStyleAttribute(HtmlTextWriterStyle.Width, "100%");

writer.AddStyleAttribute(HtmlTextWriterStyle.Height, "100%");

writer.AddAttribute("frameBorder", "0");

writer.AddAttribute(HtmlTextWriterAttribute.Src, url);






Publishing Database Projects with Visual Studio 2012 and MSBuild

by Administrator 11. August 2012 11:13
A common task for database administrators is to automate building and publishing of database projects.

With Visual Studio 2012, this automation is even eaiser.

Visual Studio 2012 provides a platform for building and publishing database projects using a combination of MSBuild.exe and sqlcmd.exe.

A prerequisite for building and publishing database projects with MSBuild are the SQL Server Data Tools, which can be downloaded here

Also, a publish profile must be created beforehand in Visual Studio to create the .publish.xml file.

A typical process for building and publishing a database project might contain the following steps:

1) Drop the existing objects in the database

(a clean database will cause the publish to generate create statements, instead of alter statements)

2) Build the project

(creates the .dacpac, and .dll files in the debug dir)

3) Publish the project

(creates the .publish.sql file)

4) Create the database objects

(execute the publish.sql file to create the schema)

5) Insert domain data

(insert static domain (lookup) data, e.g. country or state)

6) Import instance data

(execute an import process to populate business object data, e.g. product or customer)

7) Insert test data

(insert test data to support the development process)

Following is an example CMD to process the above steps:

@echo off



ECHO ===============================================================

SET /P AREYOUSURE=You are about to rebuild %1, Are you sure (Y/[N])?


ECHO %1 build started : %time%



SET SERVER=your server Name

REM parm to control target DB of staging data import

REM passed to script variables via sqlcmd


SET STAGEDB=your staging DB

SET SCRIPTSPATH=your scripts dir, e.g. domain data create

SET DROPFILE=your drop schema script file name

SET SCHEMAPATH=your DB project\bin\Debug\

SET PUBLISHPROFILE=your project dir\%TRANDB%.publish.xml

SET SCHEMAFILE=your publish schema file

SET DOMAINFILE=your domain data insert script file name

SET TESTDATAFILE=your test data insert script file name

SET PROJECT=your project file

REM clean build target

DEL /F /S /Q your project dir\bin\Debug\*.*

ECHO ========= drop schema =========

sqlcmd -S%SERVER% -d%TRANDB% -i%SCRIPTSPATH%%DROPFILE% -E -v DatabaseName=%1

REM build

msbuild /t:build your project name.sqlproj /p:Configuration="Local" /p:UpdateDatabase=False


REM publish to file

msbuild /t:publish .sqlproj /p:SqlPublishProfilePath=%PUBLISHPROFILE% /p:Configuration="Local" /p:UpdateDatabase=False /p:OutputPath=%SCHEMAPATH%

ECHO ========= create schema =========

sqlcmd -S%SERVER% -d%TRANDB% -i%SCHEMAPATH%%SCHEMAFILE% -E -v DatabaseName=%1

ECHO ========= insert domain data =========

sqlcmd -S%SERVER% -d%TRANDB% -i%SCRIPTSPATH%%DOMAINFILE% -E -v DatabaseName=%1

ECHO ========= run data import =========

sqlcmd -S%SERVER% -d%STAGEDB% -Q"exec _dev_run_stage_sp %1" -E

ECHO ========= insert test data =========

sqlcmd -S%SERVER% -d%TRANDB% -i%SCRIPTSPATH%%TESTDATAFILE% -E -v DatabaseName=%1

ECHO =====================================

ECHO %1 build complete : %time%


With Visual Studio 2012, we now have complete end to end integration of build and publish for database projects.


Visual Studio 2011 Beta Notes

by Administrator 8. March 2012 18:30
The beta of Visual Studio 2011 has been made available on MSDN Developer Downloads

This build is promoted as 'production ready', including round tripping (backward compatibility) without conversion of existing Visual Studio 2010 project files. Note that project files prior to VS 2010 SP1 will require conversion.

My test configuration was: a Dell Precision M4400 x64, Windows 7 Pro, Visual Studio 2010 Pro, SharePoint Server 2010, SQL Server 2008 R2.

After creating an image of my partition, I started the web based install of the VS2011 Beta.

One of my favorite test cases is how setup recovers from failed installs, so I cancelled the web install in the middle, and then deleted the target folders from Program Files and Program Files (x86).

I then re-started the Web based install, which completed but raised various errors at the end, after which 2010 failed to start with no specific error, and 2011 failed to start with an error that its Side by Side configuration was invalid. This showed that the VS2011 Setup is unable to automatically clean-up prior failed installs, which is not surprising but hope springs eternal.

To minimize connectivity issues, I decided to move on to the ISO install. After reapplying my base image, and downloading the ISO from MSDN, I restarted Setup. This time setup completed without error, and in far less time then previous versions of Visual Studio.

During the install, I selected the option to have my VS 2010 settings migrated to 2011. After ignoring the big LAUNCH link, and rebooting, I started VS 2011. The IDE loaded very quickly compared to 2010, and I was presented with the new look and feel.

The first thing I noticed was the almost total lack of color in the UI. While a few controls and menu items are colored, most are back and white. This gives the UI a very consistent and clean look, which I think I’ll continue to appreciate. The conversion of my VS 2010 settings did bring over my external tools menu items, and my custom tool bar, but failed to add the commands to the toolbar. However I was able to easily re-add them via Tools \ Customize.

I then opened, built, and deployed my test SharePoint Web Part solution in VS2010 to confirm that nothing was broken. When everything worked fine in the prior version of VS, I then opened the solution in VS2011. I was very happy to see that project ‘round tripping’ did indeed work, as my VS 2010 project loaded, built, and deployed without conversion. Even more impressive was the fact that after saving the solution in VS 2011, the project continued to load, build and deploy in VS 2010. For the first time, we can test different versions of Visual Studio side by side without compatibility and usability issues. This is a huge win for Microsoft and the Visual Studio team!

Although I’ve obviously only scratched the surface of the product, Visual Studio 2011 Beta will continue to be my default Windows development IDE going forward, with the added security of being able to fall back to VS2010 as needed.


SharePoint 2010 Configuration Wizard Fails with 'IIS not Installed'

by Administrator 18. January 2012 11:30
SharePoint 2010 requires IIS 6.0 Management Capability to be enabled when used with IIS 7.0

If when IIS 7.0 is installed this option was not enabled, or later disabled, this error can occur.

When running the SharePoint Configuration Wizard on an IIS 7.0 installation, the wizard may fail with the error ‘IIS not installed'.

This misleading error actually means that the IIS 6.0 Management Capability option is not enabled in IIS 7.0

To enable it go to: Control Panel -> Windows Features -> IIS -> Web Management Tools -> IIS 6 Management Compatibility


Integrating a minimal ASP:Wizard control with SharePoint 2010

by Administrator 25. May 2011 16:00

On a recent project I needed to integrate the ASP:Wizard control with SharePoint 2010. The control hierarchy is:

SharePoint 2010 - > Visual Web Part -> Custom User Control -> ASP:Wizard Control

In this case, I wanted to simply use the Wizard control for state management, and suppress most of its UI and behavior. Navigation between the various Wizard step was accomplished by passing a query string on the URL called from the SharePoint Quick Launch into the Web Part, and then setting the Wizard active step in Page_Load:

if (Request.QueryString["ws"] != null)


int wizPage = Convert.ToInt32(Request.QueryString["ws"]);

       Wizard1.ActiveStepIndex = wizPage;


The Wizard Side Bar normally handles direct navigation between steps, however in this case because I replaced it with the SharePoint Quick Launch, I hid the Side Bar by adding DisplaySideBar="False" to the Wizard tag in the user control ascx file. This freed some client space and provided effective use of the Quick Launch control.


Because my work flow was non-linear, I also wanted to hide the previous, next, and finish buttons normally displayed on the Wizard steps. This was accomplished by using the Wizard tasks context menu on the Visual Studio 2010 design surface for the user control aspx page to apply the StartNavigationTemplate, StepNavigationTemplate, and FinishNavigationTemplate. After generating this markup, I added Visible="false" Enabled="false" to each of the three buttons to make them go away.


The final step of the wizard integration was to dynamically set the Title of the Web Part based on the name of the Wizard step in the Wizard Step tags. However, the server side code for the Web Part class generated by the Visual Studio project wizard adds a generic Control instance to the Controls collection.


To allow access to the parent Visual Web Part from the user control containing the Wizard, the default code:


Control control = Page.LoadControl(_ascxPath);



Was replaced with:


VisualWebPart1UserControl control =


control.ParentWebPart = this;



This allowed assignment of the Wizard step name to the Title property of the Web Part so:




The ASP:Wizard control can be seamlessly integrated with a containing Visual Web Part and SharePoint 2010. In a future blog, I’ll describe contextual customization of the SharePoint 2010 ribbon control, for execution of custom user actions within each wizard step.



SharePoint Turns 10

by Administrator 3. April 2011 19:18

Microsoft’s long term strategy for SharePoint can be hard to predict, or even follow, given the changes in functionality and transition to the cloud. When Microsoft first announced SharePoint 10 years ago, its set of underlying services were somewhat lacking in integration and alignment. Yet Steve Ballmer saw SharePoint as the platform for the future of the Office product line, and a potential long term replacement for Windows.

Now, SharePoint adds more than 20K users a day, and has spawned an entire ecosystem of add-ons, tools, partners, solution providers, and consultants. SharePoint 2010 provides developers with a real SDLC environment, and users with a rewritten interface and many usability enhancements. The fact that developers can host their coding on Windows 7 and Vista (x64 only) is a testament to Microsoft’s commitment to the future of the SharePoint platform.

Where will SharePoint go in the future? It is clear that communication and collaboration will be central to the success of teams and businesses, and SharePoint is laser focused on optimizing that experience. Recent surveys have shown that primary SharePoint initiatives within organizations are to increase departmental penetration and define a strategy; this indicates that SharePoint remains in the early adoption phase of its evolution.

As IT departments recognize the over-reaching complexity of their systems they will likely commit to a 1) reorganization 2) deprecation 3) innovation plan. Leaders responsible for this type of re-architecture can consider SharePoint as a Swiss army knife, and use it to fix a lot of the problems they will encounter along the way.


Visual Studio MSDN Topic in IE Favorites Issue

by Administrator 24. March 2011 15:54

If you have been having issues with the IE integrated MSDN help viewer breaking links stored in the IE favorites list after a re-start of the help agent:

This issue is resolved with the new help viewer included in Visual Studio 2010 SP1:




Log in
Privacy Policy Terms and Conditions Copyright Relational Development