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

Creating Your First Tabular Model (part 1)

  • 22 March 2012
  • Author: DustinRyan
  • Number of views: 32743
  • 0 Comments

With this post I begin a series of blog posts covering one of the most talked about features of SQL Server 2012: Tabular Modeling. Being new to this like most of us are, I hope to learn much about Tabular Modeling as I walk through the basics of building your first Tabular Model. In this installment, we'll talk about what a Tabular Model, when a Tabular Model is the right choice, and of course how to create a Tabular Modeling.

What is a Tabular Model?

With the release of SQL 2012, we are (re)introduced to tabular modeling. If you're familiar with Power Pivot, you're going to notice many similarities and will most likely pick up the tabular modeling aspect of SSAS pretty quickly. Basically a Tabular Model is an in-memory database in SQL Server Analysis Services. The Vertipag engine that was previously only used in Power Pivot is now utilized within Power Pivot and SSAS 2012 Tabular as xVelocity. The xVelocity technology allows you to perform complex analytics of your data all in-memory while making use of column store indexes. This eliminates expensive IO unlike SSAS Multi Dimensional Modeling where IO is a viable concern.

The Tabular Model also allows us to bring together multiple data source types very easily, similarly to Power Pivot. Bringing together data stored in a SQL Server Database, Oracle, Excel, and Access is not only possible but straight forward.

1 Data Source Types

Once you've imported your data from whatever sources you need, defining relationships is very easy. Simply dragging and clicking an arrow from one object to another is all that is required here.

2 Creating Relationships

When Do I Choose Tabular Over Multidimensional Modeling?

You might be asking yourself, "Self, if Tabular is so fast and great, why would I ever use Multidimensional Modeling"? That's a valid question, so lets go over some of the perks of each and when one or the other would the optimal choice.

1. If you need access to many different external data sources, choose Tabular. Multidimensional can do this to an extent, but if you need to relate an Excel spread sheet, a text file, an SSRS Report Feed, and your database data, Tabular is the way to go here.
2. If you need complex calculations, scoping, and named sets, choose Multidimensional.
3. If you need mind numbing speed and consistently fast query time, choose Tabular.
4. If you need Many-to-Many relationships, choose Multidimensional. You can model this relationship type in Tabular, but Multidimensional is still easier to create and manage these more complex relationships.
5. If you are planning on using Power View, choose Tabular. At this time its impossible to build Power View reports against a Multidimensional model, but that could change in the future.
6. If you don't know DAX and want to use Tabular, either take the time to learn or use Multidimensional ; ) .
7. If your solution requires complex modeling, choose Multidimensional.

Take these points into consideration when choosing Tabular vs. Multidimensional. This isn't every single consideration to think about, but should at least get you started in understanding the differences between Tabular and Multidimensional.

How Do I Create a Tabular Model?

So now that we have a general understanding of what the Tabular Model is and what are some of scenarios we should choose to use the Tabular Model, lets start creating our first Tabular Model.

For you to play along with my example, you're going to SSAS 2012 installed in Tabular Mode.

Select Tabular Mode

If you're not sure if your instance of AS is in Tabular Mode, just connect to Analysis Services in SQL Server Management Studio and look at the icons next to your instance of SSAS.

Tabular and Multidimensional Instances

The Tabular SSAS instance has the nifty little blue icon and the Multidimensional instance has the same icon as before in 2008.

You will also need SQL Server Data Tools and the AdventureWorksDW2012 sample database.

So first things first. Open SQL Server Data Tools.

3 Open SSDT

Then go to File, select New, then click Project.

4 Create project

Under Business Intelligence, highlight Analysis Services, and select Analysis Services Tabular Project. I'm naming my project FirstTabularProject. Then click OK.

5 Create SSAS Tab Project

After clicking OK you can see the new project in the Solution Explorer with an empty model, Model.bim.

6 Model in Solution Explorer

With the project created, your empty model should be open in the Designer Window. So now its time to create a connection to our data source(s). In the top left of the menu tool bar, click the Import From Data Source icon.

7 Import From Data Source Icon

Then select the type of data source you want to connect to. In my case, I'm connecting to a SQL Server database. Select Microsoft SQL Server and click Next. Specify the Server name, the credentials, and the Database.

8 Connect to SQL Server

After clicking Next, we must specify the Impersonation Information. These are the credentials that Analysis Services will use to access the data source when importing and processing the data. We can either specify specific credentials or tell it to you use the AS Service Account. I'm specifying credentials.

9 Impersonation Info

On the next screen, we need to choose how to import the data. We have two options: We can either select from a list of the tables and views which objects we'd like to import or we can write a query to specify the data to import. I'm selecting from the list of tables.

On the Select Tables and Views screen, you'll see a list of the Tables and Views in your database. I can browse through this list and places checks next to all the tables and views I'd like to import. Or I can select a table and then click the button Select Related Tables. This will use the referential integrity of the database to determine which tables to check for you. Be careful clicking Select Related Tables. If you accidently click the button and the wizard selects 20 other tables, there's no easy way to unselect the newly selected tables. I've selected FactInternetSales and allowed the wizard to select the dimensions based on the referential integrity.

10 Select Tables

Important:

Before clicking Finish, you'll want to make sure that you highlight each table you want to import and then click the Preview & Filter button. This will allow you to not only preview the data, but also uncheck any fields that you do not wish to import into your model. This is important since the database will be stored in memory. We do not want to store any unnecessary data that we do not have to. You can see that I've gone through the FactInternetSales table and unchecked the fields I don't want to import.

11 Uncheck unwanted fields

After filtering out the unnecessary fields, click Finish. The importing of the data will begin.

12 Importing Data

Once it is finished, click Close. You'll notice the data has been imported and is now viewable in the Designer Window.

If after importing your data you decide you need to bring in another table from the same data source, click the Existing Connections icon.

13 Existing Connections icon

Then click Open and you are able to add new tables, views, or named queries to your model.

In the Designer Window we have two views. The Grid view allows us to see the imported data, with each table on an individual tab.

14 Grid View

We can also switch to the Diagram View by clicking the Diagram View icon at the bottom right of the Designer Window. The Diagram View is ideal for viewing all the imported tables and their relationships at one time.

15 Diagram View

So now that we've imported in our data, we need to add some measures to our model. Switch back to the Grid view and click over to the tab for the fact table, FactInternetSales. Select the first text box in the Measures Grid directly below the Sales Amount field. If the Measures Grid is not visible, just click the Show Measures Grid icon to toggle it back on.

17 Measures Grid

After highlighting the text box beneath the Sales Amount field, click the Sum (Sigma) icon. This will automatically create a measure with an aggregation type of Sum. Then go into the properties of your new measure and give it a friendly name since this is the name that your users will see when browsing the cube.

16 Add a measure

Then do the same for the Order Quantity field.

Now lets deploy and process our model. By default, the model will be deployed to default instance of SSAS on the local machine. We can change the server we want to deploy to by right-clicking the project in the Solution Explorer and selecting properties. You can also change the name of the database that will be created when you deploy the Model.

18 Project Properties

In the Menu bar, click Build, then click Deploy .

19 Deploy Project

This will begin the deployment and processing steps. If you specified specific credentials to use for impersonation, you'll need to enter the user's password during this step.

20 Deploying

Once the model is deployed, we can now view our model deployed to the AS server and browse it with Excel. Click the Analyze in Excel icon and your model will open in Excel so you can browse it.

22 Analyze in Excel

We've created our first Tabular Model. I hope this gives you a good introduction on what Tabular is, when Tabular is the right choice, and the basics of creating a Tabular Model.

In the next article, we'll get more into modifying our model by building hierarchies in our dimensions and other more advanced topics so stay tuned for the next article. And as always, post any questions or comments here and I'll answer them as best I can.

Print
Categories: Blogs
Tags:
Rate this article:
5.0
DustinRyan

DustinRyanDustinRyan

Other posts by DustinRyan

Please login or register to post comments.