Author: Soheil Bakhshi

Power BI, Power BI Desktop, Uncategorized

Build You First Dashboard in Microsoft Power BI Designer, Access Analytics Everywhere

This is the third article from Power BI Designer series. To fully understand this article you need to read my previous posts “Build Your First Report in Microsoft Power BI Designer Part 1, Basics” and “Build Your First Report in Microsoft Power Bi Designer Part 2, Make it More User Friendly” as well. In this article I explain how to publish your predefined reports on www.powerbi.com website which is free. So after publishing the reports, you can create flashy reports very easily. By very easily, I mean it! Creating dashboards is even easier than dragging report objects and dropping them somewhere on the tool! I’ll explain how that is possible. Actually, it is all about the awesomeness of the online BI Designer.

Frist of all, you need to create an account in www.powerbi.com. Unfortunately, you’ll need to have a corporate email address that means you’re NOT allowed to use free email accounts like MSN, Hotmail, Yahoo, and Gmail and so on. But, if you’re a student with a valid university email address or if you’re an employee with a corporate email address, then you’ll be fine.

Let’s start.

  • Browse www.powerbi.com
  • Enter your valid email corporate or university email address then click on “Use it Free”

Continue reading “Build You First Dashboard in Microsoft Power BI Designer, Access Analytics Everywhere” →

SQL Server, SQL Server Management Studio - SSMS, T-SQL

Batch Index Rebuild without Using Cursor

Today I came across a cube processing performance issue with one of our clients. So I started a step-by-step troubleshooting including optimising named queries. In some cases the named queries were actually querying some SQL views from the source data warehouse.

After all, I created about 35 new indexes and I needed to justify that all of those indexes are really used. As I processed the faulty cube several times during my step-by-step troubleshooting process it seemed all of those indexes were used.

But, I knew that I created some indexes that covered by some of the new ones and those indexes won’t be used.

I needed to rebuild all the indexes, however, rebuilding all of those indexes from SSMS UI would be such a pain. So I needed to do a batch index rebuild.

So I googled and I’ve found some scripts which actually are doing the job, but, all of them were using cursors. Sadly, I hate cursors so they are the last item in my book. Indeed, I’ll never use cursors until it’s absolutely necessary and there is no other better choices.

Therefore, I decided to do it in my way and I wrote the following script. I thought I’d be happy to share it with you guys as it might help some of you as well.

declare @ix varchar(max), @tbl varchar(max), @counter int, @CustomIx Varchar(max)

declare @table table (id int, tbl varchar(max), ix varchar(max))

set @CustomIx = ‘YOUR_INDEX_NAME_STARS_WITH’ –Custom index name will be like MY_IX_***

insert into @table (id, tbl, ix)

SELECT   ROW_NUMBER() over (order by ix.[NAME]) id

           , OBJECT_NAME(ixstat.[OBJECT_ID]) AS [OBJECT NAME]

         , ix.[NAME] AS [INDEX NAME]

FROM     SYS.DM_DB_INDEX_USAGE_STATS AS ixstat

         INNER JOIN SYS.INDEXES AS ix

           ON ix.[OBJECT_ID] = ixstat.[OBJECT_ID]

              AND ix.INDEX_ID = ixstat.INDEX_ID

WHERE    OBJECTPROPERTY(ixstat.[OBJECT_ID],‘IsUserTable’) = 1

          and  ix.[NAME] like @CustomIx+‘%’

 

set @counter= (select max(id) from @table)

 

while @counter >=1

begin

    set @ix = (select ix from @table where id = @counter)

    set @tbl = (select tbl from @table where id = @counter)

    exec(‘ALTER INDEX ‘+@ix+‘ ON [dbo].[‘+@tbl+‘] REBUILD PARTITION = ALL ‘)

    print @tbl + ‘.’ +  @ix + ‘ Rebuild successful’

    set @counter-=1

end

Continue reading “Batch Index Rebuild without Using Cursor” →

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” →