Tuesday, October 13, 2015

PolyBase and Views

PolyBase and Views

Views can be created against PolyBase external tables, using T-SQL. Unfortunately, an indexed view is not supported. Once you include the option "with SCHEMABINDING"  in your Create View, you  get an error:

         Msg 46518, Level 16, State 9, Procedure PolyBaseTableView, Line 115
         The option 'SCHEMABINDING' is not supported with external tables.


I expect there will be situations where being able to create an indexed view against an external PolyBase table would be ideal. Conversely, the potential size of datasets loaded into Hadoop or Azure could be so large, that the process of creating a local index on such a large dataset could exceed the capacity of the database server.  Still, I wish we had the option - and let the DBA make the determination.

Monday, October 5, 2015

Microsoft Overhauls Reporting Services – finally!

Microsoft Overhauls Reporting Services – finally!

Reporting Services has been one of the long disappointments with SQL Server. When it was first introduced back in 2003, it provided a much needed reporting platform to go along with SQL Server. But after the updated release as part of SQL Server 2005, the Reporting Services platform has languished.  Stuck in a world that still used an old version of Internet Explorer. I remember all the hacks I had to do just to get Reporting Services to work with a browser other than IE. It was ugly.

With the latest community technology preview of SQL Server 2016, Microsoft has radically overhauled Reporting Services. Support for HTML5 - check. Support for mobile reports - check, using the nicely done Power BI mobile app - no need to create a second. Support for Power BI Desktop .pbix reports - promised. According to the SSRS Blog roadmap, and the Power BI ideas forum, SSRS will gain the ability to ability to render Power BI Desktop reports. That would be great.

The Reporting Services stand-alone development tool has also undergone a face lift. It now has the look and feel of Power BI 2.0. Allowing you to directly publish to a reporting server or just create a dataset to be used by the new Mobile Report Builder.  Visual Studio not required. Still, the revised SSRS Report Builder is much like the version released years ago. (Robert Sheldon's write-up of how to create a basic report in the previous Report Builder 3.0  is still useful ) But, the most significant change is the output. SSRS 2016 reports support HTML5, allowing support across all modern browsers.

Printing with reporting services was another drawback. The report viewer toolbar required ActiveX. It has finally been ditched! And now supports PDF.

So, the updated stand-alone report builder, clearly targeted at analysts and power users, allows staff to create reports, and then publish them to a report server.  All, without the need for an IT developer. And HTML5 support allows those reports to be viewed by just about any modern browser. You can find the current listing of enhancements to Reporting Services at the book online site:  What's New in Reporting Services.

Links:



New, stand-alone Reporting Services Report Builder, CTP 2.4




Friday, October 2, 2015

PolyBase – error connecting to Hadoop file

PolyBase – error connecting to Hadoop file

At least with the current SQL Server 2016 CTP 2.3, PolyBase errors out trying to connect to certain text files in Hortonworks Hadoop. You typically get an error message similar to the following:

Update - June 14, 2016:  MSFT published a Polybase focused white paper for the Azure Data Warehouse, which supports Polybase.  This out lined - at least for Azure DW, the file types supported by Polybase.  For Azure DW, Polybase only supports UTF-8 files. This could explain some of the failed load issues experienced.  


Msg 107090, Level 16, State 1, Line 79
Query aborted-- the maximum reject threshold (0 rows) was reached while reading from an external source: 1 rows rejected out of total 1 rows processed.
OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Unspecified error".
Msg 7421, Level 16, State 2, Line 79
Cannot fetch the rowset from OLE DB provider "SQLNCLI11" for linked server "(null)". 

I’ve investigated what might be causing these errors, trying to discover if there are certain traits that cause the error.



Could not find or load main class path.....

Dennes found on Hortonworks a possible solution for the following error.

         
      Error: Could not find or load main class org.apache.hadoop.mapreduce.v2.app.MRAppMaster  
 

This may be beneficial for other issues, such as Could not obtain block.
       
Answer by Montrial Harrell · Jun 11 at 04:19 AM
Got it!! I added the below property to the mapred-site.xml file and the query ran successfully.

<property> <name>mapreduce.app-submission.cross-platform</name> <value>True</value> </property>   
 

(this was provided by Montrial Harrell at the Yarn ClassPath Value for Polybase Pushdown - Hortonworks Community Connection    -- review the entire post, as there may be other suggestons that might be of assistance.)

...cannot be cast...

If you're error message includes the phrase:  "...cannot be cast..." see page PolyBase wants a strongly typed ORC Hive Table.

Could not obtain block:

Dennes found that the error message Could not obtain block:  "...usually means the port 50010 (data node port) is not responding to SQL Server."  See also adjustments noted above for "Could not find or load main class"

EOF does not match expected format:

From my investigation, if EOF (end of file) has a different format from that declared in the CREATE EXTERNAL TABLE command, you will get an error. If, for example, the file has a trailing line with row count, or other meta data.

Data Type Mismatch

If the declared data type does not match with the incoming data type, you’ll get an error. In setting up a new table, it might be useful to first declare all columns as varchar or nvarchar to minimize any potential errors.  Once you establish a working connection, create a new table with the correctly typed columns. Loading dates into PolyBase has a very specific protocol, and not all date formats are supported. 

Data Size insufficient

If the size of the declared data type is not sufficiently large for incoming data type, you’ll get an error. In setting up a new table, it might be useful to first declare all columns as long varchar or nvarchar to minimize any potential errors. An example might be where you declare the column to be varchar(10), but the maximum size found exceeds 10.  No auto-truncate.

Line Delimiter

At least so far, I’ve been able to connect to files with the three ‘standard’ approaches to establishing the EOL: 
1)  CR/LF   0D 0A     (DOS/Windows)
2)  LF                 0A    (UNIX)
3)  CR        0D           (Mac)

File Format

The UTF-8 format seems to be the most common format, PolyBase works with the UTF-8 format. I have had trouble with files in the older ANSI/ASCII format.  My single attempt with UTF-16 did not work, but that may not be due to the UTF-16 format.  As noted above, Azure Polybase only supports UTF-8.

Other – still to be discovered problems


Several Hadoop files I have are not connecting to PolyBase, and I have not resolved what the problem. As I get them resolved, I’ll add those discoveries here.

Tuesday, September 29, 2015

Power BI Desktop - upgrade

On September 23, 2015, Microsoft released the awaited upgrade to the Power BI Desktop tool.
With the December 21, 2015 update, the added a Beta connector to R.  You can find out more at Visualizing and operationalizing R data in Power BI.

What is amazing, is that there is a very long list of enhancements to the desktop tool. And, especially so, since Desktop is a free tool. Has Microsoft lost it? Or perhaps have they finally decided to once again try to take the lead in BI.

A hidden gem is support to connect to an on-premises SSAS Multi-dimensional database. In earlier versions, only Tabular was supported. And the Navigator connection tool only needs to know the name of the server and the authentication user/pw.


Power BI 2.0 desktop September, 2015 update to read about the upgrade in detail.
Power BI 2.0 desktop December 21, 2015 update to read about the upgrade in detail.


 Power BI Get Data Navigator listing the SSAS Multi-dimensional databases

 

Power BI Relationships Tab - displaying the imported Multi-dimensional table

 


Power BI Waterfall displaying the imported Multi-dimensional table






Thursday, September 24, 2015

Power BI Updates for September 22, 2015

The most recent Power BI weekly updates ( Sept 22, 2015) added some much needed improvements to the available list of tile sizes. Until this release, there were only a small number of sizes available.

While the introduction of Power BI 2.0 has been well received, the limited tile sizing was a presentation drawback, This release greatly expands tile sizing flexibility that will give report designers a greater ability to deliver the reports being requested.

In this weeks release, it was also suggest that an update to Power BI Desktop will be release very soon.  For more details go to the Power BI Weekly Service Update report.

Friday, September 18, 2015

Power BI: Comparing the Waterfall, Line and Column Charts

The Waterfall visualization (often called a progressive bar chart) combines a column chart with a line chart. In effect, it lays out the incremental data as individual columns that add incrementally to the total. Visually, each record provides you with a proportionately sized column that is added to the total over time. It is a great visual for showing unit and sales growth.

For our example below, we used the monthly gross profit numbers for the Adventure Works sample database. Using a waterfall chart, the value for each month shows up like a step, sized according to the impact it has on the total. Showing sales, gross profit, or any other incremental item allows you to see not only the overall trend, but also the individual contribution by month. It is a really nice way to display these types of trends.

These three graphs display the same data to compare the Waterfall Chart with the Column Chart and the Line Chart.



Waterfall Chart

Line Chart

Column Chart



You can visit the Power BI tutorial at support.powerbi.com.













Data type Changes when loading Power BI Data

During the data loading process, Power BI Desktop evaluates the incoming data, and then assigns what it thinks is the correct data type. And in most cases, it makes a good choice. But what if it makes the wrong choice? 

For example, what if one of the columns is a text field made up of the month and the year? Such as December, 2015. Power BI Desktop decides that this is a data field, and changes the value to 12/1/2015. Great if that is what you want.

Editing the data types


During the initial load process, one of the first screens will be the Navigator. On the bottom right, select/click on the Edit button.




This will bring up the Query Editor. The Query Editor is one of the most powerful tools available to the Power BI Desktop, Over time, you'll find this tool more and more useful. On the right side of the Query Editor, locate the Applied Steps section. For this example, the last step the query editor performed was the Change Type.



Simply right click on the Change Type item, and select Delete. Or, simply select/click on the X immediately to the left of the Change Type item. Once the change has been made, the Sales Month column is now back to the value we wanted.




While we are in the Query Editor, lets update the data type for the monetary columns. Holding the Ctrl button, select/click on the monetary columns. On the Data Type combo box, select the Fixed Decimal Number value. 



For our example, the incoming column in Excel was configured as Currency. Once we change the Data Type to Fixed Decimal Number, Power BI recognizes the columns as Currency.


  
When you are finished, select/click the Close & Load button. After loading the data, you can review using the Data Tools. The currency columns are now presented as currency values.