Uncompress RAR Files in Windows 8

You have a RAR file to Uncompress and run Windows 8 ?  You could try this Windows 8 app from the Windows Store http://apps.microsoft.com/windows/en-us/app/uncompress/47925c5f-c967-468f-8b5f-b68f48f83236.  Simply download the app, open Uncompress, locate your RAR file, select a Destination folder, click OK, don’t cross the streams and all is good !

Diagram to Reverse Engineer a SQL Query

Toad for SQL Server has a nice feature to allow you to create a visual diagram of a SQL Server query – ok so whats in it for me you ask ?

Lets say you have been given a dirty big query to troubleshoot – now you need to trawl through the query and pull out the tables, the joins and the filters specified in the where clauses.     Formatting the query in SQL Server Management Studio using colour context can help, but a picture is a thousand reserved words.

Diagramming the query can be very handy to help you understand the query faster and provide a level of documentation which probably doesn’t exist.   So whether you are dealing with lots of complex queries, or reverse engineering the backend of an existing production business intelligence solution, this could be a useful tool for you.

http://www.toadworld.com/Blogs/tabid/67/EntryId/434/Reverse-Engineer-a-SQL-Query.aspx

Note the freeware edition http://www.toadworld.com/Downloads/ToadforSQLServerFreeware/tabid/562/Default.aspx, has the “Send to Query Builder” smoked out so you will need a full license for this functionality.

A license for a full version can be purchased here https://shop.quest.com/682/purl-toad-for-sql-server?x-AdCode=cbshopbtntoadforsqlserver but note it aint cheap.

If you want a cheaper option you could build your own tabular view of INFORMATION_SCHEMA.VIEW_TABLE_USAGE

http://www.mssqltips.com/sqlservertip/1638/using-sql-server-meta-data-to-list-tables-that-make-up-views/ but would require a bit of work to get some recursive logic happening for nested views.

SSRS – The Tablix includes a table header or column with Fixed Header set to true

This is one of those times when you are about to throw the waste paper basket rather than throw something in the waste paper basket.  You attempt to embed a tablix within a tablix and when you preview, get the error “The Tablix includes a table header or column with Fixed Header set to true”.

Check your row and column groups by clicking on the name of each group in the Row Groups and Column Groups sections in Report Designer or Report Builder.  Now check the properties, look for the Fixed Data property under the Other Property category.  If this is True, set it to False and retry your report.

TSQL – Calculate Is Current Fiscal Year and Is Previous Fiscal Year

The following sample shows how to calculate IsCurrent and IsPrevious Fiscal or Financial year indicators from your full date column in your date dimension.

CASE

WHEN MONTH(full_date) < 7 and YEAR(full_date) = YEAR(getdate()) THEN 'Yes'
 WHEN MONTH(full_date) > 6 and YEAR(full_date) = YEAR(getdate())-1 THEN 'Yes'
 ELSE 'No' END AS IsCurrentFinancialYear,

CASE
 WHEN MONTH(full_date) < 7 and YEAR(full_date) = YEAR(getdate())-1 THEN 'Yes'
 WHEN MONTH(full_date) > 6 and YEAR(full_date) = YEAR(getdate())-2 THEN 'Yes'
 ELSE 'No' END AS IsPreviousFinancialYear

SSRS – External Images not displaying

Remember for SSRS reports that refer to external images (i.e. images not stored in a database or stored in the report itself), you need to prefix any UNC path with “file:” or the HTTP path with “http:”.

e.g.

The full UNC path should be constructed like “file:\\server\share\path\filename.jpg”.

The full HTTP path should be constructed like “http:\\server\share\path\filename.jpg”

For UNC file paths be sure you have permissions for the SSRS service account and the Unattended account.   You may want to consider pulling the files down to your local report server if performance issues prevail when the report retrieves images.

Tales from Dr Carl – Uncloaking pesky hidden characters

In the post https://popbi.wordpress.com/2012/08/28/how-to-remove-carriage-return-line-feed-tab-from-a-string-in-tsql I discussed options for removing unprintable characters. In the code example below, Dr Carl demonstrates how to use the ASCII function to unclock those pesky characters likely to cause troubles in your BI solution.  The result of the ASCII function is the specific character code you can then reference with the char command to include in your removal procedure.  A complete list of string functions is referenced here http://msdn.microsoft.com/en-us/library/ms181984.aspx.


--Create SomeTable
CREATE TABLE dbo.SomeTable
(column1 varchar(10))
GO
--Insert a value
INSERT dbo.SomeTable
VALUES
('Value')
GO

--Insert a dubious value with a tab character at the end
INSERT dbo.SomeTable
VALUES
('Value' + CHAR(9))
GO

--Values look the same at first
SELECT Column1, LEN(column1) AS ColumnLength FROM dbo.SomeTable

--Unclock the dubious character
SELECT Column1, LEN(Column1) AS ColumnLength,
 ASCII(RIGHT(Column1,1)) as AsciiCharacter
 FROM dbo.SomeTable