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.
Continue reading Power BI Tips, Tricks & Best Practices Webinar Recording & Materials Now Available
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
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.
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
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:
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. 🙂 
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!
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!
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.
If 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.
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.
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.
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.
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.
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.
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.
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.
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.
And once you’re done surfing, validating, etc, you can easily save your diagram as an 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.
So I’m 95% sure that I blogged about this topic at some point over the last couple years, but every time I try to find the link to show a class I’m teaching or to show a client, I can never find the darn thing. This is why I’m writing this blog. That and its also nice to have a good example of this on hand, which is what we have here.
In SSAS we have the ability to create named sets. An named set is basically an aliased set expression that we can use within our MDX queries. This is very useful if we have a set that is commonly used in our organization’s reporting solution.
But there are two types of named sets: static and dynamic. Static and dynamic sets appear very similar but they actually behave very differently, which is why I present to you the following example.
Below you will see a snippet of MDX from my cube script that creates a named set called Top 10 Customers – Static. This is the basic syntax for creating a named set in your cube’s MDX script. You’ll notice the static keyword, highlighted in blue. This specifies that we wish this named set to be static. The static keyword is actually optional, because if we leave the static keyword out of the create set statement, the set will still be created as a static named set.
CREATE STATIC SET CURRENTCUBE.[Top 10 Customers – Static]
AS topcount(
[Customer].[Customer].children,
10,
[Measures].[Internet Sales Amount]
) ;
The next create set statement creates a dynamic named set called Top 10 Customers – Dynamic, the big difference here being obviously the keyword dynamic, highlighted in blue. This specifies that this named set should be created as a dynamic named set.
CREATE DYNAMIC SET CURRENTCUBE.[Top 10 Customers – Dynamic]
AS topcount(
[Customer].[Customer].children,
10,
[Measures].[Internet Sales Amount]
) ;
Here you can see the create set statements in my Adventure Works cube script.
And here we can see the two sets as they appear in our cube’s metadata tab in SQL Server Management Studio.
Now this is where it gets interesting. Below we have an example of our static named set being used in a query on the row axis. And we can see that it works fine.
But what happens if add a constraint in the Where clause? Uh oh, we run into an issue. The static named set does not respect the Where clause (or a subselect statement in the From clause for that matter). The named set displays the same members instead of displaying the top 10 customers from the year 2006. This could be a problem for our users depending on the requirements of the reporting solution.
This could be where a dynamic named set may be more useful. Here you can see an example of a query that uses our dynamic named set.
Except when we provide a constraint in the Where clause, the named set listens to the Where clause and displays the correct data. I know the Internet Sales Amount numbers are all the same but that’s just the nature of the Adventure Works data.
I think this perfectly demonstrates the differences between static and dynamic named sets. Static named sets behave exactly as their name suggests: They are static and do not respect the Where clause or a subselect statement in the From clause. The dynamic named set is dynamic and will listen to a Where clause slicer or a subselect in the From clause.
If this is all a little overwhelming to you and anytime someone mentions using MDX you curl up into the fetal position, suck your thumb, and sob uncontrollably, I would suggest taking a look at the BI xPress calculation builder. BI xPress has a nifty little wizard that will help you create MDX calculations and named sets without you having to do any of the tough MDX writing on your own.
To create a named set with the BI xPress calculation builder, click the little calculate icon in the Calculations tab of the cube designer in BIDS or SSDT. This will open up the MDX Calculation Builder Wizard part of BI xPress.
Choose the Top 10 Count template under the Sets folder and click Next.
On the next screen we can pick the attribute required for our set. In this case, I’ll select the Customer attribute of the Customer dimension in order to create the Top 10 Customers set we were playing with earlier.
Then we select the measure we want to use to rank our customers. I’m selecting the Internet Sales Amount measure.
Lastly we give our named set a name and click finish. On this screen we can preview the MDX the BI xPress MDX Calculation Builder wrote for us.
And we’re done!
The BI xPress MDX Calculation Builder wrote all the MDX for us without us having to know a lick of MDX! Pretty nifty if I do say so myself. For more information on BI xPress or the BI xPress MDX Calculation Builder, head over to PragmaticWorks.com and download the free trial of BI xPress.
And if you have any questions or comments, please feel free to leave a comment or shout out on Twitter @SQLDusty! Thanks!
A Data Dictionary is one of those things that almost all of my customers agree is important but many of my customers never get around to creating one. I think there are a few reasons why creating a descriptive and useful data dictionary is not very high on the priority list for many of my customers. One of those reasons is that creating a data dictionary can often be time intensive. Creating a data dictionary is often a very manual effort that requires some knowledge of the solution. Another reason creating a data dictionary often falls to the back burner is that picking a useful format can be challenging. Should a customer create a SharePoint list, a Word document, a OneNote document, or some other format? And then, of course, there’s the challenge of keeping the data dictionary documentation up to date so that its actually relevant and useful. All of these are very real challenges that many customers face when creating a data dictionary. This is where Doc xPress comes into play.
Many users often want to capture additional attributes as part of their solution documentation. Attributes such as descriptions, example data, SLA’s, business owner, and other attributes are very important nuggets of metadata that would be great to display in the documentation. The Doc xPress Data Dictionary feature allows us to very easily add meta data to our solution documentation that can then be included in any format allowed by Doc xPress. By using Doc xPress, we can add useful business definitions to any item in the documentation and provide useful annotation to our business users in a simple and efficient way.
One of the really nice things about Data Dictionary in Doc xPress is that we have the ability to create mandatory and optional metadata categories that can be scoped to the entire metabase or smaller subareas of a solution. For example, I might create a category called “Business Owner” that would contain the name and contact info of the business owners of the databases included in our documentation. We could also make this a required category which will allow us to analyze our solution in order to determine which objects are missing an entry for this category.
Once our Data Dictionary entries have been added, we can easily view the entries within our Doc xPress documentation.
To download the free trial version of Doc xPress, head over to Pragmatic Works site and take the Data Dictionary feature out for a spin.
SSIS Package Configurations allow us to modify SSIS package properties from outside of the packages. This gives us a seamless way to dynamically point our packages to our development, QA, and production environments without needing to manually open and modify our connection managers within each SSIS package. Package Configurations can be created using a SQL Server table, environment variable, XML files, registry entry, or parent SSIS package, but this post will focus on creating an package configuration using an XML file.
To create the Package Configuration, open an SSIS package in SSDT or BIDS and right-click in the blank area of the control flow and select Package Configurations.
Check the checkbox to enable package configurations and then click Add to add a package configuration to the package.
In the drop down list next to Configuration type select XML configuration file. Browse to the location where you wish to store the XML configuration file and type in the name which you’d like to give your configuration file and hit enter.
And now time for a quick note on best practices and my thoughts on using XML configuration files. SSIS package configurations are most often used to configure Connection Managers from outside the SSIS packages. With this in mind, I prefer to create one package configuration per Connection Manager. I also prefer to name the configuration file the same name as the Connection Manager. In my experience, the 1:1 ratio of XML configuration files to Connection Managers eases maintenance and management of the XML configuration files. If you’re creating XML configuration files to configure other properties of the package such as package variables or task properties, you may find another solution works better for you. That’s just my .02 cents on the matter.
Now we need to specify the property which we wish to configure. In this example, I’m selecting the ConnectionString property of a Connection Manager. Check the checkbox next to the ConnectionString property under the Connection Manager. Click Next. On the final screen you’ll need to give the Package Configuration a name. I give the same name as the XML Configuration File to help things stay nice and neat.
We can see our package configuration has been created. Click close.
Yay! Now your package has an XML configuration file set up to dynamically configure your connection string of the connection manager you selected at run time. All that is left to do is to copy the XML configuration file to each environment the package will be run on: developer machines, development, QA, and production environments. Then you must also update the XML configuration file with the environment’s connection string. Just open the XML file in note pad and update the Connection String to point to the appropriate server and database.
So we’ve set up the package configuration on a package. But what if we need to copy the package configuration to many other packages? Sure, we could complete the previous steps manually on each package, but that would take a lot of time. We can, however, easily accomplish this in a few clicks with BI xPress. In order to complete the following steps you will need to have BI xPress installed. You can download and install a free trial version of BI xPress here.
Shift select all of the packages within your SSIS project including the package that has the package configuration applied as well as the packages you wish to modify. Then right-click the packages in the Solution Explorer and select Package Configurations (BI xPress) to open the BI xPress Configuration Wizard.
Click Next pass the first screen of the Configuration Wizard.
You should see all of the packages highlighted and in the Select Items window. Click Next.
In the following screen you should see your packages listed and the lone package configuration displayed beneath the SSIS package we added the configuration to.
Right-click the package configuration and select copy or simply highlight the package configuration and hit Ctrl+c to copy the package configuration.
Then select each package in the list individually and hit Ctrl+p to add the package configuration to each package.
Click the disk icon to save your changes and we’re done! Our package configuration has been added to each package with much less work than we initially went though.
Just be aware that if package configurations have not been enabled for the packages you added the package configuration to you will still need to complete that step for each package.
For more information on BI xPress just head over to PragmaticWorks.com or head here to download the free trial version of BI xPress. Enjoy!
