Martina Ponca's Blog

Environment:
Windows XP Professional (32-bit)
SSIS 2008 R2
Linked Server:

EXEC master.dbo.sp_addlinkedserver @server = N'VFP',
	@srvproduct=N'foxpro', @provider=N'VFPOLEDB',
	@datasrc=N'c:\MyFolder\', @provstr=N'VFPOLEDB.1'

GO

I tried to access the FoxPro files from the linked server but was getting the following error:

The OLE DB provider “VFPOLEDB” for linked server “VFP” reported an error. Access denied. Cannot obtain the required interface (“IID_IDBSchemaRowset”) from OLE DB provider “VFPOLEDB” for linked server “VFP”. (Microsoft SQL Server, Error: 7399)

When the SQL Server service account run under NETWORK SERVICE or a DOMAIN Account, it didn’t work even though the domain account did have permissions to the FoxPro files. When I changed it to “Local System” it worked. To make it work with a domain account, I found the following answer in http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/a525b569-77ca-41da-96e1-2ba1938dd32d

1. Start –> Run –> Dcomcnfg
2. Component services –> My computer –> DCOM config –> MSDAINITIALIZE
3. Right click on MSDAINITIALIZE –> properties –>security
4. Add the SQL Start-up account ( you may have to go to services in administrative tools under control panel to check log on account) under “launch and activation permission”, “Access permission” and “Configuration permission”.
5. Give full rights.

Environment:
SQL Server 2008 R2 Integration Services (32-bit)
Microsoft OLE DB Provider for Visual FoxPro
C:\Program Files\Common Files\System\Ole DB\VfpOleDB.dll 9.0.0.5815

I’m getting the following error when I click on “Columns” while editing OLE DB Source using “Microsoft OLE DB Provider for Visual FoxPro” named “Source – myTable”:

Warning:
Warning at {….} [Source – myTable]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the “DefaultCodePage” property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component’s locale ID will be used.
Fix:
Edit the properties of the OLE DB Source, [Source – myTable] and set property “AlwaysUseDefaultCodePage” to True.

This error happens because the server does not have a default code page for Visual FoxPro in “C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles”.

The following blog posting was very helpful: http://venkattechnicalblog.blogspot.com/2008/07/ssis-errors-part-4.html. Thank you.

I have encountered the following issue and reported it to Microsoft (Bug 697259 on connect.microsoft.ca). It’s marked as “Won’t Fix”, but in case you have the same issue, the workaround might come handy:

https://connect.microsoft.com/SQLServer/feedback/details/697259/ssis-2008-r2-ole-db-provider-for-visual-foxpro-ole-db-source-includes-deleted-records-even-if-deleted-true

Dev environment:
SQL Server 2008 R2 Integration Services (32-bit)
Microsoft OLE DB Provider for Visual FoxPro
C:\Program Files\Common Files\System\Ole DB\VfpOleDB.dll 9.0.0.5815

Bug:
When I create an SSIS package with “OLE DB Source” using “Microsoft OLE DB Provider for Visual FoxPro”, and for “Data access mode” use “Table or view” deleted records are included even if DELETED=True (means IGNORE DELETED=True).

Workaround:
For “Data access mode” use “SQL Command”, i.e. “select * from myTable”. Deleted records will be excluded.

DECLARE @statement NVARCHAR(MAX);
SET @statement = N'
CREATE FUNCTION [dbo].[SplitWordToLetters]
(
      @StringToSplit nvarchar(2000)
)
RETURNS @RtnValue table
(
      Id int identity(1,1),
      Letter NCHAR(1)
)
AS
BEGIN
      Declare @substring nvarchar(2000)
      Declare @letter NCHAR(1)

      Set @substring = @StringToSplit 

      While (LEN(@substring) > 0)
      Begin
            SELECT @letter = LEFT(@substring, 1), @substring = SUBSTRING(@substring, 2, 255)

            Insert into @RtnValue (Letter) values (@letter)
      End

      Return
END '

IF OBJECT_ID(N'[dbo].[SplitWordToLetters]', N'TF') IS NULL
BEGIN
      EXECUTE (@statement);
END
ELSE
BEGIN
      SET @statement = REPLACE(@Statement, 'CREATE FUNCTION', 'ALTER FUNCTION');
      EXECUTE (@statement);
END

Usage:

SELECT *
FROM dbo.SplitWordToLetters ('abc')

/* Result:
Id          Letter
----------- ------
1           a
2           b
3           c

(3 row(s) affected)
*/

If you are already running SQL Server and don’t have too many needs, you can create a dashboard using SQL Server Reporting Services R2.

Advantages:

• No additional cost.
• Able to visualize data in a table: Indicators, sparklines, data bars and gauges can be used as part of a table.
• Able to drill-through to existing reports
• Can access KPIs from SSAS cubes
• Many maps, gauges and charts
• Able to embed reports/dashboard within a web page or SharePoint

With some creativity:

• Example of custom Gantt Chart: http://blog.summitcloud.com/2009/11/ssrs-2008-gantt-charts/
• Example of custom Heat Map:
  Thomas Williams: SSRS Heat Maps,
  Teo Lachev: Heat Maps as Reports
• Example of using Visio and SSRS Map Reports for Store Layouts http://blog.oraylis.de/2010/06/using-visio-and-ssrs-map-reports-for-store-layouts-etc/

Disadvantages:

• Unable to dynamically set visibility of parameters (in SSRS, can do it in custom app)
• Unable to move the parameters from the top header to right or left (in SSRS, can do it in custom app)
• Unable to rename Excel sheets on export
• Unable to create dynamic alerts but could schedule Standard or Data-Driven subscription as a workaround that would run on a regular basis.
• And more: http://www.simple-talk.com/sql/reporting-services/ten-common-sql-server-reporting-services-challenges-and-solutions/

Visual Communication
Data visualization is a way to present an audience with easier to read data and communicate data visually. Based on my experience, these are some of the elements users like:

• Keep things simple – simple lines, well organized and easy to view data.
• Create summarized data sections that allow users to drill through (to another report) and drill down (usually after clicking “+” sign).
• Choose the types of graphs and performance indicators that make sense and are correct for the situation. Use correct labels. Show numbers in a context.
• Don’t use too many intense colours, shading and gradient filling as it takes away from data readability. Tone down the colours or use shades of the colour.
• Shading and gradient filling might confuse the reader if the shading and gradient filling does not mean anything, and it is done to simply make the report more “exciting”.
• Keep colours and pictures across the report or reporting solution consistent (i.e. green indicator is always good, red is bad, yellow is neutral).
• Export reports to Excel, Word, PDF, etc. exactly as the user requested.

Most of the reports I create contain financial data, and I prefer if the visual representation of that data still looks like a financial report not as a computer game. But, as with everything, it’s important to meet the needs of the audience. Best reports are reports created specifically for a user, department, or company in mind while allowing that user, department, or company give us feedback.

Dashboards vs. Reports
I have been looking at options to create effective dashboards. Dashboards are different from reports in a way that reports answer specific question your audience asked while dashboard is a single screen that is used to “monitor” what’s going on in some aspect of the business and deals with situation awareness. There are many books out there related to building dashboards. The book that resonates with me is Information Dashboard Design. The Effective Visual Communication of Data by Stephen Few. His visualizations just make sense and seem so simple: http://www.perceptualedge.com/. Takes his Graph Design I.Q. Test and see how you’ll do.

Dashboard Software Evaluation Criteria:

1. Can the dashboard tool drill through to existing SSRS Reports (reusability)?
2. Can the dashboard tool access SSAS KPIs?
3. Does the dashboard tool have (a) its own server, or (b) does it need to be integrated with a web page or SharePoint?
4. Can I use Active Directory Trusted Security or does it have its own authentication system?
5. Would I have access to any of these out-of-the-box controls:
   • Strategy Maps
   • Calendar Component
   • Bullet Graph
   • Tree Map
   • Gantt Chart
   • Heat Map
   • Table Inline Charts (sparklines, indicators, bar chart)
   • Decomposition Tree
6. What are the options for (a) value filters (dropdowns) , (b)additional tabs and (c) drill troughs?
7. Ability to create alerts (a) visually on screen, (b) send email or SMS, (c) or trigger some other action.
8. Initial price and annual support maintenance cost?
9. Could I use it for mobile BI? Would it run on iPhone or iPad?

Dashboard software packages I’m in the process of reviewing:

1. Microsoft SQL Server Reporting Services 2008 R2
2. PerformancePoint in Microsoft Office SharePoint Server 2010 (former ProClarity) Getting started with business intelligence in SharePoint Server 2010
3. Dundas Dashboard www.dundas.com
4. Tableau Software www.tableausoftware.com
5. SAS Software www.sas.com
6. Xcelsius – SAP Crystal Presentation Design Free Trial Download Page
7. Microsoft Office Excel, PowerPivot & Excel Services (SharePoint)

Some of the other BI Tools worth mentioning are MicroStrategy (has mobile BI that will run on iPad), Information Builders, Oracle and Hyperion, SAP Business Objects, IBM Cognos, and Pentaho.

For more BI Tools, see these two article:

• ComputerWorld: The state of BI tools
• ComputerWorld: Self-service BI catches on

DECLARE @Temp TABLE (PrimaryKey INT, ParentKey INT)

INSERT INTO @Temp (PrimaryKey, ParentKey)
SELECT 1, 0
UNION SELECT 2, 1
UNION SELECT 3, 2
UNION SELECT 4, 3;  

WITH TopParent(PrimaryKey, ParentLevel, ParentKey) AS
(
       SELECT PrimaryKey, 0, ParentKey
       FROM @Temp
       UNION ALL
       SELECT Temp.PrimaryKey, TP.ParentLevel + 1, TP.ParentKey
       FROM @Temp Temp
       INNER JOIN TopParent TP ON TP.PrimaryKey = Temp.ParentKey
)
SELECT Tbl.PrimaryKey, Tbl.MaxLevel, TopParent.ParentKey
FROM TopParent
INNER JOIN (
	SELECT TopParent.PrimaryKey,
		   MAX(TopParent.ParentLevel) AS MaxLevel
	FROM TopParent
	GROUP BY TopParent.PrimaryKey
) Tbl ON Tbl.PrimaryKey = TopParent.PrimaryKey
	AND Tbl.MaxLevel = TopParent.ParentLevel;

/*
PrimaryKey  MaxLevel    ParentKey
----------- ----------- -----------
1           0           0
2           1           0
3           2           0
4           3           0

(4 row(s) affected)
*/

SELECT
       MyTable.myColumn.value('local-name(.)', 'VARCHAR(100)') AS ColumnHeader,
       MyTable.myColumn.value('.', 'VARCHAR(MAX)') AS Value
FROM (
       SELECT (
       SELECT *
       FROM Production.Product
       WHERE ProductID = 1
       FOR XML RAW, TYPE) AS XmlRow
)T
CROSS APPLY T.XmlRow.nodes('row/@*') AS MyTable(myColumn)

I used this idea when creating a Generic Drillthrough Report for SSRS, my query for dataset looked like this:

-- the SSRS report has the following parameters
DECLARE @SchemaDotTableOrViewName VARCHAR(200), @PrimaryKeyColumnName VARCHAR(200), @PrimaryKeyValue VARCHAR(20)
SELECT @SchemaDotTableOrViewName = 'Production.Product',
	@PrimaryKeyColumnName = 'ProductID',
	@PrimaryKeyValue = 1

-- dataset query
DECLARE @statement NVARCHAR(MAX)
SET @statement =
N'
SELECT
	MyTable.myColumn.value(''local-name(.)'', ''VARCHAR(100)'') AS ColumnHeader,
	MyTable.myColumn.value(''.'', ''VARCHAR(MAX)'') AS Value
FROM (
	SELECT (
		SELECT *
		FROM ' + @SchemaDotTableOrViewName + '
		WHERE ' + @PrimaryKeyColumnName + ' = ' + @PrimaryKeyValue + '
		FOR XML RAW, TYPE
	) AS XmlRow
) T
CROSS APPLY T.XmlRow.nodes(''row/@*'') AS MyTable(myColumn)
'

EXECUTE dbo.sp_executesql @statement

Handy shortcuts for SQL 2008:
Display multi-valued parameters:
- Join(Parameters!Product.Label, vbcrfl) for new line
- Join(Parameters!Product.Label, “,”)
Format Date:
- FormatDateTime(Parameters!PaymentStartDate.Value, DateFormat.ShortDate)
- “yyyy-MM-dd HH:mm:ss”

Force report repeat table header on each page:

	
	  
		
		  After
		  true
		  true
		
		
		  
		
	  
	

For multi-valued params, determine if user selected “(Select All)”:
The report should have 1 dataset CountriesDataset and 2 parameters, for example:
- @Countries will be VISIBLE multi-valued parameter and allow users to select countries they’d like to query
- @AllCountries will be HIDDEN and “available” as well as “default” parameters will be set to our CountriesDataset
You pass both params to the stored procedure, in the stored proc, and with a help of your Split funciton, do a check as follows

DECLARE @AllCountriesSelected
SET @AllCountriesSelected =
	(CASE WHEN (SELECT COUNT([Data]) FROM dbo.Split(@Countries, ',')
			= (SELECT COUNT([Data]) FROM dbo.Split(@AllCountries, ',')
		 THEN 1
		 ELSE 0
	END);

-- the query will be something like this
SELECT *
FROM SomeTable
WHERE (
	@AllCountriesSelected = 1
	OR SomeTable.Country IN (SELECT COUNT([Data]) FROM dbo.Split(@Countries, ',')
	)

Function CHGetFirstOfMonth ( ByVal Year As Integer, ByVal Month As Integer) As Date

                Dim firstOfMonth as Date
                firstOfMonth = DateValue(Year.ToString() + "-" + Month.ToString()+ "-" + "01")
                Return firstOfMonth
End Function

Function CHGetLastOfMonth ( ByVal Year As Integer, ByVal Month As Integer) As Date

                Dim firstOfMonth as Date
                Dim lastOfMonth as Date

                firstOfMonth = DateValue(Year.ToString() + "-" + Month.ToString()+ "-" + "01")
                lastOfMonth = DateAdd("D", -1, DateAdd("m", 1, firstOfMonth))

                Return lastOfMonth
End Function

-- Create Date
declare @year int, @month int, @day int
select @year = 2009, @month= 3, @day = 3;
select dateadd(mm,(@year-1900)* 12 + @month - 1,0) + (@day -1) [Date]

-- Get Last Name out of Full Name string
DECLARE @FullName VARCHAR(30), @LastName as VARCHAR(30)
SET @FullName = 'Smith,John';
SELECT LastName = SUBSTRING(@FullName, 0, CHARINDEX(',', @FullName, 0))