Category Archives: Pragmatic Works

Power BI Tips, Tricks & Best Practices Webinar Recording & Materials Now Available

image

Thank you to everyone that attended my Power BI webinar last month, September 29th. Sorry its taken me a while to finally make the information available, but my schedule has been crazy lately! The good news is, however, the recording is available! So if you weren’t able to watch the webinar live, you can still catch the recording anytime you like.

View the Power BI webinar recording

Continue reading Power BI Tips, Tricks & Best Practices Webinar Recording & Materials Now Available

How I Got my Start at Pragmatic Works

October 4th of this month was my seven year anniversary as an employee of Pragmatic Works. Things have changed a lot over the past seven years. Working with the wonderful people at Pragmatic Works has been quite an amazing journey and incredible opportunity. With that in mind I thought that I should share my story of how I ended up working in the business intelligence field with the great team at Pragmatic Works. Continue reading How I Got my Start at Pragmatic Works

Refreshing Excel Power Query & Pivot Tables with SSIS and Task Factory

image With SSIS 2014 and earlier there is currently not native way to refresh an Excel workbook which include Power Query queries. Now that functionality is rumored to be included with SQL Server 2016 but if you’re currently running SQL Server 2014 or 2012 you are out of luck. But that’s why Pragmatic Works put together the Excel Power Refresh component for SSIS.

Configure the Excel Power Refresh Task in SSIS

Configuring the Excel Power Refresh Task is pretty straightforward. There’s not a lot of complexity to this component, which is a good thing.

First create a Connection Continue reading Refreshing Excel Power Query & Pivot Tables with SSIS and Task Factory

Navigating Hierarchies with MDX webinar recording is now available!

image Thanks to everyone that attended my webinar on Navigating Hierarchies with MDX! We looked at a bunch of different ways we can navigate up, down, and side to side in our hierarchies in order to do some really neat things with calculations. If you would like to view the recording, you can do that here completely for free! Also, if you’d like to view my PowerPoint slide deck and scripts I used for the webinar, you can download those from here. Just download the Navigating Hierarchies with MDX .zip file. 🙂

Now on to the questions!

Q: Is there anything like storeproc / pre stroed mdx query in SSAS which can be called in .net application.
A: There is a concept of SSAS stored procedures, which you can read more about here: http://msdn.microsoft.com/en-us/library/ms176113.aspx and here: http://asstoredprocedures.codeplex.com/

Q: How would ParallelPeriod handle a leap year?
A: ParallelPeriod returns the member at the same position in the specified period. So if the 29th day of February does not exist in the previous year, then no value will be returned:
image

Q: Can you use PeriodsToDate() on a ‘custom’ period like an Academic Term?
A: PeriodsToDate can be used on any user defined hierarchy.

Q: Setting date property for MTD, QTD seems straightforward.  What about WTD (week-to-date)?  It seems it might take some careful work each year.
A: If you have a Week attribute in your Date dimension, that should be set to Week, as well. That’s all that is required. 🙂
image

Q: What is the name of the zoom tool and highlighter used, just curious.
A: Zoomit. It’s free, too! I get asked that question every time I present.

Thanks for all the great questions, everyone! If you have any further questions, please feel free to post it here or to send me a tweet!

Navigating Hierarchies With MDX Webinar 9/23/14 11 am EST

Heads up, everyone! I’ve got a free training event coming up on Tuesday, September 23 at 11 a.m. EST! Next Tuesday I’ll be discussing Navigating Hierarchies With MDX.

One of the great strengths of SQL Server Analysis Services is the ability to create hierarchies by defining relationships between attribute fields. In this webinar, we’ll take a look at how we can fully leverage our SSAS hierarchies in our MDX queries and calculated measures using navigational functions such as PARENT, DESCENDANTS, PARALLELPERIOD, and many more! So if you’ve ever wanted to learn more about some of the cool, navigational functions built into MDX or have had questions about creating really powerful MDX calculations, this webinar is for you!

But the best part is it’s all free training! Just get signed up and register for the event, completely free of charge. I look forward to seeing you there!

Pragmatic Works Release Doc xPress Server Edition

This month marks the official release of Pragmatic Works’ Doc xPress Server Edition! While everyone knows that Doc xPress gives you the capability to document your SQL Server databases, SSIS packages, SSAS cubes, and SSRS reports in way like no other tool. But with the release of the Server Edition, you can now host your documentation to a hosted web application making it now easier than ever to share documentation, lineage, and data dictionary information across your organization. Imagine being able to provide your technical users as well as your business users a one-stop-shop to all of your organization’s technical documentation without requiring anything to be installed on their desktop! Doc xPress Server Edition provides that capability!

Being able to view your Doc xPress-generated documentation online means that your technical team can quickly and easily assess the impact of changes in your BI environment by conducting a thorough lineage and impact analysis. What SSIS packages, SSRS reports, or SSAS cubes will be affected if I change a single column? Now you know!

How many times have you wondered what your environment looked like in the past? With Doc xPress, you can periodically snapshot your environment and compare snapshots over time so you can quickly and easily assess any changes that have occurred. And now you can view the documentation in your web browser without the need for any desktop configuration or installation.

If Doc xPress Server Edition sounds interesting and you’d like more information on the software, Pragmatic Works is presenting a free online webinar on the technology July 30th at 1:00 pm EST! Head over to PragmaticWorks.com (you’ll need to scroll down a screen or two) to get signed up and registered for the event.

Data Validation Via Data Surf

imageIf you’re a developer like me, you’ve probably at some point had to validate data. Validation is often a tedious and boring job that can involve checking individual records and data values in multiple tables. It’s not fun but its a necessary part of our job after all. Recently I’ve discovered the Data Surf feature of DBA xPress which is part of the Pragmatic Workbench DBA toolset.

Data Surf enables me to begin browsing a database beginning with a single table or even a single record. Using that table or record as a start point, I can navigate to other tables related to my initial selection. I can specify that I’d like to view parent or child records of my selected row in a related table, which makes validating data a snap. So I’d like to show you how we can accomplish that.

I’ll begin by selecting the Data Surf feature from the Pragmatic Workbench.

image

Next I specify the SQL Server and Database I’d like to begin surfing. For this example I’ll start with my trust Adventure Works Data Warehouse.

image

Then I’ll select which table I’d like to begin my surf. Reseller Sales it is. You’ll notice if you hover your mouse over the name of the table, you can view the columns with their data types that exist in the table.

image

I can also optionally specify a specific search criteria. If I’d like to view certain records associated with a product, I can do that. This is a very nice feature for data validation.

image

Now I can see my surf’s starting point. I have a quick view of the records in the table as well as a row count.

image

If I click one of the records in the table , some options appear. On the left side of the screen, I can see related parent tables as well as child tables. In the case of the FactResellerSales, there happen to be no related child tables.

image

In the margin, if I click the dbo.DimEmployee table, I can now see the relationship between DimEmployee and FactResellerSales. I can also see the related Employee record based on the record I had initially highlighted in FactResellerSales. Because I had selected a record with an EmployeeKey of 285, when I click DimEmployee I see the record(s) with EmployeeKey 285. Also, by selecting the DimEmployee table, my related child and parent tables on the left have changed.

image

If I click on the DimSalesTerritory table, I can now see the relationship between DimSalesTerritory and DimEmployee. In the record viewer, I also see the DimSalesTerritory record(s) related to the DimEmployee record with EmployeeKey 285.

image

There’s also some other nice features available with Data Surf. I can customize the colors of the nodes in my diagram in order to produce a simple and easy to view ERD.
image

And once you’re done surfing, validating, etc, you can easily save your diagram as an image.

image

All in all, Data Surf is a very simple and easy to use tool that I can see as being very beneficial to the average developer. As a BI developer, I spend most of my time designing data warehouse solutions and having Data Surf in my back pocket is great. Download the free trial at PragmaticWorks.com and check out Data Surf.