Archive for the ‘SQL Server 2008 R2’ Category.
November 9, 2011, 11:03 pm
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.
November 9, 2011, 10:46 pm
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.
November 9, 2011, 9:49 pm
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.
February 2, 2011, 12:32 pm
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:
Disadvantages:
February 1, 2011, 10:00 am
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:
- Can the dashboard tool drill through to existing SSRS Reports (reusability)?
- Can the dashboard tool access SSAS KPIs?
- Does the dashboard tool have (a) its own server, or (b) does it need to be integrated with a web page or SharePoint?
- Can I use Active Directory Trusted Security or does it have its own authentication system?
- 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
- What are the options for (a) value filters (dropdowns) , (b)additional tabs and (c) drill troughs?
- Ability to create alerts (a) visually on screen, (b) send email or SMS, (c) or trigger some other action.
- Initial price and annual support maintenance cost?
- Could I use it for mobile BI? Would it run on iPhone or iPad?
Dashboard software packages I’m in the process of reviewing:
- Microsoft SQL Server Reporting Services 2008 R2
- PerformancePoint in Microsoft Office SharePoint Server 2010 (former ProClarity) Getting started with business intelligence in SharePoint Server 2010
- Dundas Dashboard www.dundas.com
- Tableau Software www.tableausoftware.com
- SAS Software www.sas.com
- Xcelsius – SAP Crystal Presentation Design Free Trial Download Page
- 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:
