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…
Multiple value parameters are a common tools used in SSRS. You can use this tool to select which columns actually show on a report. You will need to create a multiple value parameter and place an expression on each column on the report. More specifically the expression needs to be…
If you need to call an AS 400 program from an SSIS package this can easily be accomplished with an Execute SQL Task. This can be made even easier with two variables and an expression too. The AS400 program QCMDEXC is usually called using the CALL QSYS.QCMDEXC command. This is…
When using the Bulk insert task in SSIS, you might encounter an error stating that The File Does Not Exist or The Path Specified Cannot be Found. This is usually due to the fact that the file must be on the same server as the database. In other words, it…
In a lot of industries there is a popular calculation called “Per Member Per Month Per 1000” calculation or the “Per Customer Per Month Per 1000” calculation. This is used to determine the level at which the company is rendering services compared to the number of company customers. This calculation…
In Reporting Services 2008 r2 and above there is a great tool called the Map tool. It is great for visualizing data across a map. One of the issues I have run into is overlapping labels. For example I am trying to track where people in the company are at… Read more
I have been doing interviews for Pragmatic works for the past few years and I have come across quite a few people who wanted to be hired as a senior BI consultant and their skills were just not up to the level of a senior consultant. It seems they work… Read more
Crystal Reports has a special field name “DataDate”. This date shows the last time the data on the report has been executed. A recent client asked for the similar functionality in SSRS when a snapshot is run. They wanted to see the actual date for to be used as a… Read more
PowerPivot is a powerful new tool from Microsoft that has been improved even more in the 2012 release, which you can download for free here. In this blog I will give you a PowerPivot workbook that I created to connect to the Reporting Services logs and pull data. This… Read more
This week I was building power pivot models for a client pulling data from Oracle. One table kept giving the below error when trying to import:
OLE DB or ODBC error: Accessor is not a parameter accessor..
Out of line object Datasource referring to id ….
Out of line object… Read more
In SSIS 2012 there is a great new feature called environments. They can be thought of as a collection of parameters or variables. One of the questions I get asked when teaching SSIS 2012 is “Can I duplicate an environment?”. There is a move feature already included. But this moves… Read more
I did a webinar during the 12 days of 2012 on how to use Report Builder 3.0. In this webinar I showed how to pull data from Stored Procedures and Shared Data sets. I also showed the issues that come with some of these items.You can see the webinar here:… Read more
If you want to execute a set of SSIS packages in SQL Server 2008 or 2005, you can do this using T-SQL. First you will need a table with all of your package names on it. Then a While loop to execute each package.
Here is the example code:
Declare… Read more
I was working on a Cube in Analysis Services for a client recently and needed to sort on a field that was a varchar but contained numeric data. I wanted to sort as if it was numeric. I could not just convert this code to a number and sort on… Read more
With some calculated members in MDX it only makes sense to see the calculation if a certain Hierarchy is used.
Aggregate(YTD([Date].[Calendar].CurrentMember),[Internet Sales Amount])
This calculation only works in the Date.Calendar Hierarchy. I wanted to show the end users a message informing them about this and hide the calculation… Read more
Auditing in SSIS can be a real headache. In this two part series blog I am going to try to make it a little easier. You can also download my white paper and sample files on Auditing SSIS here. You can also see this post and more at SQL… Read more
Auditing in SSIS can be a real headache. In this two part series blog I am going to try to make it a little easier. You can also download my white paper and sample files on Auditing SSIS here. You can also see this post and more at SQL…
SSIS packages are great ETL tools and can do just about anything you need in terms of ETL. Most organizations start out creating SSIS package one by one until they have dozens, hundreds, or even thousands of packages. I have worked with one client that ran over 4,000 packages. This…
Data mining is a great way to help your company make decisions and predict future values. The Data Mining Algorithms built into SQL Server Analysis Services gives you this power.
The Adventure works data base comes with views that are already set up to perform data mining. Let’s take a… Read more
Previously I wrote a blog on how to do a for each loop to look through each col in an SSIS data flow here. Well things have changed since I wrote that blog, in fact I believe that old code only works in SSIS 2005. So I thought I… Read more