One of the powerful features of SSAS 2005 (and above) is the capability to have one cube with multiple measure groups at different grains. For example, say we have sales facts, loaded from an order system, that have a grain of day and forecast facts, from a 3 rd -party forecasting tool, that have a grain of month. Forecasts typically become less and less accurate as we lower the grain. Annual forecast may be a best guess. A day level forecast may be challenging to build and highly suspect if we ...
Read More
I worked on ETL application last year. We were extracting data in flat files from online system that was in COBOL. The data files were not even - means each row was not having same amount of columns (csv values)-. Since it was Oracle, we use External Tables very extensively to import data and it was fine. Lately, I was trying to do similar thing in MSSQL. I post a question in BIDN forum about the same earlier toady (http://www.bidn.com/forums/microsoft-business-intelligence/sql-server/71/how-to-...
I use this query often to find nth MAX value. --Create table & insert data IF OBJECT_ID('MINMAX') IS NOT NULL DROP TABLE MINMAX GO CREATE TABLE [MinMax] ( [srno] [int] IDENTITY (1, 1) NOT NULL , [val] [varchar] (2) COLLATE Latin1_General_CI_AS NOT NULL , [MARKS] [int] NULL ) ON [PRIMARY] GO INSERT INTO [MinMax]([val], [MARKS]) VALUES('A', 50) INSERT INTO [MinMax]([val], [MARKS]) VALUES('B', 65) INSERT INTO [MinMax]([val], [MARKS]) VALUES('C', 85) INSERT INTO [MinMax]([val], [MARKS]) VALUES('...
Someone was asking how they could dynamically execute a given package dynamically, without hardcoding the package name into the Execute Package Task. Well, I believe this is what they were asking anyway, and it is a good enough excuse to talk about Expressions in SSIS again. You probably already know about Expressions, but let's just add one more example to the list, dynamically executing a given child package at run time. This is easily accomplished by first creating a variable to hold the name...
Recently I tried to deploy a report to a Report Server on my laptop, which is running Windows 7 Ultimate. Unfortunately I received the following error: The permissions granted to user ‘MachineName\SomeUser ‘ are insufficient for performing this operations. This confused me a bit since my account was an administrator. After digging a bit I found a very simple solution. Right-click on the Visual Studio icon or the Business Intelligence Development Studio icon and click Properties . Then go to the ...
When converting a string of numbers to a date you will need to enter the dashes or hyphens in the string before you run the convertion through a derived column transform. Here is the string I am passing in: 20090321 The derived columns has the following code: (DT_DBDATE)(SUBSTRING(stringdate,1,4) + "-" + SUBSTRING(stringdate,5,2) + "-" + RIGHT(stringdate,2)) The end result is a date as a date datatype.
I have read several articles about deploying SSIS packages. Most focus on using the deployment wizard or simply copying the files and their dependent properties onto the server. For companies with a small number of SSIS packages the later appears to be the method of choice. Recently, I was asked if there was a way to accomplish this without leaving Business Intelligence Development Studio (BIDS). To deploy a package from BIDS complete the following steps: 1. Open your SSIS package in BIDS: 2. On...
I'm currently working on a project where I'm pulling filenames from a directory into a database to check for name consistency against a 'master' table. The filenames take the form: Abbey Lane Cemetery 03022 Floor Plan Abbey Lane Primary 4-11 2001 Floor Plan Abbeydale Grange Secondary 11-16 4254 Floor Plan Abbeydale Industrial Hamlet 00433 Floor Plan Aldine House CYP0048 Floor Plan To check against the 'master' table, the entries need to be in this format: Abbey Lane Cemetery Abbey Lane Primary 4...
Assume we have a regular six-faced dice. What is the expected value of pips that you get from one throw? Any mathematician will tell you it's 3.5 ( (1+2+3+4+5+6)/6 ). Let's see what SQL Server thinks. DECLARE @Dice TABLE ( pips int ) INSERT INTO @Dice SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 SELECT AVG ( pips ) FROM @Dice And the result is 3! Why is this so? Well, Books Online do say that for an integer argument the return type is int . This in itself i...
Often we get asked to apply a sort to a Parameter Picklist in SSRS, or even simply sort a dimension hierarchy in our MDX statements. i.e. Quarters descending or Products in alphabetical order. Whilst this is easy to achieve in SQL using the ORDER BY statement, it can present more of a challenge in MDX. Here are a couple of examples WITH MEMBER Measures.NullColumn AS ‘Null’ SELECT {Measures.NullColumn} ON COLUMNS, ORDER({[Time].[Quarter].Members }, [Time].CurrentMember.Properties(”Key”) ,DESC) ON...
For a little more than a year now I've been a part of the PASS BI SIG and have tried to contribute whenever possible. The SIG is split into several focus groups that meet on all BI topics. I help in leading the SSIS group. Today I actually had an opportunity to present to the Analysis Services focus group on the topics of Analysis Services Partitions and Aggregations which you can view here. If you think you would like to present on any BI topic contact me and we'll get you lined up as a speaker...
After my first day of SSIS training my boss walked into the office and said to me, "how was the first day of class". To which I responded, "great!" Then asked me, "so what is the difference between the control flow and data flow?" This posed a simple, but foundational concept, of SSIS. There are a few key things to remember when talking about the differences between control flow and data flow, and not just the completely obvious statement: data flow deals with data. Hopefully if you are beginnin...
Often times, you want to analyze sales using a year to date view as shown below: While you can do this in an MDX formula in SSRS, you might find this calculation valuable enough to store a server-side calculation. To do this, you'll need to pick what date dimension or role playing dimension you want to slice this by. After you know that, you can use an MDX calculation as shown below for this: Sum(YTD([Delivery Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount]) This will show a r...
I was recently asked how to convert a string to a date and if the date is invalid make it a null date value. This can be done with one derived column transform in SSIS. Here I have a table with one varchar column named StringDate, one row with a date, one without. In my SSIS package I have an OLEDB source in a data flow and a derived column next with a terminator destination. This destination is available in Task Factory . The derived column has one expression in it to create a new column called...
We're moving fast at the BI Developer Network (BIDN.com). In an effort to make sure you get your BI questions answered, we've installed a new twitter interface that if your question in our forums doesn't go answered after 2 days, it will escallated in a number of ways. We'll first feature your question in our newsletter and it will also be automatically be tweeted to http://www.twitter.com/bidn for any followers. Your question will be featured on twitter with a prefix of "BIDN User SOS" with you...
I've been working this week to automate the BIDN.com's twitter connectivity and to do so, decided to build a quick Twitter interface to communicate with BIDN. I needed a simple console application that could be called via a trigger or a SQL Agent Job (my actual method). The entire application winds up only being a 50 lines of code or so. Essentially from a command line, you pass in your twitter account, password, the message and optional URL. If you pass a URL in, it will be converted to TinyURL...
So there I was coding a very dumb, but necessary, SSIS package to refresh some tables on my test server from production, looking like the following: and as I had about a dozen more tables to do, I just couldn't take it any longer, and knew I had to find a more generic solution to my problem. As you may know from my posting on this in the SSIS forum ("Dynamic Data Flow?"), I simply needed to replace the contents of a table on one server with the contents from the same table on another server. I w...
If your like me, then you may have a little OCD and need everything to be completely organized in your environment. BIDS has been annoying me in this respect lately. I am working on creating a data warehouse and will be starting on the next portion of the project shortly. I wanted to keep everything for it in the same solution, my warehouse ETL as well as the work on the Cube. So I figure, create the solution and add a new project for the Cube. So I create a new project and much to my dismay the...
A new feature of Reporting Services 2008 R2 is the ability to 'easily' rotate text 270 degrees. This was very difficult to do in the past and required that you create images for your headers instead of using text. YUCK! Naturally, this is not a preferred work around. So in this blog post I will show how easy this is in the future version of SSRS. Let's start with my boring report: In this report you can see that the data for the Business Entity field takes up much left space left to right than i...
Where are my “Indent” and “Un Indent” icons within Bids “Script Task Editor”? On the default toolbar, you have comment and uncomment code, but Indent and Un Indent are missing. You'll have to customize your tool bar and add them. From the Menu click thru View -> Toolbars -> Customize… Within your Customize Window, select “Edit” from the Categories: window and then scroll in the Commands window until you see “Line Indent” Now click and Drag the “Line Indent” icon up to your Standard tool ba...
Hopefully if you are reading this post, then you have already read PART 1 of this series. http://www.bidn.com/blogs/bretupdegraff/bidn-blog/263/exploring-row-and-page-compression-with-sql-2008-part-1-the-research In review, we are taking an 80 Gig data warehouse database and seeing what the effects of SQL Server 2008 compression are on this database. Part 1 we reviewed what compression is at a high level and refer to a great white paper on compression. For this post, we are going to use some too...
Our application runs on SQL 2005 and so we are looking at the upgrade path to SQL 2008 and even SQL 2008 R2. This application has been installed at over 20 locations (clients) and they all have thier own installation and environment for SQL Server. In order for our clients to realize the value in upgrading to SQL 2008, I need to find that value. One area that has real promise is Row and Page level compression on tables and indexes. Our largest clients have significantly large databases (80G-250G...
Well, I finally got around to taking a look at R2 and specifically Master Data Services (due mostly to some strange decisions on my current data migration project). First impressions - I actually quite like it. I can see that it is going to require some deep late-night learning to completely get my head around it all but it's certainly not what I would call a Version 1 product (actually given that it's born out of Stratature +EDM it's more like version 4/5) . The new web based UI is pretty clunk...
In part one of this post I showed how you can use a report to run SQL Server Agent Jobs by using a report. In that post I only showed you how to run a single job but hardcoding in the job name. Someone recently asked if it was possible to have a drop-down list of jobs to choose from instead of have the hardcoded job name so I thought it made for a great opportunity to write a part 2. Step One Create a Data Source that points to MSDB on the server that the SQL Agent job that you want to run is lo...
SQL Lunch # 5 – Looping Logic in SSIS Speaker: Tim Mitchell Topic: Looping Logic in SSIS Meeting URL: JOIN MEETING ADD TO OUTLOOK CALENDAR Description: Software developers have long taken for granted the ability to run iterative processes by using loop structures in their code. For SSIS developers, these same structures are available through the For Loop and the For Each Loop controls. In this session, we'll review each of these containers and demonstrate how they can be used for repetitive proc...