SSIS vs Bulk Load

Based on 2000 experience with DTS, a lot of people prefer set operations or bulk load over transformations. One big benefit of SSIS transformations is that you can execute several operations in one pass--which you can't do with set operations. And if you use the SQL Server Destination adapter (and native SQL connection manager), you get speeds much faster than OLEDB--in fact you can get virtually identical speed to bulk load. One thing to watch out for is that when using the SQL Server Destination, the package must run on same machine as destination database.

Reed

When is Ragged Right right for Fixed-length files?

It's natural to expect that if you have a Fixed Length file, that you should choose the SSIS Fixed Length file type. But Fixed Length is really for binary files, where there are no CRLF characters at the end of the lines. If you have a fixed length text file, and want to eliminate the delimiter, use Ragged Right flat file, and for the final column, specify CRLF as the delimiter.

- Reed

Diagrams are Back!

One of the most (ahem) active threads in the SQL 2005 beta newsgroups was discussion around the loss of the trusty database diagramming tool, formerly found in SQL Enterprise Manager. I am happy to report that as of the April Community Technical Preview build, diagrams are back in the SQL Management Studio. Even though Microsoft has stated that the primary emphasis for diagramming will be with both Whidbey and with Visio Enterprise Architect, it’s nice to have this often used functionality returned to the tool-set that many of us use day to day to create or understand Star or Snowflake Schemas. While there are many tools that offer more extensive diagramming feature sets, the integration and ubiquity of the Database Diagramming Tool makes it invaluable. Personally, I use it to visually understand the layout of Star and Snowflake schemas that I am becoming familiar with. I also use it to create prototype relational databases for proofs of concepts.

Feature-wise, it seems to be much the same as the previous version. I’ve noticed two differences worth mentioning (so far):

1)  The procedure for adding related tables is slightly different. In the SQL 2000 version this was accomplished through the add-table dialogue; now it is accomplished by selecting a table in the diagram and clicking the “Add Related Tables toolbar icon”

2)  The ability to open tables is no longer part of the diagramming tools. That functionality is available as part of SMS, just not directly from within the Diagramming Tool as in the SQL 2000 version.

Kudos to the SQL Server Development Team for incorporating our feedback around the Diagramming Tool.

- Tom Huguelet

 

DTS Service Benefit

I've sometimes wondered what the real benefits of the DTS Service aulkre--aside from managing the server storage and monitoring jobs. I just found out that the DTS Service caches the enumeration of components. Turning on the service keeps the enumerations from having to happen each time you run the package. For some people this has made orders of magnitude difference in execution speed.

- Reed

Link to Unknown Member Blog

Christian Wade from Conchango has an excellent blog entry on issues relative to the Unknown Member. He makes a compelling case for handling unknown members in the ETL whenever possible, and describes an excellent way of keeping clear which fact records need to be reprocessed.

 

http://blogs.conchango.com/christianwade/archive/2005/04/01/1228.aspx

 

- Reed

 

Hybrid MOLAP/ROLAP dimensions

Some dimensions—if you push them to their physical extreme, something we never did in 2000—get very, very large at the bottom. As a simple example, you could have 50M customers. In 32-bit AS2K you would never dream of making that a dimension—or at least not a MOLAP dimension—and you wouldn’t want to make it into a ROLAP dimension either, because that would eliminate any MOLAP data storage anywhere in the cube. In 2005, I can easily imagine a situation where I would want the most aggregated attributes (Country, State, City) of a user hierarchy to be MOLAP—so that they can support good MOLAP aggregations—and the bottom attributes (such as Customer, or even Order ID) as ROLAP, so they don’t require MOLAP processing.

The developers imagined it as well, but apparently couldn’t get it to work in the Yukon time frame. So the whole dimension has to be either MOLAP or ROLAP. In the first place, my experience with large ROLAP dimensions used with a MOLAP cube is very limited (which is to say, not yet existent), so it may not be all that much of an issue.

If it turns out that a truly “hybrid” dimension is desirable, there are a couple of ways I can think of for creating the hybrid. One is to split the dimension in two, with the higher-level (MOLAP) one a reference dimension by way of the lower-level (ROLAP) one. Another is to create a many-to-many dimension to connect the two. It will be interesting to experiment with different alternatives in specific scenarios.

-- Reed

Drill Through and Fact Dimensions

Fact Dimensions did not work in Beta 2. At the time I assumed that they were just “degenerate” dimensions, so that you could create dimensions directly from low-cardinality values stored directly in the fact table—such as whether a row was part of a promotion or something like that. I also assumed (without ever really looking at it closely) that Drill Through was basically the same as in AS 2000—a SQL Query retrieval of the rows that contribute to a specific cell.

In the Dec CTP, they fixed fact dimensions—and it turns out that they are completely different from what I had thought. A fact dimension is essentially using the fact table itself as a dimension—with multiple possible attributes—and it is the key to getting Drill Through to work.

Suppose that you had a PO number as a field in the fact table, but didn’t want to include that in any dimension. In 2000, you could retrieve the PO directly from the fact table via Drill Through and a SQL Query. In 2005, all Drill-Through requests go to the UDM (which actually seems internally consistent with the concept of the UDM). But that means that any column from the fact table that you want returned in the Drill Through result has to be part of a dimension. Hence the Fact dimension. First add a primary key to the fact table. (You can, in principle, use a combination of columns to get a unique key, but I think it’s good practice—and I’ve had better success—just adding an AutoNumber primary key column.) You then create a dimension using the fact table primary key as the key, and add any other columns from the fact table (such as the PO number) as attributes. Then make the dimension ROLAP storage. Once you add the dimension to the cube (and probably hide it from the users), you can include any of its attributes in the Drill Through Action definition. It actually worked quite well for us. Good luck.

--Reed