If someone tries to rename the column of table then we write a query as SP_RENAME 'TableName.[OldColumnName]' , '[NewColumnName]', 'COLUMN' --we use as SP_RENAME 'DimCustomer.[FirstName]' , 'CustomerName' , 'COLUMN' --Mistakenly, SP_RENAME 'DimCustomer.[FirstName]' , '[CustomerName]' , 'COLUMN' -- which gives the column the name [CustomerName], not CustomerName. For rename the column http://blog.sqlauthority.com/2008/08/26/sql-server-how-to-rename-a-column-name-or-table-name/ You don't use the [...
Read More
I recently had to set up another Date Dimension and went through the process of setting up all the properties in the date dimension so that when it is used in Excel, the end users would then be able to use the Date Filters built into Excel. The Excel Date Filters are very handy when creating Excel documents. As an example if you create your Date Filter to be for the last month. Every time you open your Excel document it will automatically filter to the current month. So below are the details on ...
Through this post , I want to share about the problem we faced while working in Windows Server 2003/2008. In our day to day work , we might frequently need to connect to remote Windows Server machine and while we try to connect to the Windows Server which allows multiple login session, sometimes we get an error message as : if there is no any free sessions “ If you need to connect the server urgently for your high priority task to complete then you simple need to find the sessions in that ...
Who's driving this car? At first glance it appears that as a developer, you have very little if no control over how MapReduce behaves. In some regards this is an accurate assessment. You have no control over when or where a MapReduce job runs, what data a specific map job will process or which reducer will handle the map's intermediate output. Feeling helpless yet? Don't worry the truth is that despite all that, there are a number of ninja techniques you can use to take control of how data moves...
I have been writing a lot of T-SQL scripts lately for regression testing. I identify test cases and write scripts to modify the data. Then the data can be verified as the rows go through ETL. As I tweak the scripts, I need to continually restore the source databases to get the data back to the starting point. Instead of doing restores, I am using the Database Snapshot feature. Database Snapshots were introduced in 2005 in Enterprise and Developer editions. They allow you to quickly revert a data...
I am late for posting this in time for T-SQL Tuesday, but I thought I would contribute anyway. This month the topic is about your career path. My career has focused on SQL Server since 2002, but it was a long, winding road to get here. I didn’t start out in technology at all; I began my adult working life in pharmacy. After getting to play with a TRS 80 computer in a class during my last year of pharmacy school, I became obsessed with computers. Programming became my hobby, and I learned s...
It appears that Microsft has silently refreshed some Services/Management portal as no blog or In specific im referring to the vizualization added to Windows Azure SQL Database We can now view Deadlock Failed Conections Throttled Connections etc You may view those by Logging into the HTML5 Management Portal Once in, you can navigate the new information as below Hit SQL Databases (on the Left Menu) --> Select a server --> Select a Database --> Select Monitoring Attached are a few Screensh...
This is just a quick tip to help with folks who present SQL code at events such as SQL Saturday. While most presenters use tools like ZoomIt (which if you present, please please learn to use this wonderful, free tool) sometimes it can get nauseating for attendees to watch you constantly zooming in and out, especially on code. A quick way around this is by using the magnification feature in SQL Server Management Studio 2012. To do this simply hold down the Ctrl button on your keyboard and with yo...
SP_SPACEUSED 'TableName' name rows reserved data index_size unused TableName 4 16 KB 8 KB 8 KB 0 KB
So I've decided to take a step in the right direction and begin the data modelling phase of my project. For those who didn't read my last blog post, I'm trying to find ways to study and get ready to take the BI Cert exam in September. I thought working on something for work might be a good way to start. So I'd like to begin by creating a small datawarehouse that tracks positions for investments. A position is the value of a portfolio of stock at a specific moment in time. So for example, the pos...
SSIS (SQL Server Integration Services) is well known for extracting data from a variety of sources. It can extract data from online sources as well. Google Spreadsheet is one of the online source which stores data online. In this post, we will learn to extract data from Google Spreadsheet using our favourite tool – SSIS. Pre-requiste: Before we talk about SSIS package, there are few concepts that we need understand. #01. To use Google Spreadsheet data you must have a Google account. With a...
In my last post ( HERE ) we talked about troubleshooting and resolving issues with problematic MDX queries. In this post we will look at techniques to tune and troubleshoot the processing side of your Analysis Services cube. Understanding Cube Processing Some of the common questions I hear as a consultant are "Why does my cube take 4 hours to process?" or "How can I reduce the time it takes to process my cube?". The answer to both of these questions starts with identifying the processing bottlen...
In this post I am going to deviate from Hadoop and HDInsight to focus on SQL Server Analysis Services Mutli-dimensional and more specifically MDX queries. As a consultant one of the common issues I encounter more so than design is that of performance. Typically, the performance issues SSAS users encounter occur in one of two realms: cube processing and query execution, while this post will focus on the latter we start by establishing a higher level of understanding of what happens when an XMLA c...
The D word...Documentation! I guess I am nutty but I think the product (whatever you making) is NOT complete until the documentaion is filed. There are two schools of thought here: documentation is not really important, who reads that stuff or sure do it but do not let that stop the deployment. The later is a half hearted attempt to appear that the higher ups are for it but they really do not care. HA! It should stop the deployment! I have worked only ONE and I mean one postion that had dcoument...
It's 2013, and I have several goals. Study for and pass the SQL BI certification exam - Implementing a DW with SQL 2012 Build a simple datawarehouse solution to practice with while I study Convince the powers that be, that it's a better idea to build something that buy something For #1, I need some advice on study guides, places to start, or essential books. For #2, I'd like to post my findings, mistakes, versions, and at times - frustrated rantings. Hopefully someone besides me will read this a...
Recently I was looking at the Adventure Works database and I noticed how Product, ProductCategory, and ProductSubCategory are their own separate tables. It got me to thinking about how someone might want to take attributes from each of those tables and create one hierarchy for reporting when you are looking at it. In DAX, this can be accomplished by using the RELATED() function. By using the related function in our table with our lowest level attributes, we can bring in the higher levels and cre...
Just a quick note that I'll be presenting this weekend, Saturday May 11th, at SQLSaturday in Rochester, NY . Exciting to return to Central New York as I graduated right down the road at Syracuse University . Go Orange!If you're in the area come on down, should be a great day of training. Check out the day's schedule ( link ), I'll be presenting two sessions this weekend: Do More With Less: Consolidate and Virtualize Policy-Based Management in a Nutshell You can follow the event along on Twitter...
A little while ago I blogged about how to created discretization buckets in a multi-dimensional cube. The nice thing about it is that it was just a few properties that you needed to modify, and was pretty straightforward. To the flip side of that I see a lot of companies wanting to take advantage of the new tabular Analysis Services model and the performance you get from it. The good news is that you can do the same thing in tabular, but they’re not properties you set. To set it up, first ...
In my last post, we took a helicopter tour of the MapReduce framework and its many facets. I believe its important to have a functional understanding of MapReduce even if you never intend to never work directly with it since the more user-friendly abstractions of both Pig and Hive depend on it. In this post we will again turn to Java as we let our fingers do the walking to build our first MapReduce program. For this demo we will start slowly, implementing first the map and reduce functions...
Sometimes, due to environment changes or some other reason, you want to change the workspace database that you are developing your tabular model against. I recently had a need to do this as my old tabular database instance was no longer active. When I went to open up the bim file, I received the following error message: To fix it, click on the bim file once to select it and then go to properties (F4). The last property on the page is called “Workspace Server” and specifies where the ...
I did a webinar on Monitoring SQL Server with SSIS on pragmaticworks.com. Check it out on the webinars page there. Here are the code files for that webinar. The code is done in 2008 R2.
I am going to be making two of my presentations: Managing your reporting Services Environment and SSIS Templates, Configurations, and Variables I hope to see you there. You can find the details of the event here: http://www.sqlsaturday.com/209/eventhome.aspx
IT is an interesting field; you are never done learning. In fact, things move pretty fast and it is hard to keep up. Even if you think you your job is stable, there are no guarantees and it pays to keep your skill sharp. I recently compiled a list of books that have helped me or are on my list to read (well, a couple of them I wrote) and I thought that list would be helpful to those who want to learn more about SQL Server. Learn T-SQL Beginning T-SQL 2008 Beginning T-SQL 2012 Microsoft SQL Serve...
Somewhere between teaching a BI Bootcamp class and wrestling my troop of kids, I promised myself I would get a blog post in this week. Luckily, I've had a few code heavy posts, so we will dial it back slightly as I briefly introduce MapReduce for Hadoop/HDInsight. Most of the MapReduce posts I've seen to date, talk very specifically about how to implement a C# MapReduce job on HDInsight. Before we go there, I think it's a topic that deserves a somewhat more abstract/academic discussion so that w...
Pragmatic Works hosts FREE live demos every Tues & Thurs @ 11 am EST. SQL Server, SharePoint, PowerPivot and Business Intelligence topics from Microsoft MVP's and industry experts. Join Jorge Segarra for Free Training on the T's 4/23/2013 In this absolute basics session, we’ll be talking about databases and SQL Server from an absolute beginner’s perspective. We’ll talk about what is a database, a table, an index, what are the basic terms of SQL Server and so on. This sessio...