Power Map is released (Office 2013 SP1)!

As of yesterday, Power Map was released as part of Office 2013 SP1.

Power Map for Excel - Office 2013 SP1



More info about the announcement:
Power Map for Excel Now Generally Available, Automatically Updated for Office 365

Announcing the release of Service Pack 1 for Office 2013 and SharePoint 2013





Important:
(thanks Chris Webb for the headsup!)

One important point to note here is that Power Map will only be available to Office 365 customers. If you have a standalone version of Excel, or have a regular (ie not Office 365) Professional Plus license, you will no longer be able to use Power Map. See:

http://office.microsoft.com/en-us/excel-help/power-map-for-excel-HA104204034.aspx?redir=0

Power Map for Excel New!

If you have a subscription for Microsoft Office 365 ProPlus, Office 365 Midsize Business, or for the Office 365 Enterprise E3 or E4 plans, you’ll have access to Power Map as part of the self-service business intelligence tools. To determine which subscription you have, see Office 365 ProPlus and Compare All Office 365 for Business Plans.

If you have Office 2013 Professional Plus or a standalone version of Excel 2013, you’ll be able to download and use the Power Map Preview for Excel 2013 until May 30, 2014. After that date, the preview will no longer work in any non-Office 365 subscription version of Excel.

To learn more about Power Map, see Get started with Power Map.



Join us at LinkedIn!
Don’t forget to join the ‘Microsoft Power BI‘ group on LinkedIn and stay updated!:
linkedin-microsoft-power-bi-group








OPENROWSET – The object has no columns or the current user does not have permissions on that object

Today I stumbled upon this little annoying issue.
If you are trying to run a stored procedure using OPENROWSET, for example:

SELECT *
FROM OPENROWSET('SQLNCLI','Server=(LOCAL);Trusted_Connection=Yes;Database=DB_Name','EXEC [DB_Name].dbo.SP_Name')

and you’ve received the following error:

Msg 50000, Level 16, State 1, Line 231
Cannot process the object [T-SQL statement]. The OLE DB provider "SQLNCLI10" for linked server [LinkedServerName] indicates that either the object has no columns or the current user does not have permissions on that object.

The thing is that I wasn’t able to solve it properly, just found a work-around thanks to Google:

By adding ‘SET FMTONLY OFF; SET NOCOUNT ON;‘ it will ignore the metadata as it seems, it will simply return the resultset without validating:

SELECT *
FROM OPENROWSET('SQLNCLI','Server=(LOCAL);Trusted_Connection=Yes;Database=DB_Name','SET FMTONLY OFF; SET NOCOUNT ON; EXEC [DB_Name].dbo.SP_Name')



More info about it:

The OLE DB provider “SQLNCLI10” for linked server indicates that either the object has no columns or the current user does not have permissions on that object.
When one of the columns has an invallid name this can occurs. This can be fixed by altering the sys.spt_columns_view by replacing the c.name column in quotename (c.name).

To avoid this error we need to check if our SP returns a result set that has two or more columns with the same name or does not have any column name. Next we need to set NOCOUNT ON at the beginning of the procedure (just after the begin statement). If these two things are correct then the statement will execute successfully.








From DATETIME(OFFSET) to VARCHAR(x), a world of differences :)

Always use the smallest data type, but in some cases this can really bite you.
In my example, I’ve both DATETIME and DATETIMEOFFSET types in a single table.
‘Simply’ converting/casting both to strings and expecting similar results, isn’t an option.

Instead of the ‘Jan 2 2012 9:15AM’, it would be nice to just have it like ‘2012-01-02 09:15:48’
But…you’ll have to do that yourself 🙂

datetime(offset)-to-(n)varchar-print

datetime(offset)-to-(n)varchar-select

Code

PRINT CAST('2012-01-02 09:15:48' AS datetime)
PRINT CAST('2012-01-02 09:15:48.0000000' AS datetimeoffset)
PRINT CAST('2012-01-02 09:15:48.7529001 +01:00' AS datetimeoffset)
PRINT ''
PRINT CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48' AS datetime),127)
PRINT CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48.0000000' AS datetimeoffset),127)
PRINT CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48.7529001 +01:00' AS datetimeoffset),127)

SELECT '1| -- '+CAST(CAST('2012-01-02 09:15:48' AS datetime) AS VARCHAR(100)) AS Results
UNION
SELECT '2| -- '+CAST(CAST('2012-01-02 09:15:48.0000000' AS datetimeoffset) AS VARCHAR(100))
UNION
SELECT '3| -- '+CAST(CAST('2012-01-02 09:15:48.7529001 +01:00' AS datetimeoffset) AS VARCHAR(100))
UNION
SELECT '4| -- '+CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48' AS datetime),127)
UNION
SELECT '5| -- '+CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48.0000000' AS datetimeoffset),127)
UNION
SELECT '6| -- '+CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48.7529001 +01:00' AS datetimeoffset),127)









SSRS: History in ExecutionLogStorage

By default, Reporting Server (SSRS) keeps report execution information in the execution log (‘[dbo].[ExecutionLogStorage]‘). After that, you guessed it…it will be deleted. In case you would like to increase the retention period on this table, there’s a server property named “ExecutionLogDaysKept” for Reporting Services. This can help you to increase the retention period, for more information:
http://technet.microsoft.com/en-us/library/bb934303%28v=sql.105%29.aspx

ExecutionLogDaysKept
The number of days to keep report execution information in the execution log. Valid values for this property include 0 through 2,147,483,647. If the value is 0 entries are not deleted from the Execution Log table. The default value is 60.


How to increase the number of days
You can modify the settings by connecting to your SSRS database and executing the SetConfigurationInfo stored procedure:
EXEC SetConfigurationInfo @Name=N'ExecutionLogDaysKept',@Value=N'90'

Or connect to your Reporting Services instance with SSMS and open the Server Properties of SSRS and go to ‘Logging’:

SSRS-server-properties-logging








Report Server Analytics with Power Pivot & Power View

Amit Banerjee blogged a very usefull article about how to analyze the performance and usage of your Reporting Server (SSRS) with Power Pivot and Power View:
http://troubleshootingsql.com/tag/executionlogstorage/

I recently needed to query report execution statistics for a SQL Server Reporting Services instance where the number of rows were quite large. So I decided to use Excel 2013 and PowerPivot to analyze the data. This would allow me to directly pull all of the data into an Excel sheet and then perform analysis on the extracted data without having to query the Report Server database repeatedly.

Leverage PowerView’s interactive capabilities to view the Report execution statistics, the report processed status (failure or success) and the processing, rendering and data retrieval times for each report.


report-server-execution-statistics-power-view
More info: http://troubleshootingsql.com/tag/executionlogstorage/