Category Archives: SQL Server

Pragmatic Works, SQL News, SQL Server, SQL Server Tools

Pragmatic Works Release Doc xPress Server Edition

This month marks the official release of Pragmatic Works’ Doc xPress Server Edition! While everyone knows that Doc xPress gives you the capability to document your SQL Server databases, SSIS packages, SSAS cubes, and SSRS reports in way like no other tool. But with the release of the Server Edition, you can now host your documentation to a hosted web application making it now easier than ever to share documentation, lineage, and data dictionary information across your organization. Imagine being able to provide your technical users as well as your business users a one-stop-shop to all of your organization’s technical documentation without requiring anything to be installed on their desktop! Doc xPress Server Edition provides that capability!

Being able to view your Doc xPress-generated documentation online means that your technical team can quickly and easily assess the impact of changes in your BI environment by conducting a thorough lineage and impact analysis. What SSIS packages, SSRS reports, or SSAS cubes will be affected if I change a single column? Now you know!

How many times have you wondered what your environment looked like in the past? With Doc xPress, you can periodically snapshot your environment and compare snapshots over time so you can quickly and easily assess any changes that have occurred. And now you can view the documentation in your web browser without the need for any desktop configuration or installation.

If Doc xPress Server Edition sounds interesting and you’d like more information on the software, Pragmatic Works is presenting a free online webinar on the technology July 30th at 1:00 pm EST! Head over to PragmaticWorks.com (you’ll need to scroll down a screen or two) to get signed up and registered for the event.

Pragmatic Works, SQL Server

Creating A Data Dictionary Has Never Been Easier

A Data Dictionary is one of those things that almost all of my customers agree is important but many of my customers never get around to creating one. I think there are a few reasons why creating a descriptive and useful data dictionary is not very high on the priority list for many of my customers. One of those reasons is that creating a data dictionary can often be time intensive. Creating a data dictionary is often a very manual effort that requires some knowledge of the solution. Another reason creating a data dictionary often falls to the back burner is that picking a useful format can be challenging. Should a customer create a SharePoint list, a Word document, a OneNote document, or some other format? And then, of course, there’s the challenge of keeping the data dictionary documentation up to date so that its actually relevant and useful. All of these are very real challenges that many customers face when creating a data dictionary. This is where Doc xPress comes into play.

Many users often want to capture additional attributes as part of their solution documentation. Attributes such as descriptions, example data, SLA’s, business owner, and other attributes are very important nuggets of metadata that would be great to display in the documentation. The Doc xPress Data Dictionary feature allows us to very easily add meta data to our solution documentation that can then be included in any format allowed by Doc xPress. By using Doc xPress, we can add useful business definitions to any item in the documentation and provide useful annotation to our business users in a simple and efficient way.

One of the really nice things about Data Dictionary in Doc xPress is that we have the ability to create mandatory and optional metadata categories that can be scoped to the entire metabase or smaller subareas of a solution. For example, I might create a category called “Business Owner” that would contain the name and contact info of the business owners of the databases included in our documentation. We could also make this a required category which will allow us to analyze our solution in order to determine which objects are missing an entry for this category.

Once our Data Dictionary entries have been added, we can easily view the entries within our Doc xPress documentation.

To download the free trial version of Doc xPress, head over to Pragmatic Works site and take the Data Dictionary feature out for a spin.

Pragmatic Works, SQL Server

Browsing SQL Server Databases With DBA xPress

image

With the latest release of the Pragmatic Workbench, we are exposed to the Object Browser. The Object Browser is a very cool feature that is party of the DBA xPress package of tools designed with the database administrator in mind. This is the first time I’m exploring this tool, so I wanted to walk through the feature with you and give my take on it (even though I’m not a DBA, but nonetheless…).

When you first launch Object Browser, you must first point to the server where your database exists. So far so good.

image

After connecting, we’re immediately treated to a nifty tree view of our database and the objects within that database. In this case, of course, I’ve connected to the Adventure Works DW 2008 R2 database.

image

The browser is extremely responsive and very quick. Drilling down and exploring the database is slick and fast. Once you’ve connected to the database, you can browse it and search for a single object using the search function. All I had to do is type in my search term and the tree view filtered faster than Google.

image

One of the really cool features is that you can see the SQL script to create the object you’ve selected. whether it’s a table, a view, a column in a table, a procedure, or a role. Very handy! We can also hover over the objects and view the meta data for that object.

image

Another neat feature is the ability to take a snapshot of the database. Click the Take Snapshot button at the top of the Object Browser. A nice, clean XML file is the output and can be saved to the location of your choosing.

image

To learn more about DBA xPress and download the free trial, head here.

Community, Data Mining, PASS, SQL Server, SSAS, SSRS, Virtualization

SQL Rally 2012 Recap In Yo’ Face!

2 Comments

This past Thursday and Friday I attended my first PASS SQL Rally event in Dallas, Texas. The week was full of some pretty amazing sessions presented by some even more amazing speakers. If you were a DBA looking for performance tuning tips or other ways to perfect your profession, there were tons of great sessions for you. If you were a developer looking some great development tips and tricks to make your life easier, there were some really informative sessions given. And if you were a business intelligence developer, like myself, hoping to take your skills to the next level, there was definitely some amazing material to take advantage of.

I focused heavily on the business intelligence space and spent most of my time attending those sessions. I learned some great tips, increased my knowledge in several areas, and also networked a little with some really bright and great all-around people.

So What Sessions Did I Attend And What Did I Learn?

Performance Tuning SSAS Processing with John Welch

The first sessions I attended was Performance Tuning SSAS Processing given by John Welch (blog | twitter). There’s no doubt about, John knows his stuff. I learned a ton from his session. Here’s a few of the tips I picked up from John’s great sessions:

    • Instead of bringing entire tables into your DSV, use views. Views will allow you to tweak the SQL and use query hints to improve the performance of the Select statement.
    • When Analysis Services processes data, it does so one buffer at a time. If AS detects duplicate rows in a buffer, it will aggregate the data. Because AS aggregates duplicate records, you can save on space and increase performance if you order the data in your Select statement in your partitions.
    • Tune SSAS processing in 3 steps:
      • Dimension Processing
      • Partition ProcessData
      • Partition ProcessIndexes
    • Set the AttributeHierarchyOptimize property to false for attributes that are not used in queries often or if the attribute has a high cardinality and a near 1:1 relationship with the key attribute.

The Report Part Library with Jessica Moss

The next session I attended was The Report Part Library with Jessica Moss (blog | twitter). Jessica is one lady who is an amazing speaker and an SSRS guru without a doubt. Jessica’s wonderful sense of humor and bubbly personality made the session enjoyable and exciting. We covered how to create report parts, share those report parts across multiple reports, and then update those same report parts. Jessica also covered how to organize and manage a large Report Part Library. It was really great to finally meet Jessica in person.

Stop! Consolidate and Listen! with Jorge Segarra

After lunch, I attend Jorge Segarra’s (blog | twitter) session called, “Stop! Consolidate and Listen!”. Jorge covered some of the different methods of consolidation and dug into virtualization. Between Jorge’s sad rap skills and Patrick Leblanc’s even weaker beat boxing, I managed to pick up a few pointers.

Some of the Pro’s to virtualization:

    • Great isolation!
    • Flexibility
      • Snapshotting virtual machines (so if something goes horribly wrong, you can just roll back to the last snapshot)
      • Creating and dropping virtual machines is easy as pie
      • VM’s can be migrated across servers without having to be shut down!

Some of the Gotcha’s:

    • Behind the scenes resources could actually be shared, so make sure you understand the systems that your VM’s are running on.

Also, check out the free MAP toolkit from Microsoft. Jorge gave a great intro into the tool and how to use it to determine which machines are prime candidates for consolidation and virtualization.

Data Mining with Devin Knight & Adam Jorgensen

The last session I attended was Data Mining with Devin Knight (blog | twitter) and Adam Jorgensen (blog | twitter). Being far less than a master of data mining, I really looked forward to this session.

Data mining does three things:

  • Explores the data
  • Identifies patterns in the data
  • Performs predictions based on those identified patterns

There are several algorithms available for us to use:

  • Decision tree
    • Allows us to see how someone could come to a decision?
  • Clustering
    • Measures the distances between the cluster of points on a graph and an outlier
    • This method is often used for fraud detection and data validation
  • Time Series
    • Predict 3 units (days, months, years, etc) into the future
    • Based on historical values
  • Sequence Clustering
    • Used to determine somebodies next step
    • Where will a person click next on a web site?
  • Association Rules
    • Market basket analysis
    • Amazon uses this: People who bought this also bought this.
  • Naïve Bayes
    • A data mining algorithm that is literally hundreds of years old
    • A classification algorithm that could be used to determine the differences between customer who buy a product and customers who do not buy a product.
  • Neural Networks
    • Typically used with FICO scores
    • Takes multiple factors into account but can’t really point to one or two things to tell you how it came to a decision

Zero To Cube: Fast Track To Analytics with Adam Jorgensen & Dustin Ryan

I also had the wonderful pleasure of presenting on SSAS with Adam Jorgensen (blog | twitter). Our session was titled, “Zero To Cube: Fast Track To Analytics”. We had volunteers (some were volunteered by others) drive through creating a cube from the ground up. Within an hour and 15 minutes, we created a connection to our data source, created the data source view, made some enhancements to the data source view, created our cube, built a couple hierarchies and fleshed out the dimensions, and even built a report in Excel with a pivot table and a chart. We had a great, very participative audience and we had a blast! Thank you so much to all those that came to our session!

All in all, this year’s SQL Rally was an amazing event. I learned a lot of new things, made some great friends, and had a great time. If you weren’t able to make the event, you missed out big time. Definitely put next years SQL Rally on your calendar and make attending that event a priority. You won’t be sorry.

SQL Server, SSAS, TSQL

Create Date Dimension Table Script

7 Comments

On a recent project I created a Date dimension script that I thought, I’d share (mostly so I would have access to it from anywhere). I based this script on one written by Mike Davis, but I included a few changes such as a first and last day of month flag, open flag, relative time period attributes, and a few others. Enjoy! Continue reading Create Date Dimension Table Script →

Pragmatic Works, SQL Server, SSAS, SSIS, SSRS

BI Documenter 4.2 Released

I’ve been meaning to blog this for a while but since I’ve been on the road for the past month I’ve been pretty busy. This past January, BI Documenter version 4.2 was released by Pragmatic Works. With it comes some pretty cool upgrades and some new functionality.

One of the biggest improvements was the upgrade to the SQL Server Instance Snapshot functionality. It is now possible to include in your documentation Backup Devices, SQL Agent Jobs, and Server Configurations. Personally, I’ve been waiting for the ability to document SQL Agent Jobs so I’m very excited about this release. There’s also many other improvements to the SQL Server Instance Snapshot functionality, so head over to PragmaticWorks.com to check out the other improvements.

Theres also a couple new enhancements that are really nice. Now you have the option to output your documentation in MS Help 2 format. Also, BI Documenter can now document Analysis Services Mining Structures.

With the ability to document SQL Servers, SSIS packages, SSRS reports, and SSAS cubes, its certainly the most encompassing SQL documentation tool out there. If you’re searching for a one stop documentation tool for your SQL Server environment, definitely check out BI Documenter.