Tag: PowerPivot

DAX, Excel, Power Pivot, Power Query

Dynamically Passing Parameters to a SQL Stored Procedure in Excel 365 Using Power Query

In September 2014, I wrote a blog post on dynamically passing parameters from PowerPivot to a SQL Server stored procedure using VBA. Back then, VBA was a real lifesaver. It perhaps still is for many of us. But frankly, I even forgot how to write VBA. Maybe it is time to look at it again. I also wrote a quick tip in August 2020 about doing a similar thing in Power BI using Query Parameters. Check it out if you’re keen to know how it works in Power BI.

Eight years later, one of my weblog readers asked how to do the same thing in later versions of Excel; he is specifically asking for Excel 2019. I thought it would be good to cover this topic after 8 years and see how it works now. So, here it is, a new blog post.

The Problem

From time to time, Excel users require to get the data from a SQL Server stored procedure. The stored procedures usually accept some input parameters and return the results. But how can we dynamically pass values to the stored procedures from cells in Excel to SQL Server?

Prerequisites

For this blog post, I use SQL Server 2019 and Microsoft’s famous sample database, AdventureWorks2019. You can find Microsoft’s other sample databases here. I also use Excel 365, it should work the same way in Excel 2019, though.

The Solution

I discuss two approaches to overcome the challenge. Both approaches use Power Query slightly differently. In both approaches, we parameterise the SQL Statement of the SQL Server connector, passing the values to the parameters from an Excel table. One approach requires ignoring the Privacy Levels in Power Query, while the other does not. Both approaches work, but, depending on your preferences, you may prefer one over the other.

As mentioned, I use the AdventureWorks2019 sample database that contains a couple of stored procedures. I use the dbo.uspGetBillOfMaterials stored procedure accepting two parameters, @StartProductID and @CheckDate.

Approach 1: Parameterising the SQL connector’s SQL Statements, Ignoring Privacy Levels

Follow these steps to pass the parameters’ values from an Excel sheet to the stored procedure and get the results in Excel:

  1. In Excel, navigate to the Data tab
  2. Click the Get Data dropdown
  3. Hover over the From Database option and click the From SQL Server Database
  4. Enter the Server
  5. Enter the Database
  6. Expand the Advanced options
  7. Type EXEC [dbo].[uspGetBillOfMaterials] @StartProductID = 727, @CheckDate = N'2013-01-01' in the SQL statement textbox
  8. Click OK
Using SQL Statement in Power Query for Excel
Using SQL Statement in Power Query for Excel
  1. Click the dropdown on the Load button
  2. Click Load to
Load to Options to Load the Results of Power Query query into an Excel Sheet or PowerPivot Model

From here, we have some options to load the results either into an Excel sheet or the PowerPivot data model. We want to load the data into the PowerPivot data model in this example.

  1. Select Only Create Connection
  2. Check the Add this data to the Data Model option
  3. Click OK
Loading the Power Query Data into PowerPivot in Excel
Loading the Power Query Data into PowerPivot in Excel
Continue reading “Dynamically Passing Parameters to a SQL Stored Procedure in Excel 365 Using Power Query” →
Azure Analysis Services, BISM Tabular, Business Intelligence - BI, Data Warehousing - DW, DAX, Power BI, Power BI Desktop, Power BI Desktop RS, Power Pivot, Power Query, Quick Tips, Self-Service BI, SQL Server, SQL Server Analysis Services - SSAS, T-SQL

Quick Tips: Time Dimension with Time Bands at Seconds Granularity in Power BI and SSAS Tabular

Time Dimension with Time Bands at Seconds Granularity in Power BI and SSAS Tabular

I wrote some other posts on this topic in the past, you can find them here and here. In the first post I explain how to create “Time” dimension with time bands at minutes granularity. Then one of my customers required the “Time” dimension at seconds granularity which encouraged me to write the second blogpost. In the second blogpost though I didn’t do time bands, so here I am, writing the third post which is a variation of the second post supporting time bands of 5 min, 15 min, 30 min, 45 min and 60 min while the grain of the “Time” dimension is down to second. in this quick post I jump directly to the point and show you how to generate the “Time” dimension in three different ways, using T-SQL in SQL Server, using Power Query (M) and DAX. Here it is then:

Time Dimension at Second Grain with Power Query (M) Supporting Time Bands:

Copy/paste the code below in Query Editor’s Advanced Editor to generate Time dimension in Power Query:

let
Source = Table.FromList({1..86400}, Splitter.SplitByNothing()),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "ID"}}),
#"Time Column Added" = Table.AddColumn(#"Renamed Columns", "Time", each Time.From(#datetime(1970,1,1,0,0,0) + #duration(0,0,0,[ID])), Time.Type),
    #"Hour Added" = Table.AddColumn(#"Time Column Added", "Hour", each Time.Hour([Time]), Int64.Type),
    #"Minute Added" = Table.AddColumn(#"Hour Added", "Minute", each Time.Minute([Time]), Int64.Type),
    #"5 Min Band Added" = Table.AddColumn(#"Minute Added", "5 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/5) * 5) + 5, 0)), Time.Type),
    #"15 Min Band Added" = Table.AddColumn(#"5 Min Band Added", "15 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/15) * 15) + 15, 0)), Time.Type),
#"30 Min Band Added" = Table.AddColumn(#"15 Min Band Added", "30 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/30) * 30) + 30, 0)), Time.Type),
#"45 Min Band Added" = Table.AddColumn(#"30 Min Band Added", "45 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/45) * 45) + 45, 0)), Time.Type),
#"60 Min Band Added" = Table.AddColumn(#"45 Min Band Added", "60 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/60) * 60) + 60, 0)), Time.Type),
    #"Removed Other Columns" = Table.SelectColumns(#"60 Min Band Added",{"Time", "Hour", "Minute", "5 Min Band", "15 Min Band", "30 Min Band", "45 Min Band", "60 Min Band"})
in
    #"Removed Other Columns"
Continue reading “Quick Tips: Time Dimension with Time Bands at Seconds Granularity in Power BI and SSAS Tabular” →
Business Intelligence - BI, DAX, Power BI, Power BI Desktop, Quick Tips

Quick Tips: Keyboard Shortcuts/Hotkeys When Writing DAX in Power BI Desktop

DAX Keyboard Shortcuts in Power BI Desktop

Keyboard shortcuts is an interesting topic for developes that can really improve your report development in Power BI Desktop. In this post I show you some keyboard shortcuts/hotkeys when writing DAX in Power BI Desktop.

 

  • Indend right: Ctrl + ]
  • Indent left: Ctrl + [

Mini-tip: You can also indent your code to ther right by pressing TAB or indent left by pressing Shift + TAB. But, the difference is that if your cursor is in the middle of a line, when you press TAB it divides your code to two pieces and indends the characters to the right from the position that cursor is in.

  • New line keep indent: Shift + Enter
  • New line starting from first of line: Alt + Enter
  • Activate Intellicence: Ctrl + Space
  • Comment multiple lines: Ctrl + KC or Ctrl + /
  • Uncomment multiple lines: Ctrl + KU or Ctrl + /
  • Move the current line up/down: Alt + Up/Down Arrow Key
  • Enter multiple lines of code at once: Ctrl + Alt + Up/Down Arrow Key
  • Find and replace a word: Ctrl + D to highlight the current word, Ctrl + D again to find/highligh the same next word. Continue pressing Ctrl + D to find/highlight all same words, then start typing to replace all words at once
  • Find and replace all of a kind at once: Ctrl + Shift + L to highlight a part of your DAX expression then start typing to replace the highlighted words at once

Continue reading “Quick Tips: Keyboard Shortcuts/Hotkeys When Writing DAX in Power BI Desktop” →

DAX, Power BI, Power BI Desktop

Using “IN” Operator in DAX

IN operator in DAX

If you are a SQL guy I bet you’ve used “IN” operator zillions of times. You might also looked for the same functionality in DAX and I’m sure you’ve found fantastic blog posts showing you how to mimic the same functionality in DAX. The October release of Power BI Desktop is full of new analytics features such as Grouping, Binning and TOPN filtering. On top of that, one new awesome feature that is not documented at time of writing this article, or at least I haven’t find anything over the internet, is “IN” operator in DAX. In this post I show you how to use it in your DAX expressions.

Requirements

Note 1: You need to install SSMS2016 to be able to write DAX queries provided in this article. Alternatively, you can use DAX Studio . If for any reasons you cannot use SSMS 2016 or DAX Studio and you only have Power BI Desktop, don’t worry, I’ll provide some examples in Power BI Desktop as well.

Note 2: If you run previous versions of SQL Server it’s absolutely alright. There is nothing special in AdventureWorksDW2016CTP3 for this article that you don’t get in older versions of the sample database. But, keep in mind that SQL Server 2016 Developer Edition is now free and you can download it very easily. Check this out if you’re interested to see how.

Getting Started

After downloading the latest version of Power BI Desktop run it then

  • “Get Data” from SQL Server
  • From AdventureWorksDW2016CTP3 load “FactResellerSales”, “DimProduct”, “DimProductCategory”, “DimProductSubCategory” and “DimDate” to Power BI Desktop model
  • Find the local port of Power BI Desktop by opening “msmdsrv.port.txt” file from the following path:

“%UserProfile%\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces\AnalysisServicesWorkspaceXXXXXXXX\Data”

Note: The “XXXXXXXX” postfix is a random number. 

  • Open SSMS 2016 and connect to Power BI Desktop model as an Analysis Services local server. Do you want to learn more about how to connect your Power BI Desktop model from different software? Then check this out.

SSMS Connect to Power BI Desktop Model

  • Open an MDX new query
  • Run the following DAX query
EVALUATE
    SUMMARIZE('FactResellerSales'
                , DimDate[CalendarYear]
                , "Total Reseller Sales"
                , SUM('FactResellerSales'[SalesAmount])
                )

Here is the results:

Writing DAX in SSMS

Now we want to filter “CalendarYear” so that the query shows sales values for 2011 and 2012 only. One common scenario we had to do in prior versions of Power BI Desktop, Power Pivot or SSAS Tabular model was to use a logical OR operator “||” like below:

EVALUATE
FILTER(SUMMARIZE(FactResellerSales
                    , DimDate[CalendarYear]
                    , "Total Reseller Sales"
                    , sum(FactResellerSales[SalesAmount])
                    ) , DimDate[CalendarYear] = 2011 || DimDate[CalendarYear] = 2012
                    )

From now on we can write the above query using “IN” operator in DAX like below:

EVALUATE
    FILTER(
        SUMMARIZE(FactResellerSales
                    , DimDate[CalendarYear]
                    , "Total Reseller Sales"
                    , sum(FactResellerSales[SalesAmount])
                    ) 
            , DimDate[CalendarYear] 
                IN (2011, 2012)
            )

Here is the results:

IN operator in DAX

Continue reading “Using “IN” Operator in DAX” →