Power Query and SQL Server Stored Procedures

Today I want to explain how you can pass parameters to a SQL Server stored procedure. I myself was looking for a way to pass parameters to a SQL Server stored proc from Power Query. I spent a lot of time to search for a good article over the internet that explains how I could pass parameters to a stored proc from Power Query and show the results in Excel. But, I couldn’t find that much information around this as I expected. So, I decided to do some work around and you can read the results in this post. To simplify the solution, I’m going to use uspGetBillOfMaterials stored procedure in AdventureWorks 2012 database. The stored procedure accepts an integer number as ProductID and a date as CheckDate. So we need to pass two parameters to uspGetBillOfMaterials  to get the results.

If we execute the stored proc in SSMS using

exec [dbo].[uspGetBillOfMaterials] 727, ‘2009-01-02’

, we’ll get the following result:

image

Now, lets go to do some works on Power Query. So open Microsoft Excel and go to Power Query tab and select SQL Server database.

image

Now type Server, Database and SQL Statement, then click OK.

Continue reading Power Query and SQL Server Stored Procedures

Creating a Simple Date Dimension Using Recursive Common Table Expressions (CTE)

In this post I’ll explain how to create a simple date dimension to use it in your data warehouses and your BI solutions. So, this article is for you if you need a fast and easy way to make a simple date dimension that supports the most commonly used date elements like

·         Integer date key

·         Different date formats

·         Quarter

·         Month names

·         Week numbers

·         Day of the week

·         Day of the year

·         Is day end of month

·         Not available (N/A) row

Due to the fact that there are lots of fellows that are still using SQL Server 2008 and earlier, I put the codes that support SQL Server 2008  as well as SQL Server 2012. But, I’ve commented the 2008 lines.

Continue reading Creating a Simple Date Dimension Using Recursive Common Table Expressions (CTE)