Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

«February 2016»
MonTueWedThuFriSatSun
25262728293031
12345

Power BI Publish to Web for Anonymous Access is Here

Earlier this week on Wednesday the Microsoft Power BI made an incredibly exciting announcement and released Power BI “publish to web” as a preview feature. This is HUUUUGE news! This was probably the top requested feature and its finally here thanks to the hard work and dedication of the Microsoft Power BI team!

Read Getting Started with R Visuals in Power BI

Power BI “publish to web” allows you to easily expose a Power BI report to the world through an iframe that can be embedded wherever you like.

To publish your Power BI report to the web, log into your Power BI site.

Find the report that you want to share and click File in the top left.
Power BI publish to web

You’ll see a message pop up box similar to below. Click the yellow button to create the embed code.
Power BI publish to web preview

This is where you’ll see a very important warning!
WARNING: Reports that you expose through the “publish to web” feature will be visible to everyone on the internet! This means NO AUTHENTICATION is required to view the report that is embedded in your application.
warning 2

Once you do that, you’ll receive an embed code that you can then use to expose your Power BI report within your blog as seen below!

https://msit.powerbi.com/view?r=eyJrIjoiYTNjNzcwNjctNTczMy00ZDMxLWFlMGUtMDViODA1NGZiNmI0IiwidCI6IjcyZjk4OGJmLTg2ZjEtNDFhZi05MWFiLTJkN2NkMDExZGI0NyIsImMiOjV9

As you can see the report maintains all the interactivity features of Power BI. And as your Power BI report updates and changes, those changes will be reflected in your embedded Power BI reports!

Pretty awesome!

Additional Resources

Read the Power BI “publish to web” announcement here.

Read the Power BI “publish to web” documentation here.

Feedback

Let me know what you think of this feature or if you have any questions. Leave a comment down below.


Read more
67
8

MDX NON EMPTY KEYWORD VS NONEMPTY FUNCTION

Non Empty vs NonEmpty

Hey everyone, in this blog I want to address a very common MDX Question. What is the difference between the NON EMPTY keyword and NONEMPTY function? To take it a step further which one should you use?

Non Empty keyword VS NONEMPTY Function.

The big difference between the NON EMPTY keyword and the NONEMPTY function is when the evaluation occurs in the MDX. The NON EMPTY keyword is the last thing that is evaluated, in other words after all axes have been evaluated then the NON EMPTY keyword is executed to remove any empty space from the final result set. The NONEMPTY function is evaluated when the specific axis is evaluated.

Should I use NON EMPTY keyword or NONEMPTY function?

Ok Mitchell, so you told me when each of these are evaluated but really you haven’t told me anything up until this point. Can you tell me which one I should use already? Well, unfortunately, it depends. Let’s walk through an example of each using the BOTTOMCOUNT function.

BOTTOMCOUNT FUNCTION with NON EMPTY Keyword

In this example I’m returning the bottom ten selling products for internet sales. Notice that I have returned all products that have no internet sales, this is not necessarily a bad thing, maybe you want to return products that don’t have sales.

image

However if you don’t want to return these products then we can try using the NON EMPTY keyword. In the below example you can see the results when I add NON EMPTY to the ROWS axis.

image

WHOOOAAA, what happened?? A lot of people would have expected the results here to show the bottom ten products that DID have sales. However, that is not the case, remember that I said the NON EMPTY keyword is evaluated LAST after all axes have been evaluated. This means that first the bottom ten selling products which have $0 in sales are first returned and then the NON EMPTY keyword removes all that empty space from the final result.

BOTTOMCOUNT function with NONEMPTY function.

So let’s try this again, if you want to return the bottom ten products that had sales then we must first remove the empty space before using the BottomCount function. Take a look at the code below:

image

In this code we first remove the empty space before using the BOTTOMCOUNT function. The result is we return the bottom ten products that had internet sales. Once again neither one is right or wrong here it just depends on what you want in your final result.

NON EMPTY Keyword vs. NONEMPTY Function – Performance

There is a very common misconception that the NONEM

Read more
91011121314
15161718192021
22232425262728
29123456

Microsoft Integration Stack

  • 14 February 2013
  • Author: MarkGStacey
  • Number of views: 13276
  • 0 Comments

Note:

 This blog post has moved to : http://markgstacey.net/2013/02/12/microsoft-integration-stack/

Microsoft Integration Stack

I am often asked the question about WHICH Microsoft technology to use when integrating systems, and it’s a very relevant question, as there are so many different technologies. The good news is that each does have its’ place – there is some overlap, and most of these can be shoehorned to do the other roles, but for the most part, it’s fairly clearcut.

First, a list of the technologies

Technology

Use

BizTalk

SOA Integration

.NET/WCF

Custom SOA Integration. Not an integration technology, you’re writing code….

MSMQ

Message Queuing

SSIS

ETL

Sync Framework

“Online” integration, especially mobile

Service Broker

Message Queuing – high performance integration

Stream Insight

Complex Event Processing

 

Of these, I have implemented a production version of all SSIS, SyncFramework, Service Broker, and dabbled in WCF during my dev. I’m currently prototyping a StreamInsight application, and I have never seen BizTalk.

So let’s start with what I’m not really familiar with, not co-incidentally the technologies not provided as part of the SQL stack.

BizTalk is a real-time, service integration. Do your apps talk to each other in a realtime manner? Are the data loads small? Do they need to go via HTTP?
You may need BizTalk, but I have no idea how it helps you over coding a web service. But I’m sure it’s great. WCF of course will also give you options to use MSMQ as your comms channel, and the WCF over MSMQ vs Service Broker is one of the discussions that has real merit.

The choice, as I see is it, needs to be based on whether the channels will be consumed at a data layer or an application layer. If processing and actions are going to occur from the messages directly, then WCF/MSMQ is the correct channel. However, if the messages are going to be used in conjunction with other data, then Service Broker may be a better choice. But more on service broker soon.

SSIS

SQL Server Integration Services, the successor to DTS, is the most used and most widely known of the Microsoft integration stack.

I have most commonly encountered it in loading data warehouses, in an ETL (Extract/Transform/Load) capacity, and this is its’ ideal scenario. Large data loads, relatively homogenous data (i.e. the structure does not change frequently), typically loading from flat files or databases, and definitively loading INTO databases, and more rarely, flat files.

While SSIS does support web services to some extent, it is not designed for this. It does include a “Script task” that lets you do anything you can do in C# or VB.NET (including loading custom assemblies), and this is typically how I query web services from SSIS.

SSIS provides a large array of data sources, and even more transforms, including various lookups, fuzzy matching, deduplication, and other tools, and is even more extended by third party tools. Notification of load status using email, SMS, web service call and other methods are all supported.

What it is NOT suited for is any type of real time integration, or integration with data sources whose structure is dynamic. Also, in most use cases, it is a one-way integration, although of course, one could implement both directions.

Skill Pre-Requisites: T-SQL, SSIS

Overlaps: Not really

Example Application: Data Warehouse, Scheduled Integration between OLTP systems

Sync Framework

Microsoft Sync Framework is designed for distributed and occasionally connected application architecture.

A great example is a field engineer with a mobile device, using an app which will make updates on the local DB, and have them sync’d automatically to the main DB when connectivity is available.

The Sync Framework allows for 2 way integration of a database, by default SQL Server and SQL Server Compact (if you are using an Express or higher edition on the mobile device, a little more work is required), and handles resolution conflicts when updates are made on both the mobile as well as the central DB. And of course, multiple mobile devices are totally supported, and the update code itself can run on either the mobile or the central server (there are performance trade-offs concerning what goes over the wire)

Advantages: If you have a distributed architecture, Sync Framework will definitely save you development time. In addition, it does not require a SQL licence, it can Sync between Express and CE or Express and Express.

Disadvantages: Sync Framework is suited for neither high volume nor low latency applications.

Skill Pre-Requisites: T-SQL, .NET 2.0

Overlaps: Service Broker or WCF for high performance or Low Latency. Easier to implement though

Example App: Occasionally Connected Mobile Application

Service Broker

Service Broker is a high performance message queuing technology built into the SQL stack. The essence of a message queue is that a message is pushed onto a queue (on a server) and popped off the queue (possibly on a different server, possibly different messages by different servers)

As it is optimised for this uni-directional data flow, it is *MUCH* faster than writing to a table, even a heap. In addition, you retain the transactional consistency of SQL Server, so once data has been written to a queue, it is guaranteed to be in the queue, and will only be deleted from the source server has it has been received.

As an in-depth example of how this can be used to enable high-performance applications, one of the systems we developed had a requirement that every single change to the DB be audited in a manner that allowed for rollback, and also traces of the user, the machine used and some other information. This in the end turned out to mean that the audit DB was growing at about 4 times the rate of the operational database.

So the solution was to push the audit messages onto the Service Broker queue, and have them written by separate servers (setting up a receiver on each)

In addition, as the receivers for one queue can sit on multiple machines, another solution is to only run the receivers on the machine writing to the queue when that machine is not under load. The receivers can be throttled.

Another scenario is real-time loading of information to a data warehouse or OLTP system.

Caveat: Data sent in a service broker message is NOT compressed. It is a good idea to compress your messages if you are working on a geographically distributed solution, and are not CPU bound.

Another usage of Service broker is to perform some calcs/aggregations on the data as it comes off the queue, and prior to writing to DB. Has benefits, but loses out to StreamInsight on processing speed. However, if you already are using ServiceBroker, simplicity of only using one technology would win out. And Stream Insight does NOT provide a transport layer.

Advantages: Fast communication. Scalability (scale out)

Disadvantages : No native compression, only WCF provides that out of the other technologies though.

Skill Pre-Requisites: T-SQL, Service Broker.

Overlaps: MSMQ. Stream Insight, but only a little

Example App: High Performance Distributed Auditing. Real Time Data Loads. Scale Out Architecture

Stream Insight

Firstly, let me make this very clear: Stream Insight does not provide a transport layer. Stream Insight is for Complex Event Processing, but the events themselves must be provided to the Stream Insight process.

Having said that, hooking it up to transport layers is easy enough, as owing to its’ 3 piece architecture (Input Adapter, Query Layer, and Output Adapter), wiring the transport into the Input Adapter will be as easy as building the Input Adapter.

And on to the next point…. Stream Insight is not an application. It is a technology. You will be writing C# code (and no other .NET language mind you, ONLY C#) for your Input Adapters, Output Adapters, and binding LINQ Queries to both.

Stream Insight provides a mechanism to do calculations and aggregations on events as they come in, and all the data is stored in memory. An output adapter can provide the results of these calculations/aggregations to a UI, a web service, or persist it to a database – actually anything you want it to do really, since you have to write the code.

A good example of the usage of this is monitoring a rev counter on an engine. For every revolution, a single event is sent (a “Point” event in CEP terms), and storing each of these in a database will quickly lead to huge volumes. A more efficient approach may be to aggregate the revolution events, and at a set interval (a minute, or 10 seconds, or every second even), write the Maximum, Minimum, Count, Average and Mean values to a table. A vastly smaller footprint, with not much information value lost. Of course, choosing the aggregations, intervals and calculations correctly is essential – information such as the rate of acceleration is important when monitoring driving style and would be lost with the above set of calculations.

My next pass with this is comparing the values above against a predictive model, and sending alerts for outliers. For instance, if the engine was on a car, a vastly different driving style on this particular vehicle may indicate that it has been stolen.

Combining separate streams is the next area : so multiple sensors can be combined in one of these queries.

Advantages: The performance benefit of using the CEP streams is immense over trying to use a DBMS. In addition, the separation of Input, Output and Query allows for the creation of multiple applications where User defined queries based on the needs of the moment (for example dashboards, alerts etc) can be issued without changing the underlying code. This creates the space for an ecosystem of partners to develop based on StreamInsight, as the DBMS of CEP, vs other CEP providers who supply a complete solution.

Also, a built in persistence model to a CE database is great.

Dis-advantages: It’s like installing SQL. You do not have an application; you have a technology to create an application. Also, advanced .NET coding skills are required, .NET 2.0 knowledge will not suffice

Web and Standard Edition only support up to 5000 events per second. Data Center edition is expensive

Skill Pre-Requisites: T-SQL, .NET 3.5, Stream Insight

Overlaps: Service Broker, but only a little. Definite use case for both in one application

Example App: Realtime Sensor monitoring, High Speed Investment Trading, Monitoring of Web Service Feeds

Quick and dirty diagram

 Integration diagram

Cheat Sheet – SQL Technologies

Technology

Scenario

SSIS

High volume, batch loading

Sync Framework

2 way synchronisation, low volume,

Service Broker

Low latency, high or low volume

Stream Insight

Calculations – used in conjunction with a transport layer

 

Print
Categories: Blogs
Tags:
Rate this article:
No rating

MarkGStaceyMarkGStacey

Other posts by MarkGStacey

Please login or register to post comments.