Monday, May 8, 2017

Power BI Desktop to SSRS -

Back on November 10, 2016, we wrote about the coming support for Power BI Desktop reports by SSRS.  See Upload Power BI Desktop .pbix to SSRS 2016.  We'll a few days ago, on May 3, 2017, the SSRS Team Blog posted an announcement that it was almost ready.  Almost.

It's called Power BI Report Server, and as of May 8, 2017, it is still in the "...coming soon." status. The sort-of good news is that if you have an Enterprise Edition, per-core license, with Active Software Assurance (SA), it is included. Or if you have Power BI Premium licenses.  For all the rest of us, we'll not so much.  If you have looked over our earlier review, it seamlessly worked with SSRS's rebuild engine. So, we are hoping that this is still the case.

You can review the official Power BI Report Server introduction here: Introducing Power BI Report Server  , along with the SSRS Team Blog here:  Introducing Power BI Report Server for on-premises Power BI report publishing.

Getting to ISO format 'yyyyMMdd' with a single function?

The Goal:

Convert a date in SQL Server into the ISO format 'yyyyMMdd' with a single function, and without having to CAST a character value into an integer.  Alas, we have yet to find such an approach.

Before the FORMAT command, the most expedient approach was to use the CONVERT function, combined with a second convert function, or CAST the value to an integer value.  For example:

 declare @adate date = '2017-05-01';
 ISOdate_CONVERT = cast(convert(varchar(12), @adate,112) as int);


Starting with SQL Server 2012, we were introduced to the FORMAT statement. It had promise, but like the CONVERT statement, we still had to issue a second CAST statement. Output from FORMAT is nvarchar. Sure, we could skip the second CAST function, and just rely on the implicit conversion from nvarchar to integer, but that would be poor form.  SQL Server still has to make the conversion, and we have to believe that an implicit conversion is not only sloppy, but perhaps a bit slower. So, with FORMAT, we now have two options to convert a date, or datetime into an ISO formatted date - yyyyMMdd.

 declare @adate date = '2017-05-01';
 ISOdate_CONVERT = cast(convert(varchar(12), @adate,112) as int)
,ISOdate_FORMAT  = cast(format(@adate,'yyyyMMdd','en-US')as int); 

Which to use?

Since have not done any performance testing, either one would suffice. What we would all like is a method of converting a date directly into an ISO format, with a single function.

Sunday, April 23, 2017

Azure Analysis Services general availability - April 19, 2017

Microsoft announced the general availability of the Azure Analysis Services platform. But it only supports the inferior tabular model. With Azure's scalability, the ideal use of an Azure Analysis Services platform would be to create a multi-dimensional ROLAP model against an Azure SQL data warehouse.  Announcing Azure Analysis Services general availability.

Friday, March 24, 2017

Power BI - The New Matrix

Over at PowerPivotPro, Matt Allington has insight into the "preview' of the newly released New Matrix for Power BI Desktop. The New Matrix.

Test to see if your email account has been Breached

Use this site to see if your email has been breached:

SQL Server Agent added to Linux CTP 1.4

On March 17th, Microsoft announced the addition of SQL Server Agent to the latest CTP of SQL Server for Linux. While the Linux release is not a full function release, it does add the core part of job scheduling.  SQL Server next version CTP 1.4 now available.

You can get the specific details on installing SQL Server Agent on Linux, along with some sample T-SQL to create and manage jobs at:  SQL Server on Linux: Running jobs with SQL Server Agent.

Sunday, February 19, 2017

Row Count of all tables - returned in a single dataset

When you need the row count from a table, the most common way is to use count(*). But what if you need the row count for all the tables in your database?  You could use the sp_MSforeachtable stored procedure:

    EXEC sys.sp_MSforeachtable 'select ''?'',count(*) from ?;';

Nice, but the return format is a bit difficult to manage, since it effectively runs the query for as many times as you have tables in your database.  The output might look something like this:

          -------------------- -----------
          [dbo].[Product_info] 504

          --------------------- -----------
          [dbo].[database_info] 24

Sure, you could package this to run an insert into a temp table, but still, it's a bit awkward. What we want is a single, elegant query. One that returns a single dataset. Fortunately, we can get the information from the DMV sys.dm_db_partition_stats. One of the columns returned from this DMV is row_count. The one caveat is that row_count is approximate.

Our query will look something like this:

  select p.row_count, tableName = object_name(object_id), * 
   from sys.dm_db_partition_stats as p
inner join sys.sysobjects o 
      on p.object_id =  and o.xtype = N'U'

where p.index_id IN( 0,1) and p.row_count > 0
   order by p.row_count desc;

A table is either a heap, or it has a clustered index. Heaps have an index_id of 0, while clustered indexes have an index_id of 1. They are mutually exclusive.

You can read more about sys.dm_db_partition_stats on MSDN here: