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.

SQL Saturday #298 Session Material Now Available (SSAS Tabular Models)

Another fantastic SQL Saturday event is in the books and it did not disappoint! There was a great crowd at University of North Florida in Jacksonville, FL and plenty of amazing sessions and speakers. Thank you to everyone that attended my session! I hope you enjoyed the session as much as I enjoyed presenting. It was a blast!

If you’re interested, the slide deck I used during the presentation is now available for your viewing pleasure, which you can see here.

If you have any questions or would like to contact me, you can always send me a message on Twitter or email me here. Again, thank you to all of my attendees!

Learn to Design Tabular Models at SQL Saturday #298 Jacksonville, FL on 5/10/2014

I’m excited to announce that on Saturday May 10th in a couple Saturdays I’ll be speaking at SQL Saturday 298 in Jacksonville, FL! SQL Saturday in Jacksonville always has an awesome turnout and I love speaking at this event! There will be tons of great free training sessions from the likes of Devin Knight, Robert Cain, Andy Warren, Adam Jorgensen, Max Trinidad, and tons of other great experts! If you’re in the North Florida area on May 10, you need to get registered for this event!

The session I’ll be delivering is called What the Tabular??? and will start at 1:15 PM EST on Saturday, May 10th! If you’re interested in learning how to build a Tabular Model, how to follow Tabular Model design best practices, and how to decide if a Tabular Model is the right solution to your business problem, then my session What the Tabular??? is for you! It’s a great beginner sessions and will feature tons of demos and walkthroughs of the technology!

So if you’re nearby, definitely get signed-up for this awesome event! You won’t regret it!

SSAS Static Named Sets Vs. Dynamic Named Sets

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.

image 

And here we can see the two sets as they appear in our cube’s metadata tab in SQL Server Management Studio.

image

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.

image

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.

image

image

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.

image

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.

image

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.

image

Choose the Top 10 Count template under the Sets folder and click Next.

image

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.

image 

Then we select the measure we want to use to rank our customers. I’m selecting the Internet Sales Amount measure.

image 

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.

image 

And we’re done!

image 

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!

Recording Now Available For The Webinar, Choosing The Right Analysis Services: MOLAP Vs. Tabular

image

Thanks to everyone that attended Devin’s and my webinar called Choosing The Right Analysis Services: MOLAP vs. Tabular. I’m pleased to announce that the recording is now available to watch for free over at PragmaticWorks.com, so please go check it out. It’s a little less than an hour so you can watch it during your lunch break.

Also, the PowerPoint slide deck Devin and I used during the webinar is also available for viewing now! Please visit this link to download the slide deck.

Now for the questions! Many of you asked some great questions but unfortunately we ran out of time to answer all of the questions during the webinar. So here are a few of the questions we didn’t get to.

Q: How do i link if column have more than one column is key column in tabular?
A: If you need to create a composite key in a Tabular model table, you will need to create a calculated column that concatenate the columns that make up your composite key. You’ll need to do this in both tables you wish to relate. Once you’ve done that, then you can create the relationship between the two tables using your new columns.

Q: Can DAX be used to access cubes?
A: In the SQL 2012 SP1 CU4 release, DAX support for multidimensional cubes was added, so as long as you are running on SQL 2012 SP1 CU4 or later, you should be able to query cubes with DAX expressions. On a side note, MDX can also be used to query a Tabular model.

Q: Since tablular solution is many ways better than Muti Dimensional..then my question is when to go for Multi dimensional solution
A: This is one we covered extensively during the webinar. Here are some of the things to consider:

  1. How much data are you dealing with? If its too much to fit into memory for your Tabular model, then MOLAP is the way to go.
  2. Do you have a need for complex relationships? If so, MOLAP may be the answer. Role playing dimensions and many-to-many relationships are possible to create in a Tabular model, but they’re easier to create and manage in a MOLAP cube.
  3. Do you need to perform many complex calculations involving complex Scope assignments? If so, MOLAP is the answer here.

Q: Can you use a Multidimensional database as the source for a Tabular model and improve performance when creating low level granular reports?? This goes back to the performance differences between Multidimensional vs Tabular when creating granular reports.
A: You can use a Multidimensional database as a data source for a Tabular model, but I would suggest getting the data from the original source for the tabular model. If granular type queries are slow against your cube, those same queries are still going to be slow when you execute them to process your Tabular model.

Thanks to everyone that attending Devin’s and my webinar! If you have any other questions, please feel free to leave a comment or send me a message on Twitter!

SQL Server BI Consultant, MCITP

Follow

Get every new post delivered to your Inbox.