Harlan's Business Intelligence Blog

You cannot specify alternate security options in Tabular mode

2013-09-11T09:39:00.005-07:00

I tried restoring a SQL Server Analysis Services (SSAS) tabular model using the SkipMembership security option (overwrite new roles, but don't overwrite role members) and got the following error:

You cannot specify alternate security options in Tabular mode. (Microsoft.AnalysisServices)

I had not heard that this was a limitation and was not able to find anything in Microsoft's TechNet Library documentation to suggest that alternate security options are not supported.

I have a project where I need to publish a tabular model backup file to a reporting server that lives on a different domain than my processing server, with completely different security settings. I've ruled out synchronization because I basically only have one-way communication, and the destination (reporting) server actually pulls from the source (processing) cube server during a synchronization and uses Windows authentication, which won't work for me due to the different domains and firewall settings.

So basically my only option is to do a backup, copy, and restore to publish my cube to the reporting server. Here's what I ended up doing for now. Not sure I like it, but it works for what we need.

1. Backup cube on processing server.
2. Copy .abf backup file to reporting server
3. Restore .abf backup file (with invalid security and all)
4. Run an XMLA command on the reporting server to update SSAS role membership

Let me know if you have any better ideas in the comments!

SQL Server 2014 Announced

2013-06-03T15:30:00.000-07:00

It's official - Microsoft announced SQL Server 2014 at TechEd today. According to the company's data sheet, here are the key BI features to look out for:

Project Codename "Data Explorer"
Windows Azure Marketplace
Windows Azure HDInsight Service & Microsoft HDInsight Server (preview)
Parallel Data Warehouse with Polybase
Excel in Office - PowerPivot & Power View
Project Codename "GeoFlow" in Excel
Data Mining Add-ins for Excel
StreamInsight
Data Quality Services
Master Data Services
Analysis Services
BI Semantic Model

Project Codename "Data Explorer" and "GeoFlow" will now officially be part of the SQL Server product offering, and there will likely be at least incremental enhancements or improvements to all of the other features mentioned.

For OLTP systems, the new in-memory OLTP (I assume this is Project Codename "Hekaton", which was discussed last year at PASS Summit) offers up to 10x performance gains over disk-based storage, according to Microsoft, and appears to support some interesting hybrid scenarios where some database objects can be in-memory and others on disk in the same database. Very cool.

Microsoft is also focusing more heavily on hybrid cloud scenarios with Windows Azure and how customers can take advantage of cloud storage for disaster recovery, backups, BI reports, etc. while keeping primary application databases on-premises.

At first glance, I'm glad to see that GeoFlow and DataExplorer are officially coming into the fold. It has been a lot of fun playing with these so far, so I'm excited to see these at work in real-life scenarios with some of my clients.

Polybase looks very cool for companies who are running PDW or plan to make the switch. It's incredibly powerful to be able to expose data in a Hadoop cluster to us lowly SQL analysts and developers so we can run SELECTs and JOINs and GROUP BYs without needing to care how or where the data is actually stored.

Update: Here is a thorough write-up on today's TechEd announcements from Mary Jo Foley at ZDNet.

Why I Still Use VirtualBox Over Hyper-V Manager in Windows 8

2013-04-18T14:50:00.005-07:00

I love that Hyper-V is built in to Windows 8. But I have some odd use cases, which still make Oracle's Virtual Box my go-to for launching virtual machines.

I am both a Mac and a PC user, so I need to share large files between the two, which requires formatting my external hard drive as exFAT. (ExFAT was developed by Microsoft to address incompatibility between Mac and Windows file systems - NTFS, the default Windows file system, is read-only on Mac; HFS+, the default file system for Mac OS can't even be read by Windows; and FAT32, Windows' predecessor to NTFS, had a file size limitation of 4GB.)

The only problem is that Hyper-V currently only supports mounting VHDs stored on NTFS drives. This means I can't mount any of the VHDs on my exFAT drive. So either I still have to carry around 2 drives and copy VHD files back and forth to sync them, or I can just continue to use VirtualBox. So, as much as I'd like to switch over and start using the wonderful features of Hyper-V, I'll have to wait until Microsoft supports exFAT, or someone invents a new file system.

As a side note, if you do use Hyper-V Manager to create virtual hard drives that you'd like to share with non-Hyper-V VM clients, be sure to set the virtual hard disk format to VHD. The default for Hyper-V, VHDX, will only work with Hyper-V, so you won't be able to mount the VHD on your Mac, VirtualBox, VMWare, etc.

Visio 2013 Fail - Microsoft Removes Key Database Modeling Features

2013-03-31T22:50:00.003-07:00

I complain enough as it is in my personal life, so I try to keep things pretty positive here. But Microsoft really screwed up this time. They removed all of the core database modeling features in Visio 2013.

Alberto Ferrari says it best in the comments:

C'mon guys, I wonder if you ever *really* listen to your customers.

Here's why this is a problem. Serious BI practitioners have used Visio for years to design and manage their data models. I'm a BI consultant and I have used Visio on an almost daily basis for as long as I've been working with data. One of the first things I do on a new client project is open up Visio, configure the drivers to connect to the client's database, and reverse-engineer the entire schema. As I meet with business and technology stakeholders, I use Visio to capture requirements as logical data models. I can manage attributes, change their data types, and even change drivers to change all of my data types from, say, SQL Server to Oracle.

But no more. Not with Visio 2013. Here's the rundown.

No ability to generate a diagram from existing code or a database definition (no reverse-engineer)
No ability to go from diagram to code or database (no forward-engineer)
No database drivers or data types

According to Microsoft, with Visio 2013 they "place a strong emphasis on diagramming and sharing instead of rigorous modeling." In my opinion, they took that too far. "Rigorous modeling" - you mean like defining data types? That doesn't seem all that "rigorous" to me. I was mad enough when Microsoft removed forward-engineer - not to mention alarmed at the potential implications, which have now gone from potential to quite real.

What this means, effectively, is that data professionals who upgrade to Office 2013 lose the ability to:

Design or manage a physical schema
Document existing data structures in any sort of automated way
Manage data types
Create indexes
Create database constraints
Create primary key/foreign key constraints
Manage drivers or custom data types
Do anything worthwhile with data models whatsoever, except make slightly prettier pictures of tables and column names... by hand

I know I'm a little late to the party in finding out about this - I only switched over to Windows 8 and Office 2013 on my primary laptop a few weeks ago, but I'm actually not sure what I'm going to do about it. ERwin and Embarcadero aren't cheap and there's a bit of a learning curve with both of them. Going from Visio to CA or Embarcadero for data modeling is about the same as going from MSPaint to Photoshop - great for seasoned pros, but too specialized and too expensive for a lot of us. With Visio, I could always count on clients and colleagues being able to collaborate and review files with me. If they have to cough up a couple grand per license plus yearly maintenance fees, it greatly diminishes the utility of the tool for me.

You screwed up, Microsoft. Please, come to your senses and bring this feature back. People use it. A lot.

P.S. If you use Visio 2010 and miss the forward engineer feature, I recommend you check this out: http://forwardengineer.codeplex.com

Sound Data Advice From a Fortune Cookie

2013-03-20T22:15:00.000-07:00

Put the data you have uncovered to beneficial use.

Pretty good advice, if you ask me...

Chrome Console Error

2013-03-19T23:16:00.000-07:00

I came across an error while testing a website tonight using the Google Chrome JavaScript Console. (Using Chrome? Hit Control - Shift - I for Windows or Command - Option - I for Mac to see the Chrome Developer Tools!)

Every time I loaded the page in Chrome, I got this error. Not in other browsers, only in Chrome. Here's the error:

Port error: Could not establish connection. Receiving end does not exist.

I thought there was something wrong with the site, but after scratching my head and tinkering around a bit I couldn't track down the cause. Luckily, someone else beat me to it and thanks to a quick web search (see the helpful links below), I disabled the Chrome to Phone extension and Voilà! the error message disappeared. I guess now it's back to debugging my own error messages...

https://groups.google.com/forum/?fromgroups=#!topic/easyxdm/gVigio5TYiw

https://code.google.com/p/chrometophone/issues/detail?id=316

https://code.google.com/p/chrometophone/issues/detail?id=451

T-SQL Tips: PARSE() and TRY_PARSE()

2013-02-17T17:32:00.000-08:00

Database Application Localization
Certain types of data might be formatted differently in different parts of the world, and our database applications need to be smart enough to handle these localized data formats. Allowing application users to interact with familiar data formats while maintaining consistency in the database can represent a challenge for data architects, and there are many approaches that can be employed to achieve the desired results. Two new options in SQL Server 2012 are PARSE (Transact-SQL) and TRY_PARSE (Transact-SQL).

PARSE()
PARSE() takes a string value as an input and converts that string (e.g. 'Sunday, 17 February 2013') to the desired data type (e.g. DateTime2), using the provided culture (e.g. 'en-US').

Syntax:

PARSE ( string_value AS data_type [ USING culture ] )

Example:

SELECT PARSE('Sunday, 17 February 2013' AS datetime2 USING 'en-US') AS MyParsedDate;

Result:

MyParsedDate
---------------
2013-02-17 00:00:00.0000000

TRY_PARSE()
TRY_PARSE() has similar syntax to PARSE() and will return the same results, or null if the cast fails.

Syntax:

TRY_PARSE ( string_value AS data_type [ USING culture ] )

Example 1:

SELECT PARSE('Sunday, 17 February 2013' AS datetime2 USING 'en-US') AS MyParsedDate;

Result:

MyParsedDate
---------------
2013-02-17 00:00:00.0000000

Example 2:

SELECT PARSE('not a date' AS datetime2 USING 'en-US') AS MyParsedDate;

Result:

MyParsedDate
---------------
NULL

Summary
PARSE and TRY_PARSE are two powerful new SQL Server 2012 Conversion Functions that can help you incorporate data sets from your business associates, customers, partners, and database applications in different parts of the world. You can even nest TRY_PARSE inside of a COALESCE() function to try parsing using multiple culture/language sets. Data warehouse ETL developers can use these functions to standardize dates and other data types stored in different localized data formats as they import this data into the warehouse. Application developers can keep a consistent data model structure even if the front-end application displays localized data formats to the user.

Are you using PARSE() or TRY_PARSE() to solve a tough localization problem? Leave a comment!

Bill Gates on the Importance of Measurement

2013-01-26T18:10:00.000-08:00

This is a great read from this weekend's Wall Street Journal. Bill Gates on the Importance of Measurement.

We in the data analytics community understand that in order to improve something - whether it's customer or employee satisfaction, product quality, or some other process - you start by measuring it. We spend every day coming up with clever ways to collect and analyze data to improve the way we do things.

We don't often consider that the same principles and techniques we use to analyze customer purchase records can have much broader applications in improving public health and education. What Mr. Gates draws attention to is that sound measurement is the key to fundamentally understanding a problem, setting clearly defined goals, and measuring progress toward achieving those goals.

I admire Mr. Gates' insightful exploration of this topic and very much look forward to Mr. Gates' annual letter, set to be released January 30th. More info at www.thegatesnotes.com.

Mobile App UI Mockups Made Easy

2013-01-22T10:30:00.001-08:00

I'm a big believer in prototyping as a vital part of a successful application development project. I've been working on a custom iOS app for a client and have been having fun playing with a few user interface mockup tools for mobile apps that I thought I'd share.

Fluid UI

Fluid UI is a pretty amazing browser-based tool for visually wireframing iOS, Android, and Windows 8 mobile applications. You can quickly and easily create realistic mockups, complete with working navigation, save it, share it, and test it. I can see great possibilities with a tool like this. UI/UX designers can share ideas and get feedback from users quickly and without any code being written. Developers can refer to the mockups while they're coding to make sure they build in all the necessary functionality. Testers can refer to the mockups for expected test case results.

With a free account, you get 1 project, up to 10 screens, 10MB of uploads, unlimited sharing and mobile testing. Not too bad for free, and may meet your needs for a smaller application. Not to mention it's really fun to play with their web app! You can try it for free on their site. www.fluidui.com

Interface 2 Advanced Mockup for iOS

Interface 2 Advanced Mockup for iOS is itself a mobile app available in the iTunes store for either iPhone or iPad. This app has some of the mockup and animation features as Fluid UI, with the added benefit of being able to export to XCode. This means you can turn your mockup into working code for many of the widgets available in the iOS SDK. I can't speak to the quality of the code, but my guess is that it'll be a good starting point that you'll then want to incorporate based on your own coding preferences and team standards. I wouldn't expect it to be able to produce App Store ready code, but it might give you a jump start on a functional prototype. Overall seems like a very promising tool.

Prototyping on Paper (POP)

A colleague pointed me to this one last week. POP, another iOS app, encourages you to draw screen mockups on paper (presumably a whiteboard would work just as well), snap photos of each of your screen mockups, then start piecing the screens together by adding links and navigation between your drawings. For instance, if your drawing has a Back button at the top left, you can highlight that area and create a link back to the app home screen.

I like this app because it lets you take the ideas straight from paper (or conference room whiteboard) to a sketchy mockup that includes some basic navigation. I often like to keep early design mockups fairly sketchy so users focus on functionality and don't get hung up on visual details like color and font that can come later in the design.

These are just two that caught my eye. Leave a comment if you have tried these or have others to recommend.

Happy prototyping!

Microsoft BI Whitepapers

2012-06-21T13:38:00.002-07:00

Check out these two Microsoft BI whitepapers, both written by my friends and colleagues here at Hitachi Consulting.

How to Choose the Right Reporting and Analysis Tools to Suit Your Style
Summary: This white paper provides information that will help business and IT decision makers select the right Microsoft reporting and analysis front-end tools based on their business users’ intent: self-service reporting, data mashups, professional reporting, dashboarding, and scorecarding.

Choosing a Tabular or Multidimensional Modeling Experience in SQL Server 2012 Analysis Services
Summary: This white paper provides practical guidance to help BI professionals and decision makers decide whether SQL Server 2012 Analysis Services tabular or multidimensional modeling provides the best fit for your next BI solution.

SQL Server Tip: View Schema Binding

2012-01-27T14:17:00.000-08:00

As a database and ETL architect, I often find myself in situations where I am altering tables in SQL Server that have views that reference them. Changing the name of a column or renaming the table often breaks the view definition, resulting in an error when a database user queries the view.

Using the WITH SCHEMABINDING option when you create a view binds the view to the schema of the underlying table or tables. The result is that you will get an error message if you attempt to alter the table without first updating or dropping the view definition. Setting this option on all of the views in your database is a great way to prevent developers from altering a table schema without also updating the views that reference that table or tables.

Note: This option won't prevent you from making other alterations to your tables that might alter the data returned by a view.For example, if you change the grain of the underlying table, the WITH SCHEMABINDING option won't stop you from returning duplicate results. However, for many table alterations this little trick will save you a lot of headaches and disgruntled database users down the road.

Read more about the WITH SCHEMABINDING option on SQL Server Books Online here: http://msdn.microsoft.com/en-us/library/ms187956.aspx

Hope this helps,

Harlan

Installing Microsoft JDBC Driver for SQL Server

2012-01-04T23:29:00.000-08:00

I have successfully configured the Microsoft JDBC Driver for SQL Server 3.0 and thought I would post my deployment steps as well as my results from some troubleshooting, in case they are helpful.

Steps to configure JDBC:
System requirements: Java Runtime Environment (JRE)

Download and install the Microsoft JDBC for SQL Server 3.0
When prompted, unzip to a directory *with no spaces*
To use integrated security (Windows authentication), copy the sqljdbc_auth.dll file to the C:\Windows\system32 directory. This file can be found under JDBC_install_path\sqljdbc_version\language\auth. Use the appropriate x86, x64, or IA64 sqljdbc_auth.dll file based on the JDBC help documentation.
Set the CLASSPATH environment variable to include the appropriate version of the JDBC driver .jar file, with class paths separated by semicolons. Only one of the .jar files should be included in CLASSPATH, either sqljdbc.jar or sqljdbc4.jar. Be sure to include any current CLASSPATH values in your set command. To see the current value, just run set with no arguments.

set CLASSPATH=.;JDBC_install_path\sqljdbc_version\language\sqljdbc4.jar

You should now be able to import the java.sql package and connect to a SQL Server database!

Sample Java:

import java.sql.*;

public class ConnectJDBC {

public static void main(String[] args) {

// Create a variable for the connection string.
String connectionUrl = "jdbc:sqlserver://localhost\\MyInstance;integratedSecurity=true;database=AdventureWorks";

//print message
System.out.println("Connection URL = " + connectionUrl);

// Declare the JDBC objects.
Connection con = null;

try {
// Establish the connection.
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(connectionUrl);

//print message
System.out.println("Successfully connected to the database!");
}

// Handle any errors that may have occurred.
catch (Exception e) {
e.printStackTrace();
}
}
}

Compile and run this code

javac ConnectJDBC.java

java ConnectJDBC

Congratulations! You have now successfully connected to a database. Next step: Executing a query and doing something with the results!

Errors & Troubleshooting

Error 1:

java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver

This one was resolved as soon as I moved the JDBC install path to a path with no spaces. This one had me scratching my head for a while.

Error 2:

com.microsoft.sqlserver.jdbc.SQLServerException: The server MyServer is not configured to listen with TCP/IP.

For this one, open SQL Server Configuration Manager and enable TCP/IP under SQL Server Network Configuration. You must restart the SQL Server service for the appropriate instance for the configuration change to take effect.

Error 3:

SEVERE: Java Runtime Environment (JRE) version 1.7 is not supported by this driver. Use the sqljdbc4.jar class library, which provides support for JDBC 4.0.

Make sure the CLASSPATH environment variable contains a reference to the appropriate .jar file, either sqljdbc.jar or sqljdbc4.jar. In my case, I am using JRE version 1.7, so I need to use sqljdbc4.jar.

Error 4:

Error: Could not find or load main class

This is a common Java runtime error when the JVM can't find the main class. In my case, I forgot to include the current directory (.) in my CLASSPATH environment variable. Adding .; to the beginning of my CLASSPATH value fixed the problem.

set CLASSPATH=.;JDBC_install_path\sqljdbc_version\language\sqljdbc4.jar

SSAS Maestro Program Site

2011-10-31T16:29:00.000-07:00

The SSAS Maestro Program now has an official page on the Microsoft Learning site where you can find details on the program, some of the training objectives (which, by the way, make a great list for Analysis Services study concepts), and a Meet the Maestros page where you'll find info on all the current SSAS Maestro Program graduates.

Check it out: http://www.microsoft.com/learning/en/us/certification/ssas-maestros.aspx

Big thanks to Robert Davis and Microsoft Learning for putting the site together.

BISM Demystified

2011-10-21T17:47:00.000-07:00

Just caught a great blog post from Cathy Dumas on Microsoft's Analysis Services team that helps alleviate some of the confusion around the BISM, MDX, DAX, etc. I know I personally left PASS last week a bit confused about what exactly the BISM was and how MDX & DAX play into that, especially in light of Power View (formerly Project Crescent) currently only being able to query Tabular models (multidimensional/tabular - it's all just one BISM, right?!).

PASS Summit day 1 Keynote - More Announcements

2011-10-12T09:50:00.001-07:00

SQL Server code-name "Denali" will now be called SQL Server 2012 and will be released in H1 of calendar year 2012.
"Project Crescent" will now be named PowerView and will be released as part of SQL Server 2012.
"Project Juneau" will be named SQL Server Data Tools and will be part of the 2012 release.
SQL Server Azure Reporting Services will be released some time in 2012.
Analysis Services for Azure will be coming soon. (Yay! Cloud cubes!)
Project code-name "Data Explorer" is a new cloud service that will allow you categorize your data sets based on noun analysis, will suggest recommended Azure Marketplace data sets, and will allow you to join or "Mashup" multiple data sets from your own data or data from the cloud, then download the results to Excel, PowerPivot, etc. The product is expected to be rolled out sometime in the coming year.
Oh, and one more thing. PowerView (aka Crescent) now has export to PowerPoint. And it will be available on mobile devices - Windows Phone, iPad, Android.

PASS Summit Day 1 Keynote - SQL Server & Apache Haddop Play Nice

2011-10-12T09:13:00.000-07:00

OK, didn't see this coming...
Microsoft's Ted Kummert just announced that the company will be making a foray into the world of Hadoop, not releasing their own MapReduce platform as I predicted yesterday.

What will this look like? Microsoft has already announced connectors for SQL Server & SQL Server Parallel Data Warehouse to get data into and out of Hadoop. In addition, the announcement at PASS today includes the following:

1. Microsoft will begin actively contributing to the Apache Hadoop project, building a Hadoop for Windows distribution to ensure that the platform runs seamlessly on clusters of both Windows and Windows Azure.
2. Microsoft will soon be releasing an ODBC driver and Excel add-in to connect to Apache Hive.
3. Microsoft is partnering with hortonworks to help accelerate the Windows/SQL Server/Hadoop/Hive integration.

Pretty exciting stuff. Not to mention my buddy Denny Lee (Principal Program Manager on the SQLCAT team) got to make the announcement. He told us a while back that he was working on Big Data but couldn't tell us much... I guess this is what he meant!

SSAS 2008 R2 Performance Guide

2011-10-11T22:15:00.000-07:00

SQLCAT has published the latest version of the Analysis Services Performance Guide for SQL Server 2008 R2. If you are an SSAS developer or architect, do yourself a favor and spend some time studying this paper. There are all kinds of goodies in there. Keep a copy on a thumb drive and take it with you wherever you go - you'll be glad you did.

Microsoft Big Data Announcement - A Prediction

2011-10-11T16:25:00.000-07:00

Rumor has it that Microsoft will be making a major Big Data announcement at this year's PASS Summit - most likely in Ted Kummert's keynote Wednesday morning. My prediction? This is pure speculation, but my guess is that Microsoft will be releasing Dryad, DyadLINQ, and/or Cosmos into the wild - either open-sourcing them or (more likely) announcing them as a new product line under the Business Platform Division. Guess I'll find out for sure tomorrow!

Management Studio Bug in Denali CTP3 - All SSAS hierarchy levels are displayed

2011-10-05T10:27:00.000-07:00

While browsing the Adventure Works dample cube in Denali CTP3 SQL Server Management Studio, I noticed that when I add the Customer Geography hierarchy to my rows axis, all levels of the hierarchy are displayed.

When I add the same hierarchy to the rows axis in an Excel PivotTable, as expected, only the top level (in this case, Country) is displayed. I imagine this is a bug in the CTP3 build, but I haven't had the chance to search around on Connect for it.

*Update* I just logged this as a bug on Microsoft Connect. Please click below to vote for it if you can reproduce the same results (or if you trust me implicitly!)

https://connect.microsoft.com/SQLServer/feedback/details/693225/denali-ctp3-ssms-displays-all-hierarchy-levels-when-browsing-ssas-mulidimensional-cube

SQL Server "Denali" CTP3 Adventure Works Samples

2011-10-03T19:53:00.000-07:00

The latest Adventure Works sample databases are now available on Codeplex for SQL Server code name "Denali" Community Technology Preview 3 (CTP3). The samples include the Adventure Works OLTP and Data Warehouse sample databases you might be familiar with from previous SQL Server product releases, as well as sample projects for SSAS Multidimensional Model (UDM) and SSAS Tabular Model (BISM), which includes Adventure Works DW PowerPivot workbook, Adventure Works DW tabular model, and related Project Crescent reports. Also be sure to check out Getting Started with Project "Crescent" and PowerPivot for Excel. http://msftdbprodsamples.codeplex.com/releases/view/55330 I have CTP3 installed on a test machine and will be installing the sample databases shortly. Looking forward to sharing what I learn!

PASS Summit 2011 - See you there!

2011-10-03T19:42:00.000-07:00

I will be attending PASS Summit for my first time next week. Very excited for this year's lineup of speakers and looking forward to getting the inside scoop on some of the new Denali features. I'm just glad I pre-ordered the conference DVD so I don't have to stress out about choosing from all the great content. Stay tuned for updates and highlights all next week.

SSAS Maestros

2011-09-19T15:27:00.000-07:00

Hooray! I'm very excited to announce that I am officially a Microsoft-certified SQL Server Analysis Services (SSAS) Maestro! SSAS Maestros is a new advanced Analysis Services course Microsoft has been developing this year, and I'm very proud to say that I'm one of the first "graduates" of the program. Check out the announcement over at SQL CAT to see a listing of my ten comrades. It's very humbling to see my name on a list with these world-renowned BI experts and authors I've been admiring for years. Congratulations to all of my fellow Maestros!

Microsoft BI Tool Choice Whitepaper

2011-01-21T14:47:00.000-08:00

A few colleagues of mine at Hitachi Consulting, in collaboration with Microsoft, just published a Microsoft Business Intelligence whitepaper, "How to Choose the Right Business Intelligence Technology to Suit Your Style". The paper outlines several BI reporting styles - Self-Service Analysis, Business Reporting, Parameterized & Operational Reporting, Performance Monitoring, and Scorecarding - and how Microsoft BI tools can be used to meet the unique needs of each style. The paper specifically evaluates Microsoft Excel 2010, PowerPivot for Excel 2010, Report Builder 3.0, PerformancePoint Services, and SQL Server 2008 R2 Reporting Services and explains how each one can be applied in different scenarios.

I was a reviewer and helped out with some of the content, so if there are mistakes, it's probably my fault!

Harlan

Large Scale Data Warehousing and Analytics

2010-12-15T22:12:00.000-08:00

A sampling of things I've been paying attention to lately:

MapReduce and Google File Store
Google Cluster Computing and MapReduce Lectures
Lecture 1
Lecture 2
Lecture 3
Lecture 4
Lecture 5

Google Research Publications
http://labs.google.com/papers/mapreduce.html
http://labs.google.com/papers/gfs.html

Microsoft Cosmos and Dryad
What is Cosmos?
http://www.goland.org/whatiscosmos/
Microsoft Research - Project Dryad
http://research.microsoft.com/en-us/projects/dryad/
Dryad: A general-purpose distributed execution platform
http://www.youtube.com/watch?v=WPhE5JCP2Ak

Hadoop and Cloudera
What is Hadoop?
Robert Scoble interviews Mike Olson, CEO of Cloudera

Introduction to Apache Pig
http://www.cloudera.com/videos/introduction_to_pig

Hadoop and Hive at Facebook
http://www.cloudera.com/videos/hw09_rethinking_the_data_warehouse_with_hadoop_and_hive

...and completely unrelated:
Teradata University Network (TUN)
"Teradata University Network is a web-based portal for faculty and students in data warehousing, business intelligence/decision support, and database that is provided at no cost to the university. This content also can support Introduction to IT courses at the undergraduate and graduate levels."
http://www.teradatauniversitynetwork.com/tun/

Decoding HTML-Encoded Text in SSIS

2010-07-17T11:53:00.001-07:00

I have a SQL Server data source that contains a column that has been HTML-encoded in an upstream system. I need to join this encoded string to another table that contains the original Unicode text entered by the user, which is not HTML-encoded.

Original Unicode text: Découvrez
HTML-encoded text: D& #233;couvrez

(Note: I added a space between & and # in the HTML-encoded text so your browser won't automatically decode it.)

The .Net HttpUtility class allows you to decode HTML-encoded text with the HttpUtility.HtmlDecode method. (The HttpUtility.HtmlEncode method does the opposite - encodes Unicode text as HTML-encoded text.)

To accomplish this task, I created a SQL Server Integration Services (SSIS) package with a custom Script Component transformation in a Data Flow Task. Here are the steps I followed, which may help you do the same:

1. Add a Data Flow task to your package and add a new Data Flow Source that points to the source table or file that contains the HTML-encoded text. (I am using the OLE DB connection manager to connect to my SQL Server source. My source table name is Dim_Ad)

2. Click on the Columns tab and set the column with the HTML-encoded text as both an External and Output column. (My HTML-encoded column is AdName). Also select any other columns you would like to pass through to your destination.

Now comes the important part - you will add a custom Script Component transformation to decode the HTML-encoded text.

3. Add a Script Component transformation to your data flow task and drag the green output pointer from your data source to the script component.

4. Double-click the Script Component for editing. The first time you open the script component, you will have the option of selecting the Script Language. I chose Microsoft Visual C# 2008. If you are more comfortable with VB than C#, you can choose Microsoft Visual Basic 2008 instead.*

5. Click on the Input Columns tab and select your HTML-encoded column as an input column. (In my case, AdName). Also include any other columns you would like to pass through to the destination.

6. Click on the Inputs and Outputs tab. Expand the Output 0 node, click on Output Columns, and then click Add Column. Give your column a friendly name (I chose DecodedText) and set the Data Type to Unicode string (DT_WSTR). Set the Length of your column to match the source column length (in my case Dim_Ad.AdName length is 100 so I chose 100 for my output column length).

7. Go back to the Script tab and click on Edit Script... You should see the script editor window, which will look something like this:

8. You will need to add a reference to the System.Web dll to your project before you can reference the HttpUtility class. Click on Project > Add Reference. In the .Net tab, scroll down to select System.Web and click OK.

Note: For some reason, any time you close and reopen the script, it loses this System.Web reference. If, after closing and reopening a script, you get the following error message: The name 'HttpUtility' does not exist in the current context., simply repeat this step to add the System.Web dll reference back to your project.

9. Add the following code to the script, below using System;

using System.Web;
using System.IO;

10. Add the following code below the "Add your code here" comment (If necessary, replace "DecodedText" with your output column name and replace "AdName" with your HTML-encoded input column name)

Row.DecodedText = HttpUtility.HtmlDecode(Row.AdName);

11. Save and close the script. This will return you to the Script Transformation Editor. Click OK.

12. Now all you need to do is add a destination adapter to the data flow task, drag the green output pointer from the custom script component to the destination, open the destination adapter, and map the DecodedText output column from the script component transformation to the desired input column of your destination

Note: The destination column data type must support Unicode text. I am using a SQL Server OLE DB connection, so my destination column is an NVarchar(100).

Now Execute your package and check the destination to test that any HTML-encoded text actually got decoded to Unicode text.

HTML-encoded source text: D& #233;couvrez (Space added to avoid browser decoding)

Decoded destination Unicode text: Découvrez

Voilà, all done!

*If you decide to use Visual Basic as your scripting language instead of C#, note the following minor differences in the code syntax:

C#:
using System.Web;
using System.IO;

VB:
Imports System.Web
Imports System.IO

C#:
Row.DecodedText = HttpUtility.HtmlDecode(Row.AdName);

VB:
Row.DecodedText = HttpUtility.HtmlDecode(Row.AdName)

References:
HttpUtility.HtmlDecode Method (MSDN)
Character Encodings in HTTP (Wikipedia)