Friday, March 30, 2007
SSW Rules to Better SQL Reporting Services 2005
Rules to Better SQL Reporting Services
- Do you know the 4 user experiences of Reporting Services: Vanilla, Website, Email, Windows?
- Do you know when to use Reporting Services?
- Do you check that "RS Configuration Manager" is all green ticks?
- Do you check out the built-in samples?
- Do you know your 2 migration options to show your Access reports on the web?
- Layout - Does your report print and display on the web correctly?
- Layout - Do you include a useful footer at the bottom of your reports?
- Layout - Do you avoid using word 'Report' in your reports?
- Layout - Do you underline items with Hyperlink Action?
- Layout - Do you show errors in Red?
- Layout - Do you have consistent report name?
- Data Layout - Do you show the past 6 months of totals in a chart?
- Data Layout - Do you show data and chart in one?
- Data Layout - Do you avoid using a single chart when you need it to be scaled?
- Data Layout - Do you use expressions to show the correct scale on charts?
- Data Layout - Do you show change in your reports?
- Data Layout - Do you avoid showing change as a percentage?
- Data Layout - Do you use alternating row colors?
- Data Layout - Do you have nodes count like Outlook?
- Data Layout - Do you avoid displaying decimal places?
- Data Layout - Do you have consistent height of table row across all your reports?
- Data Layout - Do you display zero number as blank in your reports?
- Data Layout - Do you know the best way to show your data?
- Data Layout - Do you show time format clearly?
- Data Logic - Do you use de-normalized database fields for calculated values?
- Parameters - Do you avoid showing empty reports by at least setting Default parameters?
- Parameters - Do you avoid showing empty reports by the most intelligent default?
- Parameters - Do you use the DateTime data type for date parameters?
- Parameters - Do you have consistent parameter names?
- Performance - Do you cache popular reports for better performance?
- Performance - Do you schedule snapshots of slow reports for quicker access?
- Internationalization - Do you keep use regional friendly formatting?
- Internationalization - Do you make sure your language follows the user's regional settings?
- Internationalization - Do you make sure your language rule has an exception for Currency Fields?
- Admin - Do you validate all your reports?
- Admin - Do you create a separate virtual directory for Admin access?
- Admin - Do you take advantage of 'Integrated Security' to do Payroll reports?
- Admin - Do you remove ExecutionTime in Subject of Subscription email?
Parse delimited string in a Stored procedure
Parse delimited string in a Stored procedure
Sometimes we need to pass an array to the Stored Procrdure and split the array inside the stored proc. For example, lets say there is a datagrid displaying sales orders, each sales order associated with an orderid (PK in the Sales table). If the user needs to delete a bunch of sales orders ( say 10-15 etc)..it would be easier to concatenate all the orderid's into one string like 10-24-23-34-56-57-....etc and pass it to the sql server stored proc and inside the stored proc, split the string into individual ids and delete each sales order.
There can be plenty of other situations where passing a delimited string to the stored proc is faster than making n number of trips to the server.
CREATE PROCEDURE ParseArray (@Array VARCHAR(1000),@separator CHAR(1))
AS
BEGIN
SET NOCOUNT ON
-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
DECLARE @separator_position INT -- This is used to locate each separator character
DECLARE @array_value VARCHAR(1000) -- this holds each array value as it is returned
-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
SET @array = @array + @separator
-- Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @separator + '%', @array) <> 0
BEGIN
-- patindex matches the a pattern against a string
SELECT @separator_position = PATINDEX('%' + @separator + '%',@array)
SELECT @array_value = LEFT(@array, @separator_position - 1)
-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array
SELECT Array_Value = @array_value
-- This replaces what we just processed with and empty string
SELECT @array = STUFF(@array, 1, @separator_position, '')
END
SET NOCOUNT OFF
END
GO
David Francis Blog : PerformancePoint CTP1 - Planning Assignment doesn't render issue and solution from Microsoft
PerformancePoint CTP1 - Planning Assignment doesn't render issue and solution from Microsoft
Here is a useful bit of info from Mark Yang at Microsoft if you ever get the following issue in the CTP1 build of PerformancePoint Planning.
I had been working/playing with the assignment and workflow functionality of PerformancePoint Planning and had created a number of form templates.
I'd set up a basic assignment using a form to be accessed by only one user and approved by another. The assignment appeared in the list but when the user clicked on the assignment they got the following:
'Error retrieving latest timestamps from Analysis Server'
'Internal Error Message: System.Web.Services.Protocols.SoapException: Failed to retrieve timestamp from workflow data from table Assignments'
at the Caching Assignments stage and the form retrieval stage.
When I ran the scenario with SQL profiler on, it appeared to be running a stored proc called bsp_BizFormGetByFormId with the GUID of the very first form I ever created in the system (different name etc etc).
Apparently according to Mark this is a known issue with CTP1 and is to do with the storing of information about assignments in the Offline Cache.
So here is his solution:
Try manually deleting your assignment cache by deleting the files from \Documents and Setttings\<user_name>\Local Settings\Application Data\Microsoft\PerformancePoint\OfflineCache and restart your Excel Client.
I have to admit I had to actually reboot my server for it to work, but work it did.
Thanks again Mark.
I'd highly recommend if you aren't signed up to the PerformancePoint newsgroups (microsoft.beta.office.performancepoint.planning and microsoft.beta.office.performancepoint.monitoranalyze) that you do so.
SQLskills.com | Immerse Yourself In SQL Server
http://www.sqlskills.com/whitepapers.asp
SQL Server 2005 Whitepapers
ÞAdvantages of a 64-bit Environment
ÞBatch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
ÞConnectivity and SQL Server 2005 Integration Services (written by Bob Beauchemin of SQLskills.com)
ÞDatabase Administrator’s Guide to SQL Server Database Engine .NET CLR Environment (written by Kimberly L. Tripp of SQLskills.com)
ÞDatabase Engine Tuning Advisor (DTA) in SQL Server 2005
ÞDatabase Mirroring in SQL Server 2005
ÞDatabase Mirroring: Alerting on Database Mirroring Events
ÞDatabase Mirroring: Best Practices and Performance Considerations
ÞEnterprise Strategy Group Information Security Brief: SQL Server Runs the Security Table
ÞHow SQL Server 2005 Enables Service-Oriented Database Architectures
ÞImproving Performance with SQL Server 2005 Indexed Views
ÞOnline Indexing Operations in SQL Server 2005
ÞPartitioned Tables and Indexes in SQL Server 2005 (written by Kimberly L. Tripp of SQLskills.com)
ÞPerformance Tuning Using Waits and Queues
ÞPhysical Database Storage Design
ÞReporting Services: Planning for Scalability and Performance with Reporting Services
ÞScalability: Internals, Troubleshooting, and Best Practices for use of Scaleout Technologies in SQL Server 2005 (written by Bob Beauchemin of SQLskills.com)
ÞScalability: Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005 (written by Bob Beauchemin of SQLskills.com)
ÞScalability: Solutions for Highly Scalable Database Applications: An analysis of architectures and technologies (includes details/comparisons with Oracle RAC)
ÞSQL Server 2005 Failover Clustering White Paper
ÞSQL Server 2005 for Oracle Professionals
ÞSQL Server 2005 Management Tools Overview (written by Kimberly L. Tripp of SQLskills.com)
ÞSQL Server 2005 Security Best Practices - Operational and Administrative Tasks (written by Bob Beauchemin of SQLskills.com)
ÞSQL Server 2005 Security Overview for Database Administrators
ÞSQL Server 2005 Snapshot Isolation (written by Kimberly L. Tripp of SQLskills.com)
ÞSQL Server I/O Basics
ÞSQL Server I/O Basics, Chapter 2
ÞStatistics Used by the Query Optimizer in Microsoft SQL Server 2005
ÞStrategies for Partitioning Relational Data Warehouses in Microsoft SQL Server
ÞTroubleshooting Performance Problems in SQL Server 2005
ÞWorking with Tempdb in SQL Server 2005
SQL Server 2000 Whitepapers
ÞDatabase Architecture: The Storage Engine
ÞImproving Performance with Microsoft SQL Server 2000 Indexed Views
ÞIndex Tuning Wizard for Microsoft SQL Server 2000
ÞMicrosoft SQL Server 2000 Index Defragmentation Best Practices
ÞQuery Recompilation in SQL Server 2000
ÞSQL Server 2000 Incremental Bulk Load Case Study
ÞStatistics Used by the Query Optimizer in Microsoft SQL Server 2000
ÞUsing Partitions in a Microsoft SQL Server 2000 Data Warehouse
Group links, blogs links to links and other useful links to links of links
ÞWhitepapers on Microsoft.com (many NOT Listed above)
ÞWhitepapers on MSDN - the Developer Center Whitepapers (many NOT Listed above)
ÞWhitepapers on TechNet - SQL Server 2005 Technologies (many NOT Listed above)
Þ"Previous Versions" Page - Information, links and resources related to previous versions of SQL Server (many NOT Listed above)
Important Note
ALL of these links were valid on March 26, 2007 and may no longer be valid. If companies (who shall remain nameless :) would stop breaking links ;), it would make our lives easier (well, at least for finding content :) BUT we're always going to have this issue as it's just the general nature of the web! So, I'm sorry if you hit a link and don't get where you want to go. Use the whitepaper title to search for it and then be sure to let us know if you find it. Regardless, please let us know that the link is broken and we'll try to find the current link for you as well.
Also, if you find a whitepaper not listed (or linked to) OR you think there's a whitepaper that should specifically be called out on this list, please let us know!
You can reach us at info@SQLskills.com and for specific questions: questions@SQLskills.com. Thanks!
March 29, 2007: Added a few more links, fixed a few broken ones (one via a cut/paste error and the other was one I missed). All of this was thanks to comments on my whitepapers blog post here.
Friday, March 23, 2007
Monday, March 19, 2007
42 Design/Tech Magazines To Read | Smashing Magazine
Links to various design guides for web masters.
Friday, March 09, 2007
SqlBI.eu
Distinct is a partially blocking component that remove duplicates from one flow. Its main advantages against the sort component provided in SSIS are:
- Memory usage: Distinct does not cache the whole flow (as Sort does) but retains in memory only the distincts, consuming less memory then Sort
- Distinct is partially blocking where Sort is fully blocking
- Distinct is freeware, you can easily download sources and adapt it to your needs
Distinct sources can be easily download from the download section at www.sqlbi.eu.
MdxScriptUpdater is a simple C# class that simplifies updating MDX Scripts into a cube in a production environment. MdxScriptUpdater is provided in form of a sample source code as is.
There are a lot of scenarios where nightly batches would update parts of the MDX Script of a cube. For example, I had a customer with a calculated member for each year with data. We can define the calculated member by hand, but we would need to remember to create a new one each year. Another case is the customer that wants to consolidate his own calculated members, without requiring a new cube deployment.
This is the introduction of a paper that describes how to leverage the many-to-many dimension relationships, a feature that debuted available with Analysis Services 2005. After introducing the main concepts, the paper discusses various implementation techniques in the form of design patterns: for each model, there is a description of a business scenario that could benefit from the model, followed by an explanation of its implementation.
Two separate downloads (available on SQLBI.EU project page) contain the full paper in PDF format and SQL Server database and Analysis Services projects with the same sample data used in the paper.
SqlBulkTool is a command line utility that is used to quickly create a mirror of a database. It reads its configuration from an XML file containing source and destination command strings and a list of all the tables to mirror and then handles the work of copying the database in an automated and highly parallelized way.
The parallelism can use the partition capabilities of SQL Server 2005: to handle a huge table it is enough to partition it to make the tool load it by running each single partition in a separate thread, dramatically increasing table load time. In the case where no partitioning is defined the parallelism is handled at the table level.
DtsToSsis-Prepare is a command line tool that prepares a DTS package for a better migration to an SSIS package.
This article describes the needs for this tool and how to use it.
The project is freeware, full source code is available. Please register on www.sqlbi.eu site if you want to receive mail notifications when bug fixes and new releases will be available.
Source: SqlBI.eu
ExoLogic
CubePort™:
CubePort is helping change the BI landscape. It is a product that feasibly permits BI migration, real ROI against software licensing, and offers dramatically improved cube performance. Open up the world of "BI Standardization" possibilities.
The product enables, in a practical way, either migration or replication from Hyperion Essbase to Microsoft SQL Server Analysis Services.
Source: ExoLogic
BI Blogs
Book List
- Teo Lachev: Applied Microsoft Analysis Services 2005
- Mark Whitehorn, Robert Zare, Mosha Pasumansky: Fast Track to MDX
- Spofford, Harinath, Webb, Huang, Civardi: MDX Solutions: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase
- Reed Jacobson: Microsoft SQL Server 2000 Analysis Services Step by Step
- Edward Melomed, Irina Gorbach, Alexander Berger, Py Bateman: Microsoft SQL Server 2005 Analysis Services
- Reed Jacobson, Stacia Misner: Microsoft SQL Server 2005 Analysis Services Step by Step
- Graves, Scott, Benkovich, Turley, Skoglund, Dewson, Youness, Lee, Ferguson, Bain, Joubert: Professional SQL Server 2000 Data Warehousing with Analysis Services
- Sivakumar Harinath and Stephen R. Quinn: Professional SQL Server Analysis Services 2005 with MDX
- Mike Gunderloy, Tim Sneath: SQL Server Developer’s Guide to OLAP with Analysis Service
- Joy Mundy, Warren Thornthwaite, Ralph Kimbal: The Microsoft Data Warehouse Toolkit: With SQL Server 2005 and the Microsoft Business Intelligence Toolset
Source: BI Blogs
Monday, March 05, 2007
James Goulding Research & Ebooks
This page consists of research files, related to the financial markets. Recently, I completely rebuilt the database on this page. I will be adding data over February and March of 2007. All links are new and all documents have been renamed. Also, some files will be moved to a new page called "Research-Historical Data". That page will be added at a later date.
Source: Research
Also...
This page is an online library of ebooks. All books are in the public domain. Therefore, no copyright laws are being violated. Feel free to download any book and save it to your hard drive
http://www.jamesgoulding.com/ebooks.html
Plus lots of info on Cycles, Calendars, Day Trading, Bonds.
Subscribe to:
Posts (Atom)
