Just found out that PASS is coming to Orlando it will be somewhat scaled down should be 2-3 days. Here is a link that explains in more detail. http://sqlserverpedia.com/blog/sql-server-bloggers/pass-2011-spring-event-in-orlando/
Read More
This is an issue that stumped me the first time I saw it. Here I am going to try and replicate it. First I added a column to the FactInternetSales table named TestColumn: Next I checked to see if it is available for a measure So now you say “You haven’t refreshed your Dataset” Thats true and of course we need to do that to refresh the schema stored. So lets do that. I went to the dataset and right clicked on the background and selected refresh. Now it shows it will refresh the testdatacolumn Let...
Recently while processing a cube I ran into an error that like many other errors in Analysis Services seemed somewhat cryptic. However after analyzing the error in question I found it to be much more descriptive than I had thought. So I would like to share how I was able to determine what the error meant. First I needed to re-create the error, to do this I edited my partition source and set the order date key to equal a constant of three question marks. The reason this will error is due to the f...
Reverse Engineer an SSAS Database and Cube Posted on June 29, 2010 by mmollenhour If you are like me and have ever made a mistake while working in bids on a cube you may want to restore your bids environment as it was when you last processed your cube. This is actually quite easy to do but sometimes hard to find. So I decided to walk you through this very useful feature. First you open up your bids environment and click new Project as below: Screen clipping taken: 6/28/2010, 8:55 PM Now you can ...
Recently I had a customer that wanted to use their current active directory grouping and security for their reporting services security. To accomplish this of course we had to have rights to their active directory server. The next step was to add a link server to their SQL Server. In our case this was the SQL Server 2008 but that should not be necessary. Since there are several articles already written about adding a link server to SQL Server to reference an active directory server I will just p...
Often times in reporting we find ourselves with a need to do dynamic grouping. This can be accomplished in several ways one way to do this would be to actually build this into the report with dynamic grouping in formulas. The other way to accomplish this is to actually change the grouping inside a stored procedure returning this to the report in an already grouped nature. This process allows SQL Server to do the grouping on the database server. There are obviously disadvantages and advantages to...
Recently I had a report to duplicate in SSRS and had issues trying to find where a particular field was in the database. Since getting the requirements seemed very difficult I figured it would be easier to create a proc that could search for this text in all columns. So this lead me to write the following proc. --------------------------------------------------------------------------------------------------------------------------------------------------------------- Create Proc [dbo].[usp_Find...
Over the years of being a BI Consultant I have found that there are a lot of power users with excel and Excel is where they feel most comfortable. While Reporting services has made leaps and bounds over previous versions it still takes some extra effort to create a report and export it to excel. Another option to get data back in to excel is to use the query builder here are the steps: Click the data tab in excel 2007 and select "from other Sources" at the bottom of the dropdown you will find th...
SQL Analysis Server Cubes allow for properties to be assigned to members. This is a noce feature but it doesn't seem to be exposed by many applications. Natively Excel is one of these apps, however by using the pivot tables extensions for excel addin this is now possible. Here is a link to the Addin: http://olappivottableextend.codeplex.com/ After downloading and installing this addin you will now be able to search these descriptions, here is how I tested it. First I added the description throug...
Often with reporting on a data warehouse you see a common theme that many companies want to report on things with the context of my current date. Take for instance a sales report where you want to see this month’s sales activity. There are several ways to handle this but I always try to side with keeping my business logic in the data warehouse so I have one place to maintain this. So the method I prefer is having flags in the Date dimension to indicate current month, current year, current day et...
Many times while working with SSIS I have had issues where I would like to add a temp table to a stored procedure that is a source for my SSIS dataflow. One work around for this is create a temp table variable instead. The issue with this is if many rows are in this temp variable the performance can be severely degraded. So the other day I was searching for workarounds and found this link http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/65112/ . Here Michael Cape gives a Wack...
Probably the most no-brainer in a DataWarehouse scenario is to index the heck out of the Date dimension. The cost of an index involves a couple of things: 1. How many inserts are performed on the table? 2. Fragmentation that may occur. 3. How much gain can be achieved by selects against the tables. So first off date dimensions only need a row per day so very little inserts occur. Fragmentation can be maintained by disabling and rebuilding the index. Last but not least the date dimension is argua...
Many times we are forced to use functions in a where clause to filter results. The issue is here that rarely can SQL take advantage of an index if a function is used. SQL can perform OK in most of these scenarios, but what if you have a query used over and over again and you need it to perform as fast as possible. For the sake of this Blog let’s assume we have a query that needs to filter rows by month. In our query we would typical have something like this “where month(salesdate) =3”. So what a...
Recently working for a client I had an issue where a little after sql was started and the memory got to approx. 50 % the whole OS (Windows 2008 r2) became unstable. This could be recreated easily and the OS would get so unresponsive the performance monitor (with just CPU selected) would leave blanks on the line drawing. Troubleshooting this was difficult since the CPU usage was low and Io was low and 50% of the memory was still available. Come to find out the OS must have been paging out SQL Buf...
I was at a client’s site recently discussing the benefits of dropping an index and rebuilding at the end of a large batch (SQL 2005 or SQL 2008). There are several things I have learned in my career but one of the most important things has been to try to eliminate hard coding and the old keep it simple technique. So say I have 4 procs that I am using to insert into the same table and I decided for better performance I will drop the index and recreate it at the end of my load. Does anyone else se...
Today I was working with creating a rolling date based partition scheme. In this scheme I wanted to switch out the earliest date range so I could switch in the new. To do this you need to create a staging table. My question was how do I create this in such a way that if I make changes to the table this code will not need maintenance? First thought was to do a select into unfortunately the select into does not support direction to a fileGroup which with switching out the data this staging table h...
During the process of trying to Schedule a subscription in reporting servies with a large report I received this error: This is easily corrected by changing the Web.config file for Reporting Services this is located on your Report Server in the reportManager directory as shown: Edit this Web config file adding or replacin with this line <httpRuntime executionTimeout = "9000" maxRequestLength="500000" /> This will incress the timeout and the length of the data able to be pushed to the repor...
I recently had an issue that I wanted to verify that my partitioning strategy was working and distributing the workload as I hoped. Windows 2008 has a great feature to do this built into Task Manager. First open Task Manager by right clicking toolbar and selecting start task manager (nothing new there). Then just click resource monitor and open DISK by double clicking and then sort by write by double clicking the header for write b/min. This proves to be a very useful way to see if your tempdb i...
The Slowly Changing dimension task works great for small loads on update/inserts but if you have a large dataset that you want to use what can you do? One new answer ( sql 2008) to this question is to use the merge statement. In exploring this option I found the merge statement to be very powerful yet very verbose requiring a large amount of development time. To get around this I wrote a stored procedure to generate my stored procedure automatically. First I created a database called Stage and a...