When you want to transform data in a data flow task in SSIS, the derived column transform performs this very fast due to the batch method. But if you have hundreds of columns that need the same transform performed on them, it can be very time consuming to create each of these derived column expressions. You can use a script task to loop through the columns in a data flow and perform the same task on every input column you select in the script task. Keep in mind, this will degrade performance, bu...
Read More
In some scenarios you will need to create a many to many relationship in your cube in SSAS. I have an article that shows you how to do this here . One of the problems that arise from many to many bridge tables are the size of these tables. In this example we have a fact table that contains the history of a person and measures for that person at a bi weekly basis. Each person can be in a Program. Each person can be in more than one program at a time also. We have thousands of people so the fact t...
When you are working with dimensional modeling there are some situations where several dimensional attributes don’t make since to be in any other dimension. When this occurs you can combine them into one dimension call a Junk Dimension. In this example you will see a person type dimension that only made since to be in a dimension together. This client has 4 different types of people in their system. The junk dimension will contain each possible combination of these person types. To avoid having ...
Reporting Actions in Analysis Services allows you to open a report in Reporting Services. Most of the time users want to pass in some parameters to the report so it shows the relevant data. If the parameter name matches the item in excel then this is a breeze. The problem comes when you need to pass something different to the report. If the report has a date range on it you need to pass the report a start date and end date. If a user clicks on a reporting action in excel at the year, quarter, or...
KPI’s (Key Performance Indicators) in Analysis Services are a great way to show visually if a measure is trending in right direction based on previous levels. If you want to show a measure has increased or decreased since last month, this can be accomplished by using the MDX function PrevMember. You want the trend to show at the proper level only in excel. This way the users are not confused by extra trend graphics. This example will be using the adventure works cube and trend at the month level...
KPI’s (Key Performance Indicators) in Analysis Services are a great way to show visually if a measure is above or below a goal. Managing the goal numbers can be a headache because they made need to be updated often. By placing the goal numbers on a table you can dynamically keep the goals up to do by simply updating the table. The following example uses the adventure works data warehouse. You will need to create a new table. Here you can see one named KPI Goals and the data on it. This table con...
Pragmatic Works is offering a free seat in an SSIS or SSRS training class for any sofware purchased before the end of April 2011. http://pragmaticworks.com/Products/
I will be at the SQL Rally in Orlando May 10-13 teaching at the pre con. Come and learn all about business intelligence. http://bit.ly/fTmljU
In an Analysis Services Cube (SSAS), named sets can be used to filter data. One of the popular request I get for named sets are rolling date ranges. Here are some examples of rolling date range named sets I have created. There are several ways to make this happen, like using the Lag function, and Last periods, or casting the date as a string then using the StrToMember function to convert the string to a member. I like using the StrToMember function because I can manipulate the string anyway I ne...
Comparing Previous Rows in SQL I had a client recently that needed me to get the direction a student’s GPA was headed on a regular basis. To do this I needed to get the last GPA record on the table and the previous GPA. The dates on these rows were different for different schools and each year the dates could change, so the query had to be dynamic. I could not hard code in the dates. This was very easy with Common Table Expressions (CTE’s). Below is the query. The first CTE (CurrentDate) gets a ...
Standard Deviations with CTE’s in SQL I was working with a client recently getting the number of standard deviations a student was off the average number of suspensions. I accomplished this with two Common Table Expressions, (CTE). The first CTE gets the standard deviation and the average for the entire database. The Database holds all of the schools in the district and this CTE gives use the overall average. The second CTE gets the number of suspensions summed up and grouped by student. In the ...
A lot of companies have several departments and want to limit the sub sites users can view. For example, the IT department should only see the IT sub site, but the HR department may need to be able to see all sub sites. To set up individual security for sub sites you will need to stop the sub sites from inheriting from the parent. To do this click on the Site Action menu while viewing the sub site, then select Edit in SharePoint Designer. On the right hand side of the screen you will see the per...
There are several ways to create a custom navigation control in SharePoint. In the below image you can see the left navigation is hidden. This is done with the use of a master page and some custom CSS. Now you want to create your own list of links to other pages and content that you control. Notice this page is not as wide as the default SharePoint site. If your client has smaller monitors throughout the company this may be necessary as in this case. The first step is to create a new web part pa...
Normal 0 false false false false EN-US X-NONE X-NONE SQL 2011 Denali New SSIS Parameters SQL 2011, code name Denali, has some great new features for SSIS. One of the most powerful features is parameters. These are a lot like variables in the package. But when scheduling a SQL agent job to run a package there is a new tab for parameters. Now you can set your package parameters without having to set the variables using the command line code. More on this later.
If you have not seen the new Project Crescent from Microsoft yet, take a moment and check it out. The videos look great and the silver light controls are beautiful. I can't wait to get my hands on this. http://www.microsoft.com/showcase/en/us/details/a90a1be4-7125-46b6-abdb-66966d764ddd
I spent last week in New York, NY co hosting the SSAS workshop with Brian Knight. We had over 100 students! The whole event was a great success. The reviews we have recieved show it was a great experience for everyone. I had one student tell me "I have learned more in a few hours than I have in the last 3 months". We kept it fun and engaging with lots of audience participation. Check out Pragmaticworks.com and click on training for a list of the upcoming cities and other classes we offer.
Sorting a matrix report can be done interactively. To perform interactive sorting, your report will need a cell to click on to perform the action. In the image below you can see a basic matrix report with State as the row group, with year and quarter as the column groups. You want to be able to interactively sort the report on all of these groups. There is an empty cell above the state and this can be the location for the sorting actions. First you need to create two more cells though, because y...
Here is a script to create a Dim Date and Dim Time table in a data warehouse. I did not write this script but it is a popular request and seems to be hard for people to find on other sites. So I thought I would post it here to make it easier for people to download. You will need to alter the start date and end date to your desired time period. I have improved the script and added fiscal dates also. I published this on a new blog here.
Well it was a great SQL Saturday in Tampa and we all learned and networked alot. In my session with Aaron Nelson "SQL Smackdown" we compared powershell and SSIS. You can download a copy of the packages here . These packages are SQL 2008 In Iron Chef Devin Knight went head to head with Adam Jorgensen building BI solutions. Brian Knight and I were the MC's for Iron Chef. The speed pass worked out great, there was no backup at all. Great job Pam.
When creating a data flow in SQL Integration Services, it is debatable to place a set of derived columns in a single series path or to use a conditional split to break them up to perform the derived columns only on the rows that need it. So I set up a little test to determine the difference. I created a flat file with over 200,000 rows and i wanted to use three derived columns to change the color column to the hex value. I could have placed all of the code in one derived column, but the point is...
If you are reporting from a Cube in SSRS and you want to drill through to another report you can set up actions just like any other drill through except one small change. The parameter you pass to the drill through report must be changed to Unique Name instead of value. Go to the detailed report that you are drilling down to and right click on the datasets folder then select show hidden data sets so you can see the parameter dataset. In this query you will see the key under the parameter value. ...
The pivot transform in SSIS is already a pain to use. When you have bad data it makes it even worse. In this blog I am going to show how to handle bad data when using the pivot transform. You should understand how to use the pivot transform already and Devin Knight has a great blog on how to do that here . Here is the input table: The output table should look like so: This is a situation where the users have entered data and they have left off the types on the input table and therefore we do not...
Sometimes we get data in some bad forms. For example look at the below table: The names and the amounts are comma separated in two different columns. Let’s imagine we need to get this data into a table like the one below: Notice the column names are the names that were in the data. This can be accomplished with derived columns and conditional splits in an SSIS data flow. Here is an example of a data flow that accomplishes this: After the source the first component is the conditional split. The c...
When working with SSAS it is sometimes necessary to process every dimension before continuing with development. This can be accomplished with a couple of clicks on the menu bar. Click on Database and then process. Then you will see each dimension processing.