Category: Reporting Services

SharePoint Server 2016 IT Preview from a BI point of view

Jorg KleinLeave a comment

SharePoint Online and Power BI have a lot of attention and focus lately, but what about the on-premises version of SharePoint that’s used in almost all serious BI solutions today? Well SharePoint Server 2016 IT Preview has just been released by Microsoft, by many stated as the last on-premises version of SharePoint ever. In this blog post I will focus on the BI features of SharePoint Server 2016.

So what kind of BI features are improved and what’s new? It’s better start with the question: which BI features will remain available in SharePoint 2016, because a lot of BI features will be deprecated or removed.

An overview of the main SharePoint BI capabilities and their availability in SharePoint 2016:

· Excel Services

Excel Services will no longer be available, it will completely move to Excel Online in Office Online.

· PowerPivot for SharePoint and Power View add-ins

These BI features are not available in the current release of the SharePoint 2016 IT Preview. Microsoft states they will implement them later this year, but when and in what form is uncertain.

· Reporting Services Integrated Mode

There is no news about Reporting Services Integrated Mode, but as I wrote in my last blog post, Reporting Services gets a big update in SQL Server 2016. One of the new possibilities will be the integration of Reporting Services and Power BI. So why still integrate with SharePoint?

With the release of the new Power BI Desktop (former Power BI designer) a lot of Power Pivot functionality move to Power BI. For example, you can now import Excel Power BI artifacts (Data Model, Queries, Power View) into a Power BI Desktop file. This new functionality, together with the Reporting Services/Power BI integration that’s coming and the news that Excel Services will no longer be available in SharePoint 2016, raises the question if BI solutions will continue to need SharePoint in the future. All functionality seems to be moving to Power BI!

On the other hand, Microsoft recently announced a collaboration with Pyramid Analytics which will offer the possibility to deploy a Power BI Desktop file to an on-premises Pyramid Analytics server, bringing Power BI on-premises. Pyramid Analytics offered SharePoint integration in the past, so maybe they will integrate again with SharePoint 2016, which results in a Power BI and SharePoint 2016 integration, making SharePoint more important than ever for on-premises solutions.

It’s clear there is a lot of uncertainty about the future of SharePoint as part of the Microsoft BI platform! To be continued…

BI on your terms with SQL Server 2016

Jorg KleinLeave a comment

The last
few years Microsoft’s strategy was all about cloud first (or cloud only?), releasing
new BI products and updates to existing products to the cloud in high pace
without almost any investments in on-premises BI. In 2015 Microsoft seems to
change its course, they now aim more on the enabling of hybrid scenarios,
investing a lot in both cloud (Power BI/Azure) and on-premises with SQL Server
2016.
Microsoft’s message regarding BI for 2015/2016 is:  “BI on your terms”.

BI on your
terms means leveraging up-to-date possibilities for one or a combination (hybrid)
of the following architectures:

  • Cloud
    with Azure and Power BI

  • On-Premises
    with SQL Server 2016

  • Server
    driven or Self-Service

To be able
to offer quality hybrid architectures Microsoft invests a lot in the
on-premises BI suite with SQL Server 2016 and they have announced to keep
investing in it the coming years. So not only cloud first like we have seen in previous
years, but more on hybrid possibilities, and if you desire on-premises only.

For the
first time in many years an exciting version of SQL Server is coming in terms
of BI. The main topics are:

  • Hybrid
    BI (Cloud/On-Premises)

  • Modern
    Reports

  • Enhanced
    Analysis

  • Mobile
    BI

Below is an
overview of the new BI related features per SQL Server 2016 service or product.
As the length of this list shows, SQL Server 2016 will be a massive BI
version!!

Analysis Services Tabular

  • Enhanced
    modeling capabilities in the semantic layer

    • Many-to-many
      relationships

    • BI
      Directional cross filtering. This means you can not only filter on the 1 side
      of a 1 to many relationship in your tabular model, but also on the many side. For
      example, two connected tables, Sales       à Product:

      • Product: product, product category

      • Sales: sales date, connection to
        product table

        Now select products sold
        filtering on sales date(many side) while also filtering on product category (1
        side). This is not possible in today’s version of SSAS tabular.

  • Time
    intelligence

    • Date/time
      columns are automatically converted to rich date/time tables starting from the
      column’s MIN date till the MAX date found

  • New
    DAX functions

    • A
      lot of new functions that at the moment require quite complex formulas like
      present time, date difference, percentile, product, geomean, median, etc.

  • Performance
    improvements

    • For
      end users

      • Query engine optimized

    • For
      developers

      • Metadata operations; modeling
        related operations are much faster

    • For
      data processing

      • Parallel partition processing

  • Expose
    on-premises tabular models in the cloud (hybrid)     à Power BI feature, possible already today
    with SQL Server 2012    .

Analysis Services Dimensional

  • Netezza
    as a Data Source (Netezza Data Warehouse | IBM – NDM Technologies)

  • Performance
    improvements

    • Unnatural
      hierarchies

    • Distinct
      counts

    • Other
      performance improvements in areas where multidimensional is not performant at
      the moment

  • DBCC
    (DataBase Check Consistency) support.     Checks the logical and physical integrity of objects in the specified
    database.

  • Expose
    on-premises multidimensional cubes in the cloud with Power BI (hybrid)

SQL Server Database Engine

  • Integration
    of R analytical engine, predictive analytic capabilities via T-SQL queries

  • PolyBase
    available without the need of PDW, makes it possible to query both structured relational
    SQL, and unstructured Hadoop data through T-SQL statements

  • Data
    encryption for stored data and data in motion

  • Row-level
    security

  • Updates
    to the in-memory OLTP engine, for example updateable in-memory nonclustered
    columnstore indexes

  • Parsing
    and storing native JSON data

  • XEvents-based
    monitoring in Management Studio

Reporting Services

  • New
    look and feel and possibility to apply themes and branding using CSS

  • New
    visualizations, chart types like tree maps and sun bursts

  • Improved
    flexible parameter panel with support for:

    • Autocomplete

    • Search

    • Hierarchical
      tree display

  • Runs
    in all modern browsers on both desktops as tablets (any device)

  • Integration
    of R analytical engine

  • Power
    Query as a data source

  • Pin
    on-premises SSRS reports to Power BI Dashboards (hybrid)

Integration Services

  • High
    Availability support

  • Power
    Query integration

  • Azure
    Data Factory integration (hybrid)

    • Execute
      on-premises SSIS packages from Azure Data Factory

    • Azure
      Data Factory data flow task

    • Azure
      storage connector

    • Azure
      commandlets

  • OData
    4.0 support

  • Hadoop
    File System (HDFS) support

  • JSON
    support

  • New
    Oracle/Teradata connector (4.0)

  • Incremental
    deployment options

  • Custom
    logging levels

  • SSIS
    package templates to reuse ETL code

Mobile BI

  • In
    the cloud with Power BI

    • Power
      BI App for Windows Phone (coming soon) and iOS

  • On-premises
    with Datazen Server

    • Now
      available for free for SQL Enterprise Edition customers (2008 or later)

    • All
      major platforms: Windows Phone, Android, iOS

    • Beautiful
      interface and data visualizations

    • Optimizable
      for Phone, Tablet and Laptop

SharePoint vNext integration

  • Edit
    Mode of PowerPivot Excel workbooks in browser

  • Support
    for Excel vNext (Office 2016) DAX functions

Master Data Services

  • Improved
    performance for large models

  • Row-level
    compression per entity

  • Improved
    user interface

  • Configurable
    retention settings

  • Enhanced
    security possibilities for read, write, delete and create operations and
    support for multiple system administrators with specific permissions

  • Excel
    Add-in is 15 times faster and is updated to support bulk entity based staging
    operation

Visual Studio

  • Database
    and BI project types merged into one Visual Studio

  • New
    scripting language for tabular models. Currently tabular models are wrapped
    into multidimensional constructs and when you deploy it will be reverse
    engineered to the tabular model. The new native language for tabular will be
    easy to understand, modify and deploy.

  • SSIS
    designer supports previous versions of SQL Server

Of course
there is still also a lot of exiting news coming from the cloud side of
Microsoft BI, for example the Azure Data Lake is announced, following the principles of myblogpost about the relational data lake. You can expect a post about the
Azure Data Lake on this blog soon!



P.S. Don’t forget
to suggest and vote for feature requests for SQL Server yourself at:  http://aka.ms/SqlBiUserVoice

 

SQL Azure Reporting is announced!

Jorg KleinLeave a comment

sql-azure-logo-lg

With SQL Azure Reporting Services you can use SSRS as a service on the Azure platform with all the benefits of Azure and the most features and capabilities of premise. It’s also possible to embed your reports in your Windows or Azure applications.

Benefits of the Azure platform for Azure Reporting Services are:

  • Highly available, the cloud services platform has built-in high availability and fault tolerance
  • Scalable, the cloud services platform automatically scales up and down
  • Secure, your reports and SQL Azure databases are on a safe place in the cloud
  • Cost effective, you don’t have to set up servers and you don’t have to invest in managing servers
  • Use the same tools you use today to develop your solutions. Just develop your reports in BIDS or Report Builder and deploy to Azure

Disadvantages are:

  • SQL Azure databases are the only supported data sources in the first version, more data sources are expected to come
  • No developer extensibility in the first version, so no custom data sources, assemblies, report items or authentication
  • No subscriptions or scheduled delivery
  • No Windows Authentication, only SQL Azure username/password is supported in the first version, similar to SQL Azure database. When SQL Azure database gets Windows Authentication, Azure Reporting will follow

Despite the disadvantages of the first version I think SQL Azure Reporting Services offers great capabilities and can be extremely useful for a lot of organizations.
I’m really curious about the CTP, which will be available before the end of this year. You can sign up for the SQL Azure Reporting CTP here

Read more about SQL Azure Reporting here

Start / Stop SQL Server, SSIS, SSAS, SSRS and SQL Server Agent at once with a batch file

Jorg KleinLeave a comment

A lot of developers have SQL Server and its different services running on their PC or notebook to develop or test BI solutions. Unfortunately this slows down your system quite a lot. To speed things up when not using SQL Server, I used to stop and start each service manual quite often.

Recently I found out that it’s possible to start and stop all services at once with a simple batch file. It now only takes a couple of seconds instead of a few minutes and some annoying steps.

Copy/paste the following in a .txt file and rename it to .bat to make it a batch file, execute it by double clicking the file.

START SCRIPT:

NET START “SQL Server Agent (MsSqlServer)”
NET START “MsSqlServer”
NET START “MsSqlServerOlapService”
NET START “ReportServer”
NET START “SQL Server Integration Services”

STOP SCRIPT:

NET STOP “SQL Server Agent (MsSqlServer)”
NET STOP “MsSqlServer”
NET STOP “MsSqlServerOlapService”
NET STOP “ReportServer”
NET STOP “SQL Server Integration Services”

SSRS – Matrix that adds a new column each time 5 rows are filled with data

Jorg KleinLeave a comment

What if you want a dynamic list of values in a matrix but with a maximum of 5 rows. How do you create a matrix like this? I thought this should be an easy job but I found out it was not really simple…

I tried to create a matrix like this for a dynamic list of countries. In this blog I will explain how you can achieve this with a few simple steps.
1. You need to create an MDX(I used a SSAS datasource) query that returns the list of countries with a numbering:

2. Next thing you need to do is create a matrix:

3. Next and last thing you need to do is the following:

  • Use the following expression for the row group: =(Fields!Country_Number.Value – 1) Mod 5
  • Use the following expression for the column group: =Floor((Fields!Country_Number.Value – 1) / 5)

Result:

SSRS – Static column headers in a Matrix

Jorg KleinLeave a comment

How do you create a static column header centered above your dynamic columns? One way to try achieving this is to place a textbox above your dynamic columns. One thing is for sure, the textbox will never be on the perfect centered location and what if the number of dynamic columns grow or shrink?

Thing you need to do is to create a static column group. You do this by adding a new column group to the matrix and give it a static expression, for example: =”static”
Now make it the top group by clicking Up for the static column group on the Groups tab of the matrix’s properties. You can also achieve this by just dragging the column group up in the layout view.

The result, a centered and perfect aligned column header with the text “YTD” above some dynamic columns containing years:

SSRS – Custom expressions for subtotals in a matrix

Jorg KleinLeave a comment

If you want custom expressions for your subtotals in a matrix, for example to calculate an average instead of the default sum, you need to use the InScope() and Iif() functions in your data field…

When you create a matrix with SSRS you get the following default groups:
A row group named:               matrix1_RowGroup1
A column group named:          matrix1_ColumnGroup1

With the normal functionalities you can’t change much on the behavior of your subtotals in your matrix. When you create a subtotal it calculates a subtotal and that’s about it 😉

If you use the following expression in the data field of your matrix you can take full control on the behavior of all your subtotals:

=Iif(InScope(“matrix1_ColumnGroup1”),

Iif(InScope(“matrix1_RowGroup1”),

                                “In Cell”,

                                “In Subtotal of RowGroup1”),

            Iif(InScope(“matrix1_RowGroup1”),

                                “In Subtotal of ColumnGroup1”,

                                “In Subtotal of entire matrix”))

           

Replace “In Cell”, “In Subtotal of RowGroup1”, “In Subtotal of ColumnGroup1” and/or “In Subtotal of entire matrix” with the expressions or fields that you want.


For example, if you want to calculate an average:

Replace “In Cell” with Sum(Fields!Amount.Value)

Replace “In Subtotal of RowGroup1” with Avg(Fields!Amount.Value)


More information about the InScope() function on MSDN

SSRS – Invalid row heights, BUG?

Jorg KleinLeave a comment

Because I got a few reactions regarding the screenshots below (people thought something was wrong with the screenshots) please note that :

Some of the screenshots below look awful because I selected all the text in the matrices with CTRL-A. I did this to make the differences in row heights clear to see. I also made the numbers in the matrix unreadable.


Problem:
As you can see in the screenshot below the row height of rows that contain empty cells differ from the rows where all cells contain data. Screenshot below is taken from IE(with all rows selected), when I run the report in BIDS preview it renders fine!

I think this is strange if you look at the properties of the rows during the problems:

Can Grow and CanShrink are set to False so you won’t expect cells heights to grow or shrink. It seems the problem is that SSRS gives empty cells a row height of 0,25(the default) and I use a row height of 0,20. Result is that everything goes fine until there are rows with empty cells. What’s also very strange is that when I made a print of the report, the row heights were all fine!!

Solution:
When I faced this problem I just tried a couple of things and it seemed the padding property gave an outcome. When I run the report with the following properties for the rows it renders fine and there are no problems:

As you can see in the screenshot from IE below, the row heights are fine now!

Conclusion:
It seems that when you
a) have a matrix with cells that can be empty
b) you use a row height that’s smaller then the default of 0,25
you need to use the following properties for your rows in the matrix to prevent problems:

· Padding: 2,2,0,0
· VerticalAlign: Middle
· CanGrow: False
· CanShrink: False

What makes this strange to me is that a print of the report and preview of the report in BIDS did not show differences in row heights but Internet Explorer did. This problem occurred with IE 7 and a SQL Server 2005 installation with SP and updates installed.