Author: Soheil Bakhshi

Power BI, Power Query

Build your First Report in Microsoft Power BI Designer Part 2, Make it More User Friendly

PBID

In this post I would like to explain more details about Power BI Designer features. In the previous post you learnt how to create some very simple reports. However, those reports were just for testing general features of the tool. For instance we didn’t even play with very simple features like renaming the dimension or fact tables and members to user friendly names. In this article not only do I explain some of the simple ones, but also I’ll go through some of the more advanced ones.

Again, as per my previous post, I’m using AdventureWorksDW2012 as a source database.  We imported “Internet Sales” into the designer and we created some reports and one new page and we saved the reports on disc. So we have all the requirements on hand. Let’s go…

Making names more user friendly:

  • Open the *.pbix report file in Power BI Designer
  • Double click on “DimCurrency” to rename it to “Currency”. We need to do the same for all other objects

image

  • We also need to do the same for the fields which are getting used on the reports

image

Continue reading “Build your First Report in Microsoft Power BI Designer Part 2, Make it More User Friendly” →

Power BI, Power BI Desktop, Power Query, Power View

Build Your First Report in Microsoft Power BI Designer Part 1, Basics

image

First of all I would like to briefly explain Microsoft Power BI Designer. Then we’ll see how easy we can create a report using designer. I will use Adventure Works DW database as the source database.

Microsoft Power BI Designer is basically an integration of Power Query and Power View. Saying that the tool is still a preview version and it’s NOT actually a released product we’ll expect to see more features when it’s released. Some features like PowerPivot models expected to be available to the release version. At the moment PowerPivot models are not available in the designer, but, hopefully Microsoft will add it to the tool. So I added an idea into BI in SQL vNext as I believe it would be great to have PowerPivot functionalities included in the release version of the product. We’ll see what happens.

Let’s have a look at the tool. At the first look, I would like to say it’s an amazing tool integrating lots of awesome features all together with ease of use. It’s so fun to use the tool to create very effective and flashy reports in a short amount of time. First of all you need to download the designer from here.  Install the designer and open it. I’ll use AdventureWorksDW2012 as the source database.

Open the Microsoft Power BI Designer Preview. If you want to get more familiar with the tool click on the videos on the startup screen.

·         To connect to SQL Server click on “Get Data” or “New Source”

clip_image002

Continue reading “Build Your First Report in Microsoft Power BI Designer Part 1, Basics” →

BISM Multidimensional, Business Intelligence - BI, SQL Server, SQL Server Analysis Services - SSAS, Uncategorized

Browsing Cubes Remotely from Excel Trough a VPN Connection without Using Windows Authentication

Technically when you connect to another network through a VPN connection you can see all allowed machines on that network. So it is easy to connect to a SQL Server instance using SQL Server authentication. However, I’m explaining this part for some of you guys that might be new to connecting from Excel directly to a database on SQL Server and create flashy reports on Excel.

But, what about connecting directly from Excel to a remote Analysis Services instance without using Windows Authentication? You’re right! I’m saying you can connect directly from your own Excel to a remote SSAS server without using windows authentication. Well, technically there is no SQL Server Authentication mode available for Analysis Services. So what does that actually mean when I say “without using windows authentication”? If you’re interested in finding the answer keep reading this article.

Scenario:

You’re working as a BI consultant, you’ve been told that a client needs to have some simple reports on Excel as follows:

·         You should connect to the client’s server using a provided VPN connection

·         The VPN connection could be established through a Windows VPN, Cisco VPN etc. so the VPN client or the port and protocol used don’t actually matter

·         Microsoft Excel is NOT installed on the client’s server

·         You’re NOT allowed to install Excel on the server

·         As it is a costly process the client will not setup a virtual machine in their network so that you can remotely connect to it and install Excel then connect to their SQL Server/Analysis Services instances

·         There is no trust relationship between your network and the client’s network, so your domain user name and password could not be authenticated on the client’s network

·         The client needs to have some reports on Excel on top of a SQL Server database and OLAP cubes on Analysis Services (SSAS)

·         You have the right to run an application as administrator on the remote server

·         You need to connect to the remote server directly from your own Microsoft Excel installed on your machine

·         The client also provided a remote desktop access to the server

·         On the remote desktop SQL Server Management Studio (SSMS) is installed

·         In the remote SQL Server your account is a member of the “securityadmin” server role so you can create a new SQL Server Login

Continue reading “Browsing Cubes Remotely from Excel Trough a VPN Connection without Using Windows Authentication” →

Administartion, BISM Multidimensional, SQL Server Analysis Services - SSAS

An Easy Way for SSAS Batch Processing

Batch processing is available in Analysis Services. Which means we can send multiple processing commands to the server in just a single SQL Server Job. With SSAS batch processing we can control which objects to be processed and in what order in a batch.  As batch processing reduces the amount of time taken to commit changes it offers better data availability. We can easily generate XMLA codes  for batch processing through SSMS (SQL Server Management Studio). You might see lots of discussions about this in other websites and lots of them are saying you need to right click on the objects one by one and generate the scripts. Then put all scripts together in another XMLA script. But it is such a pain when you have lots of objects that should be selected one after another to generate the batch processing XMLA. Sadly, it is not the end of the story. You need put all scripts together by copying and pasting the scripts several times. Today I want to show you a very easy to the job which saves lots of your time.

I’m using “Adventure Works 2012 Multidimensional” as an example and I’m going to batch process some dimensions.

  • Connect to the SSAS server from Management Studio
  • Expand the database
  • Expand dimensions

image

Continue reading “An Easy Way for SSAS Batch Processing” →