Technical Debt

Technical debt is a metaphor that equates software development to monetary debt.  In my opinion it is one of the most crucial concepts to be aware of when planning projects or road-maps.

Imagine that you have a project that has two potential options; one is quick and easy but will require modification in the future, the other has a better design but will take more time to implement.

In development, releasing code with a ‘quick-and-dirty’ approach is like incurring debt – it comes with the obligation of interest, which, for technical debt, comes in the form of extra work in the future.

Just like monetary debt, technical debt is interest-bearing and compounds.  You always have the option to pay down the debt (long-term thinking) or to take out additional credit (short-term) but your project can become insolvent where the only option is to write-off the debt (re-write from scratch).

To summarise, it is a debt that you incur every time you avoid doing the right thing like removing duplication or redundancy.  It will add an overhead to everything you do from thereon in, whether that is troubleshooting, maintenance, upgrading or making changes.

[Some parts taken from MartinFowler.com and Techopedia]

Default SQL Server Settings

Introduction

You can change server-wide default settings by using facets and properties at server level or by modifying the model system database (which acts as a template for new databases created).

I don’t change much but here are the settings I do change to make life easier for myself.

Note: I fully understand that everything needs to be considered on a case by case basis – I’m just presenting these as possible ideas.


#1 – Backup locations – (In SSMS object explorer, right-click the server >> Facets >> Backup Directory)

When configuring my backup procedures, I like to setup two folders on each database server such as the below;

E:\SQL_Backups_Nightly\ | Used with nightly backup maintenance job.
E:\SQL_Backups_Adhoc\   | Used for manual backups/restores.

I then setup the latter as my default backup directory in the server facets pane.

This means that when I do manual backups/restores the dialog box will take me straight to this folder.  I find that when restoring a database under pressure, trying to navigate to the default paths (which usually look like below) just adds unnecessary confusion.

\\Servername\e$\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup

#2 – File Locations – (In SSMS object explorer, right-click the server >> Properties)

This is similar to the backup locations above.  In Database Settings under Database Default Location I use the following values.

E:\SQL_Server_Data\
F:\SQL_Server_Logs\

Again, they are just easier to find and if you ever have to write MOVE statements and such like, this will make it a lot easier.

For the record, it’s a good idea to have your log files on a separate physical disk to your data files, please see this article for a full explanation.

Continue reading

Cubes (& PowerPivot) vs Traditional Excel Pivot tables

This is a question I’ve been asked a few times before; “apart from handling larger data-sets, what do you get with cubes that you don’t get with pivot tables?”

This isn’t an exhaustive list but it covers what I think are the most important functionality differences.

#1 – Cubes are organised into dimensions / attributes.

With traditional Excel pivot tables, you are building on top of a flat data-set and as a result you will get your dimensions / attributes / measures in one big list.

You can take a flat data-set straight into PowerPivot as well (and there are definitely use cases for doing this) but if you build a dimensional model (as you are forced to do in SSAS MD) you will logically organise your reporting attributes by the correct dimension and make a much more intuitive model for the user.

dim-vs-flat

#2 – Hierarchies.

Traditional pivot tables will show you the implicit relationships in your data but you will have to create a column for each attribute you wish to show.  You can then tidy this up by adding manual grouping but the whole process is very clunky.

With PowerPivot & Cubes, you explicitly define hierarchies and when exploring the data you can drag this single field in and have instant drill-up and drill-down, plus on large dimensions performance is going to be significantly better.  Displaying natural relationships this way is key in making the data easy to understand at a glance.

ssas_pvt_hier_v2

#3 – MDX / DAX.

In cubes and PowerPivot you can write your own MDX & DAX functions to build more complex calculations.  There really isn’t any direct comparison for MDX/DAX for traditional pivot tables although when creating a measure in a traditional pivot table there are some options available (as shown below) but nothing with the advanced analytics capability of MDX/ DAX.

pivot_measure_opts

Users also sometimes build calculations outside of the traditional excel pivot but this then means the pivot table has to remain static otherwise the calculations are overwritten.

#4 – Client Tools.

Traditional Pivot Tables are themselves the end-product.  PowerPivot provides an upgrade path to SSAS tabular and cubes have a whole host of cutting edge reporting tools that can use them as a source.  Personally I don’t like excel pivot tables for browsing cubes and have tried out some different reporting tools that can connect to SSAS.  My favourite experience was delivered by Pyramid Analytic’s BI Office solution, an example of which can be shown below.

Power View inside Excel is not bad but in my opinion it’s not the easiest thing to use and I can’t see it gaining wide adoption.

bioxl

#5 – Other Considerations

There are plenty of other important considerations such as

security,
pre-aggregation,
partitioning,
formatting of dimensions/measures,
deployment possibilities

…and of course scalability but hopefully the above should demonstrate to people new to BI that SSAS offers key functionality benefits compared with pivot tables.

Redgate pt1 – Database Lifecycle Management

Most people who work with SQL Server know about Redgate.  They are a well known company that create sql server productivity tools amongst other software.

I had used/liked their tools in the past but have been really impressed with them as a company over the last year as they are leading the way in creating a much better model for database development – not just trying to theorise on it, but also building the tools and running the workshops required to get it off the ground.

I attended a free DLM (database life-cycle management) course they ran back in April and now see it becoming the de-facto standard development model once companies have managed to catch-up.

Based-on a model that has been around in the app dev world for years, it basically automates all the manual processes in your development pipeline and gives you very early visibility of problems.

The simplified image below describes the model but you can get a full picture from their own website here.

redgate-dlm

Planning for the Future

“. . . the United States…is pioneering the development of a world-wide information grid.  It is expected that such a grid will come into being by about 1975.  For the first time in history the cumulative knowledge of mankind will be made accessible on a global scale—and it will be almost instantaneously available in response to demand.”

– Zbigniew Brzezinski, Between Two Ages : America’s Role in the Technetronic Era (Viking Press, 1970)

Brzezinski has been one of the most influential ‘statesmen’ of the last 50 years.  If you follow politics you will be aware of the impact he has had over the last few decades.

I recently read Between Two Ages and the book is full of examples of how people in this field are able to think 10, 20 or even 30 years into the future.  In particular, the quote above describing the internet, written in 1970 shows just how possible it is to predict change.

It makes me think that Architects in the DWH/BI space should be able to broadly know what is coming down the line for at least the next 2-3 years and to plan accordingly.  A lot of companies I know of are just replaying 10-year-old practices on more modern platforms.

For reference, technetronic means ‘shaped or influenced by the changes wrought by advances in technology and communications, especially electronic’.

Creating an ‘SSIS Configurations’ report

When starting my current role, I didn’t get the opportunity to have a handover period from the departing developer.  The company had a lot of SSIS packages and I needed to be able to look at safely testing them without affecting production file shares etc.

In the end I asked a consultant who was working with me (and who knew C#) to write something to help me extract the .dtsconfig data into a spreadsheet.

Once I had the data in Excel, I imported it into SQL and built the below SSRS report so I could see side by side comparisons for each of the packages…

SSIS_ConfigReport

I have made the tool available on my mega account if you want to give it a go yourself.  Instructions also provided in the folder.

dtsConfig-tool (choose download as zip).

Thanks to Steve Cage who wrote the extraction tool and let me post it online 🙂