As a Data Warehouse Consultant, I´m often asked the following by customers: “Is it possible to enrich our dimension data with some manually maintained data” and the answer is always “yeah, of course”. However, quite quickly the questions of:
- Who should input this data
- How do I inform that a new dimension value is available
- In which system should the user import this data
- And how can it been done in a user friendly manner
I have successfully used Master Data Services from SQL Server 2012 to manage this challenge.
Master Data Services offer an Excel plugin and a web application that makes it easy for business users to push data to a SQL Server. This tutorial will focus on the Excel plugin for pushing data.
This blog post will explain how you can push data from your SQL Server to your MDS implementation, thereby making it possible for the business user to enrich the data.
Normally I’m not a big fan of using trace flags, my advice is only to use these when it is absolutely necessary otherwise don’t. Here is a list of the documented trace flags that you can use with SQL Server. That is the documented once, besides that there is a list of undocumented trace flags – and it is as you might already have guessed one of these I’ll be blogging about today.[læs mere]
Every now and then standard queries just aren’t sufficient as data sources and therefore it might be neccesary to add an intermediate step. For instance by using a table variable. But using a table variable will by default not return any records. So what do we do, then?[læs mere]
Have you ever thought about logging the before and after value for an update or delete? It’s actually pretty simple getting these information as a return on an update and delete.
Lets make a quick example.[læs mere]
By default view are updateable. It means you can write a TSQL update statement against a view just like you can update a table. I both cases you off course need update permissions. This give you an opportunity to let users update views containing less columns than the original table. You can update data in a view even if your dataset is a join on two or more tables.
In this blog we will se how you avoid users from update data out of your view.[læs mere]
Maybe you are using views as a layer between your tables and your users. If you have a lot of views you properly know the case where a table is change and now your view is not working anymore.
Lets see how to avoid schema changes corrupting your views.[læs mere]
Almost every one of my customers is running backup of their databases (you would be surprised if you knew how many databases never been backed up.. but that is another story). Unfortunately a lot of these databases has never been tested with dbcc checkdb. dbcc checkdb is a little like the good old chkdsk in DOS. dbcc checkdb checks the logical and physical integrity of all the objects in the database. Not running dbcc checkdb in worse case this means that someone is backing up an corrupt database. In that case when you need to restore your database…. well it is not good.[læs mere]
SQL Server 2012 contains two new string conversion functions. PARSE and TRY_PARSE. These two functions make life a lot easier when you want to convert a string to another data type.
The different between the two functions are that PARSE will return an error if the conversion fails. TRY_PARSE will return NULL.[læs mere]