Who is online?  0 guests and 0 members
Home  »  Blogs  »  MikeDavis

Communifire Blogs

Blogs RSS Feed

MikeDavis : Most Recent postings

MikeDavis

Monitoring SQL Server with SSIS

4/29/2013 by MikeDavis  -  Comments: 6  -  Views: [4978]

I did a webinar on Monitoring SQL Server with SSIS on pragmaticworks.com. Check it out on the webinars page there. Here are the code files for that webinar. The code is done in 2008 R2.

Read More

MikeDavis

SSRS Hide or Show, Some or All Columns on a Report

10/23/2012 by MikeDavis  -  Comments: 6  -  Views: [91451]

Multiple value parameters are a common tools used in SSRS. You can use this tool to select which columns 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 on the column visibility property of the columns. Here is an example report with five columns. Here are the parameter available values with the column choices. I hard coded these choices into the available value fields; you cou...

Read More

MikeDavis

Studying and Learning Business Intelligence

10/2/2012 by MikeDavis  -  Comments: 5  -  Views: [3180]

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 in an environment that corners them into doing things a certain way and not giving the opportunity to grow pass their defined role. So I put together a list of study items to help people ramp up in BI skills.   Here are some suggested books: ...

Read More

MikeDavis

Using PowerPivot to Monitor Report Services

8/7/2012 by MikeDavis  -  Comments: 1  -  Views: [7468]

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 gives you the ability to see the performance of your report server very easily. Report Example: Keep in mind that all the data in PowerPivot is pulled into memory so if you have a large reporting environment you may have to limit ...

Read More

MikeDavis

Crystal Reports DataDate in SSRS

7/16/2012 by MikeDavis  -  Comments: 1  -  Views: [2366]

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 print date but they wanted to have the date the data was pulled for the report. This can be accomplished with the build in field in SSRS named ExecutionTime. This will return the date the report ran last even when the snap shot is pulle...

Read More

MikeDavis

Oracle Date issues with PowerPivot

5/29/2012 by MikeDavis  -  Comments: 0  -  Views: [1714]

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 datasourceview ? temp_dsv Here is the image: None of this error made any sense. It was just a table in Oracle we were trying to pull from. After some troubleshooting I was able to find that a date column was causing the issue. ...

Read More

MikeDavis

SSIS 2012 Copy or Duplicate Environments

5/8/2012 by MikeDavis  -  Comments: 2  -  Views: [1992]

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 the environment instead of copying. Now you can write T-SQL Scripts like the one at the bottom of this blog to create an environment and create the variables in the environment, but this is time consuming. You can scri...

Read More

MikeDavis

The IRS and SSRS

4/16/2012 by MikeDavis  -  Comments: 0  -  Views: [1788]

Recently I did a webinar on the IRS and SSRS. In this webinar I covered creating reports with images and forms. I also covered how to manipulate data and showed you how to cheat on your taxes(not really).You can download the files for this webinar here: http://www.bidn.com/Assets/Uploaded-CMS-Files/IRSandSSRS-cefd99fa-6074-4be6-84ee-db1490dd3f42.zip You can watch the webinar here: http://pragmaticworks.com/Resources/webinars/Default.aspx

Read More

MikeDavis

How to use Report Builder 3.0

4/16/2012 by MikeDavis  -  Comments: 0  -  Views: [2404]

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: http://swrt.worktankseattle.com/series/59/seriessignup.aspx

Read More

MikeDavis

Execute Multiple 2008/2005 SSIS Packages with T-SQL

2/16/2012 by MikeDavis  -  Comments: 5  -  Views: [4621]

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 @FilePath varchar(2000) Declare @cmd varchar(2000)   DECLARE @package_name varchar(200) Declare @PackageCount int Declare @X int Set @X = 1 Set @PackageCount = (Select COUNT(*) from Packages) set @FilePath = 'C:\Package Path' While (@X <= @...

Read More

MikeDavis

Pragmatic Works Win Software Awards

11/22/2011 by MikeDavis  -  Comments: 1  -  Views: [1576]

Our Product BI xPress won two awards from SQL Server Magazine. Check it out! http://www.sqlmag.com/content1/topic/2011-sql-server-magazine-editors-community-choice-awards-140830/catpath/awards/showprivate/1/page/3

Read More

MikeDavis

How to hide Calculated Members in MDX SSAS

10/28/2011 by MikeDavis  -  Comments: 0  -  Views: [3391]

With some calculated members in MDX it only makes sense to see the calculation if a certain Hierarchy is used. For example: 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 at the same time. Here is how I did this: Case when ([Date].[Calendar].level is [Date].[Calendar].[Calendar Year] or [Date].[Calendar].level is [Date].[...

Read More

MikeDavis

SQL PASS Summit 2011 The Aftermath

10/17/2011 by MikeDavis  -  Comments: 0  -  Views: [1756]

I just returned from Seattle, WA and my first time speaking at SQL PASS Summit. I did the SQL Smackdown T-SQL vs SSIS Loading a Data Warehouse with Adam Jorgensen. The session was a hit, it was standing room only and the room was full from beginning to end. Adam did a great job showing the T-SQL method and I showed the SSIS method. In the End The referee Devin Knight and Adam jumped me a stomped me into the stage. I survived and will live to put on another SQL Smackdown. You can download the fil...

Read More

MikeDavis

SSIS 2008 For Each Column in a Data flow

9/29/2011 by MikeDavis  -  Comments: 0  -  Views: [2989]

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 would be good to update to SSIS 2008 and show you new and better way to loop through all of the columns in a script tasks. You could do the same work with a derived column. The problem comes when you have hundreds of columns and you need to do the same work on eac...

Read More

MikeDavis

SQL Saturday 85 Orlando the Aftermath

9/27/2011 by MikeDavis  -  Comments: 0  -  Views: [1848]

SQL Saturday 85 in Orlando , FL on September 24, 2011 was a great event. The people putting it together did a great job of putting on the event. The logistics on these events must be a daunting tasks and they handled it perfectly. I would like to personally thank Bradley Ball ( Blog | Twitter ), Karla Landrum( Blog | Twitter ), and Shawn McGehee( Blog | Twitter ) for the work they did to put this SQL Saturday. I apologize to anyone I left out. In my first session I covered SSIS Script tasks. You...

Read More

MikeDavis

BI Xpress Infomercial

9/21/2011 by MikeDavis  -  Comments: 0  -  Views: [1775]

Check out this cool Infomercial Pragmatic Works made about BI xPress. http://pragmaticworks.com/landing/bixpressinfomercial.aspx

Read More

MikeDavis

White Paper - Troubleshooting SSIS

9/19/2011 by MikeDavis  -  Comments: 0  -  Views: [4296]

My SSIS White Paper has been published! Download the White Paper and the Sample Files here. "SSIS is a fantastic Microsoft tool for performing Extract Transform and Loading (ETL) procedures. A challenge in SSIS is identifying a problem once you deploy your packages. In this webinar you will learn how to both identify and solve your SSIS issues." REGISTER NOW! What you'll learn: ? How to set up auditing on SSIS packages ? How to set up logging on SSIS packages ? How to use both native t...

Read More

MikeDavis

SSIS Web Service Task

9/8/2011 by MikeDavis  -  Comments: 13  -  Views: [41053]

The Web Service task in SSIS can be used to call a web service command to perform a needed operation in your package. The results of most web services will be in XML Format. You can save these results into a variable or into a file. Either way, you can then parse the XML to find the node values you need. This can be done with the XML task in SSIS. Click here to see my blog on the XML task . The Web Service I am using is a free demo service. It allows you to enter a zip code and returns the city ...

Read More

MikeDavis

SQL Saturday Orlando – Pre Con

9/6/2011 by MikeDavis  -  Comments: 0  -  Views: [2277]

I?m very proud to announce that Pragmatic Works will be presenting a Business Intelligence Workshop pre-con for SQLSaturday #85 in Orlando on Friday September 23rd! In this session you?ll have not one, not two, not three but FOUR Pragmatic Works consultants walking you through how to develop a full business intelligence solution from the ground up! Your instructors for this awesome pre-con are: Jorge Segarra ( Twitter | Blog ) I?m a BI Consultant for Pragmatic Works and a SQL Server MVP. In addi...

Read More

MikeDavis

SQL Smackdown at SQL Sat 85 Orlando

8/29/2011 by MikeDavis  -  Comments: 0  -  Views: [2411]

Are you ready for a knock down drag out fight! Come See SSIS vs T-SQL at SQL Saturday 85 in Orlando on September 24, 2011. Watch Mike Davis (@MikeDavisSQL) take on Luis E Figueroa (@LuisEFigueroa) in a head to head battle. Showing the best practices of each method. Also, check out the pre con with Mike Davis and some of the other powerhouses from Pragmatic Works. http://www.sqlsaturday.com/85/eventhome.aspx

Read More

MikeDavis

Create Date Dimension with Fiscal and Time

7/26/2011 by MikeDavis  -  Comments: 10  -  Views: [25959]

Here are three scripts that create and Date and Time Dimension and can add the fiscal columns too. First run the Dim Date script first to create the DimDate table. Make sure you change the start date and end date on the script to your preference. Then run the add Fiscal Dates scripts to add the fiscal columns. Make sure you alter the Fiscal script to set the date offset amount. The comments in the script will help you with this. This zip file contains three SQL scripts. Create Dim Date Create Di...

Read More

MikeDavis

SQL Saturday 85 Orlando

7/26/2011 by MikeDavis  -  Comments: 1  -  Views: [2006]

I am very excited to announce that the Schedule is now live for SQL Saturday 85 in Orlando, remember to save the date for Saturday September 24th! There will be a lot of information to come. There is a Full Day Pre-Con Training also. I will be seaking on SQL Server Denali and also on Scripting with .Net in SSIS. Hope to see you at my sessions. Please go to the link, http://www.sqlsaturday.com/85/schedule.aspx , to see the sessions and the tracks. Look forward to seeing you in Orlando!

Read More

MikeDavis

Vote for SSIS vs T-SQL: Loading a Data Warehouse Session for Pass Summit

7/13/2011 by MikeDavis  -  Comments: 0  -  Views: [1904]

PASS Summit is coming up fast, and my Session with Adam Jorgensen "SSIS vs T-SQL: Loading a Data Warehouse", is one of the comunity choices. Please take a moment and vote for my session. Be sure to vote for my Community Session for PASS Summit http://www.sqlpass.org/summit/2011/SummitContent/CommunityChoice.aspx SSIS vs T-SQL: Loading a Data Warehouse

Read More

MikeDavis

SSAS MDX Previous Year to Date

6/27/2011 by MikeDavis  -  Comments: 0  -  Views: [6510]

Getting the YTD from a cube is a simple MDX formula either using the PeriodToDate function or using the YTD function. One request you may get it to see the previous year to date. So if you are looking at March 21, 2011 year to date, you want to see the year to date from March 21, 2010. This can be done by using the ParallelPeriod function. Here is the Previous YTD calculation using adventure works. (PARALLELPERIOD( [Due Date].[Date].[Calendar Year], 1, [Due Date].[Date] ), [Measures].[Sales Amou...

Read More

MikeDavis

SSRS Top N and Bottom N Reporting with Duplicates

6/13/2011 by MikeDavis  -  Comments: 1  -  Views: [14754]

Previously I wrote a blog on Top N and Bottom N reporting. There were a couple of gotcha I did not cover in that blog so I thought I would cover those here to answer some of the questions I have received. The other blog can be found here . On a report you are trying to group the top N number of rows together and all others should be in a separate group. Let’s say you want the top 5 sales people based on amount. In my other blog I showed how to use the Top N and Bottom N filters on groups. The ma...

Read More