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...
Read More
Have you ever wondered how to force a carriage return inside a string within an SSRS expression or how to display a double quote ( " ) as a string? There's actually a fast an easy solution. The Chr() functions returns the character associated with the character code. Say for example, you have an expression using an IIF statement that displays one string or another, but you'd like to force a line break mid-string. A character code of 10 (ex. Chr(10)) will force a line feed effectively moving the ...
Thanks to all who attended the Implementing the SQL Server Fast Track presentation this week. If you missed it, you can see the recordings here: https://www1.gotomeeting.com/register/933664848 or download the slides her: http://www.bidn.com/Assets/Uploaded-CMS-Files/9888faa4-bbf7-40e8-958b-9835a2fb30a5BIE402-BRK-Bknight.pdf Resources Here are some of the resources you can find about the Fast Track: Fast Track Homepage: http://www.microsoft.com/sqlserver/2008/en/us/fasttrack.aspx Fast Track Datas...
Now that we have our feet wet, let’s get started with the next puzzle. In this puzzle we will write a query that returns Percentage Growth Year over Year. For example, if sales were $258,056 in 2006 and $389,456 in 2007, then the percentage growth would be (2007 sales – 2006 sales)/2006 sales. Here are the requirements: Columns : Internet Sales Amount and Percentage Growth (calculated measure) Rows : Calendar Years Filter : Only Show United States Bike Sales Hint : You can use the CREATE MEMBER ...
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 ...
This year at SQL Saturday #28 we will be featuring a User Group Booth alongside the Sponsor booths. The User Group booth will provide information about various User Groups that host meetings. If you are attending SQL Saturday and are a part of a group that would like to be involved you can either provide a handout that can be left at the booth for other attendees or contact Stacy Vicknair at stacy.vicknair@sparkhound.com with your group information including the group name, website, description ...
In Internet exists many solution for generating calendar. Original idea I've found here but i've done some changes with using some SQL 2008 features Perhaps it will be useful for somebody. Fiscal calendar in our company contains 364 days per year and has 4-4-5 schema Declare @BeginDate date = '20070428'; Declare @EndDate date = '20300101'; With nums(i) as (SELECT n FROM(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS D(n)), tmpPvt(i) as (Select s4.i * 1000 + s3.i * 100 + s2.i * 10 + s1.i as i ...
Sometimes you’ll want to manage reporting services functionality through SQL Server Management Studio (SSMS). This would be the case if you want to manage Jobs (subscriptions), Security or Shared Schedules. To connect to your reporting services instance, fire up SSMS and then choose Reporting Services as the Server type as shown in figure 1. Figure 1: Connecting to a Reporting Services Instance Then enter the url to your Report Server as shown above, verify the authentication method (in my case ...
The company I work for, Pragmatic Works, has a non profit organization called Pragmatic Works Foundation. We offer a free week of TSQL training for individuals who would like to join the I.T. community. The material covered will be based on SQL Server 2008. Now, originally we planned on having this training in June and were unable to keep that date scheduled due to planning for Tech-Ed in New Orleans. That week was a huge success. With that being said, we have decided to move the class to the en...
I was looking through the event bags from TechEd and it looks like we bought an extra Zune HD (16GB). So, I need to find an interesting way to give it away! I decided a great way to give it away is to the biggest community supporter. So, on July 29th, I'm going to the homepage and click Last 30 Days under Top Contributors and will give the Zune HD to the top name. Keep in mind that the Last 30 Days is a rolling 30 days so names that are active right now may drop off as the month goes on but on J...
If you have two or more records on the same row, and need to write each record on its own row in a destination, you have two choices. You can do this in series or parallel in a single data flow in SSIS. Here is the input table I am using for my example. Notice I have three names on one row. I need these to be inserted into a table with a first name and last name column only. So all three first name fields need to be mapped to the only first name columns on the destination and the same is true fo...
As powerful of a programming toolset as is the .NET Framework…Having that functionality available to a SQL Server database developers I would think would be well accepted. I would assume (maybe I shouldn’t assume) many database developers would salivate over the ability to apply program concepts available to C# and VB.NET developers, to SQL stored procedures. However, after talking to quite a few BI Consultants…I’ve found that it is not widely used. Why is that? Is it the lack of understanding o...
OK, are you ready to start the MDX journey. In MDX Puzzle #1 posting I presented you with the challenge of writing an MDX query based on the requirements that I outlined in that posting. In this solution and all subsequent solutions I will walk you through the process of writing the MDX restating each requirement along the way. Also, as I write the query I will define and explain any new concepts and functions that are introduced. Enough with the formalities let’s go. The first thing that you wi...
Most of us have used SSIS to load a flat file into a destination. A little known fact about the Flat File Source which is used to do this is that as it loads data into a destination it validates any numeric and date values to ensure they're indeed those data types. This validation adds overhead to the tune of about 8% extra per column of these data types. This is especially felt in fact table loads where you have loads of numeric or DateTime columns. Of course, this validation is done for a reas...
It's been a great month of free education from Pragmatic Works. The recordings from the previous ones are now posted and I have one on Tuesday on SQL Server Data Warehousing Fast Tracks. Check out all the information below: June 22, 2010 View Recording Powershell for complete beginners Featuring: Sean McCown , InfoWorld Editor and SQL Server MVP Topic Summary: Have you been wanting to learn powershell but don’t know where to get started, or even why you should bother? This is where you start. In...
2005 Business Scorecard Manager Released Nice-ish tool, pure scorecarding 2006 Microsoft buys Proclarity, one of the premier BI analysis tools for SSAS 2007 Performance Point is released . The product is a horrible mishmash of 3 products. Proclarity , unchanged, with it's own installer. PerformancePoint Planning . Very much a version one, this product is MS's first and last attempt to actually attack true (financial) performance management PerformancePoint Monitoring : BSM, plus new analytic cha...
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...
I was reading a great blog yesterday, posted by Keith Hyer , on Identifying missing value ranges in numeric sequences . His method used a common table expression (CTE) with a series of variables, followed by a simple SELECT statement. Then your output is a 2 column result set displaying the ranges of missing values in a given numeric sequence, such as an identity column on a table. Quite honestly, I can't think of a better method that displays missing values in a range format. However, I started...
If you're like me, you probably have a significant number of servers you access on a regular basis, which means every time you start up SQL Server Management Studio (SSMS) you have to log in to each server typing in user names and passwords and connecting one at a time. I find this very obnoxious and annoying. Thankfully, SQL Server Management Studio allows you to save connections to various servers via Registered Servers, which can include Database Engines, Reporting Services, Integration Servi...
Sometimes we might have to build a cube without a relational data source. In this scenario, we can build a cube without a datasource focusing on designing the dimensions and measures and then BIDS can generate the relational schema that is needed to populate the cube. The following are the steps to achieve this. Start a new analysis Services Project in BIDS. In the Select method to build the cube diablog box -- choose "Build the cube without using a data source". In the Define New Measrue dialog...
Today I did a webinar on MDX solution. For those of you interested you can grab the slides here and code example here . If you missed the session you can also download the recording here . We do monthly webinars so make sure to check view are site regularly for free training!
TinyInt: Exact numeric value with precision 3 and scale 0. The TINYINT can only store a 3 digit number with a maximum value of 255. BigInt: Exact numeric value with precision 19 (if signed) 20 (if unsigned) scale 0. The BIGINT can store 20 digits with a maximum value of 9223372036854775807. Date: Year, month and day fields. A day/month/year value. This data type can represent data in the future and in the past. Time: Hour, minutes and second fields. 00:00:00. This will give you the time of day v...
Below is a list and descriptions of some of the more common Data Types from Microsoft. Let me sum up the meaning of Data Types by saying that they are definitions of the size of data and what type of data that is allowed be put in a column which is located in a table. Char: Character String of fixed string length given by a number. CREATE TABLE BIDN ( Customers char ( 49 ) , Address char ( 100 ) ) Varchar: Variable length character string with a maximum string length given by a number. CREATE TA...
Once upon a time, in SQL Server 2005, the SQL Server team was working tightly with the SQL Server product team to reserve a special spot of shared memory for SQL Server for SSIS to dump into and then as SQL Server would write into the database seamlessly. This was done with the with the SQL Server Destination in SSIS' data flow. The net result in SQL Server 2005 was about a 8% gain in performance typically in my older benchmarks. Enter SQL Server 2005 SP2 stage right. The big tenant of SQL Serve...
What's the issue? Have you ever worked on a table with an identity column and then deleted data from the table? It creates "gaps" in the ID numbers. Today I found myself looking for missing IDs so I would know where my gaps were. So what do you do? First, let's recreate the conditions in a temp table: SET NOCOUNT ON go IF EXISTS( SELECT TOP 1 1 DoesItExist FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#mytestrows' ) ) BEGIN DROP TABLE #mytestrows END go CREATE TABLE #mytestrows( ID INT ...