Archive for the ‘PragmaticWorks Software’ Category

h1

Reporting on your reports with BIxPress

June 30, 2012

SQL Server Reporting Services (SSRS) is the latest portion of the BI stack to get some attention in the most recent version of BIxPress.  Two new features are included in BIxPress 3.5, Reporting Performance Monitor, and Report Deployment Tool

I’m going to take some time now to walk through how easy it is to get the  Reporting Performance Monitor configured and running.  From the BIxPress start up screen you can see the new reporting icon.

image

Clicking on it will launch the reporting console.  From here the first step is to configure the connection to the report server database. To do this click on the server icon.

image

The connection screen will appear and here you will configure the server, the instance and the database for your report server database.  On my local machine everything is set to the default.  You can also choose windows authentication or supply a Username and Password instead.

image

Once that is done you are ready to start monitoring your reports.  There are a few things that I want to point out, though they might seem a little obvious to most of you.  The Reporting Performance Monitor will only give you details about reports that are deployed to the report server, not reports that are run in BIDS or Report Builder.  With that being said lets take a look at some of the details you can get you hands on.

image

This is the view when you first run the Report Monitor after connecting to the report database.  There are four report:

  1. Report Averages
  2. Longest Running Reports on Average
  3. Frequent Reports
  4. Active Users.

If we click on each report we get a more detailed view. There is a icon on the top right corner of each report that will expand the view.

image

The Report averages chock full of great information. It includes the Last report status, meaning did the report succeed or fail during its last execution, the number of times the report has been run, and its average run time.  Then it breaks down the average runtime into Averages on data Retrieval Time, Processing Time, and Rendering time, so you can quickly see what is the most time expensive portion of your report.

image

The other reports are not quite as dense as the first report but still offer valuable insight into your reporting environment.

image

image

image

There is a ton of useful information here and you can be sure that the brains behind these reports will be adding more to this console as new versions come out.  If you’d like to have your voice and suggestions heard regarding any of the Pragmatic Works software make sure to visit our Feature request page. In the meantime please take a minute and download a trial of BIxPress and test out this cool new feature and the many other features that span the SQL Server BI Stack.

Advertisements
h1

Document SQL Server 2012 with BI Documenter v4.5

May 31, 2012

A while back I wrote a series of blogs on using BI Documenter, and since then there have been some updates to BI Documenter which I believe are worth mentioning. The first new feature, or set of features if you think about it, all of the existing BI Documenter features are now compatible with SQL Server 2012.  This means that as your company migrates to SQL Server 2012 Documenter will be able to make the transition with you, which I think is pretty awesome.

The second feature that I wanted to mention with v4.5 is BI Documenter files can now be outputted to a Word document (.docx). I really like this new feature simply because it’s a file format that so many people are familiar and comfortable with. You can quickly upload the file to a SharePoint document library making it easy to store and share. 

image

The one thing I had been concerned about regarding this feature was, what if I’ve installed documenter on a server and scheduled the snapshot to run from there and I don’t have Word already installed on the server, since it isn’t really needed.  I hate when I have to go and install an application on a server just so I can create the document, even though it will never be consumed there.  Well BI Documenter uses the open xml format, which eliminates the need for Word to be installed on the server to create the document. Furthermore using the open xml format allows other programs that support the .docx format can view the Word document output from BI Documenter. Go and download BI Documenter and test out these features and many more.

h1

BIxPress Now with SQL Server 2012 Support

May 30, 2012

Last week Pragmatic Works launched it’s latest version of BIxPress with some great new features.  At the top of that list of new features was adding support for SQL Server 2012 SSIS Packages.  The Auditing Framework, Notification Framework, and Snippets you’ve come to rely on in your 2008 and 2005 environments are now available in 2012. 

Once you’ve tried out the real time monitoring console from BIxPress, it will change the way you monitor packages in your SSIS environment forever. No longer do you have to watch your package in Debug Mode inside BIDS.  Neither do you have to wait for an error notification to be sent to you if a package fails.  You can view packages that have been deployed to your server as they are running, just as if you had opened them up in bids.

image

You don’t have to worry about conflicts between some of the new features in 2012 and features in BIxPress, because they are either disabled or removed completely in this latest release of BIxPress. 

 

Check out all the new features of BIxPress here.

h1

BIxPress MDX Calculation Builder

April 30, 2012

This weekend at SQL Saturday 130 I gave presented a session on Introduction to SSAS, and after my session one of the attendees came up to me and asked how to create what amounted to a rolling 30 60 and 90 days calculation inside of SSAS.  Since this was an 100 level session, the attendee hadn’t ever seen an MDX expression so walking her through the calculation would have been a bit over her head. This is a common problem for most developers just getting into cube building with SSAS.  MDX is a difficult language to pick up.  Thankfully Pragmatic Works has some software that can help with that. 

When I’ve written about BIxPress in the past it has mostly been about the SSIS features it contains, but SSIS isn’t all it does. BIxPress has an MDX calculation builder that plugs in to the Calculation Tab inside of BIDS in you SSAS project, and its not that difficult to use, meaning you don’t have to write any MDX at all. Yes I am serious and don’t call me Shirley (thank you Leslie Nielsen)

As I said before, navigate to the calculations tab in you SSAS Project inside of BIDS. Once there, and if you have BIxPress installed of course, you will see a little green calculator button has been added to the tool bar.

image

When you click on the icon, a list of available calculations and sets are presented in a new window. These include calculations like Month to Date and Percent of Parent, as well as sets returning Top 10 Count or Top 10 Percent.

image

In this example I’m going to do the Profit Margin calculation, so I select it from the list and hit next.  The first thing I’m asked is to provide the measure that represents the total for Sales.

image

Once that is selected I need to provide the Total Cost.

image

(Notice I haven’t written any MDX yet) The next screen allows me to set any conditional formatting, like text color or Red for Profit Margin of <%15 or something to that effect, but I don’t need that for this example.

On the last screen we give the Calculation an Name and associate it with the correct measure group(Internet Sales Profit Margin, and Internet Sale respectively) and then click finish.  Once that is done, build and deploy the cube, and just like that you have a new calculation in your SSAS cube, and you didn’t have to write a single MDX expression.

image

image

Take a look at this and many other feature of BIxPress by Downloading a trial version here.  Also if you have any suggestions for additional calculations, or other features you’d like to see in BIxPress or any of the Pragmatic Works software products, post them here.

h1

Task Factory Advanced Execute Package Task

April 30, 2012

If you’ve ever developed any kind of SSIS auditing framework, chances are you’ve wanted to pass variables from the child package to the parent package.  As you know it can be accomplished, but it takes some hoop jumping including writing some code in a script task.  As I’ve said before, scripting is not my strong suit, so if there is a way to work around it, I’ll give it a try.

Thankfully Pragmatic Works has made the work around unnecessary with the new component in the ever growing list of SSIS components.  The Advanced Execute Package Task component allows you to easily configure the passing of variables between packages in both directions.

image

The component is easy to configure.  Once you’ve selected the location of the package you want to execute, either SQL Server of File System, the next step is to assign the direction of the variable mapping.  There are three options, read from child package, write to child package, read and write variable from child package.

image

Once that is selected (in this case I’m going to read the variable from the child package) the next step is to select the variable from the child package you want to read.  The nice thing that the Advance Execute Package Task does, is that it only allows you to map variables of the same type. This means that if you choose a string variable in the child package you can only choose string variables in the parent package.

image

Once the variable mapping is set the way you want it, click the add Mapping button.  This adds the mapping to the package and you can now configure another variable.

image

With this component it is very easy to pass variables back and forth between packages.  Don’t take my word for it, download Task Factory for yourself and try it out.

h1

Develop Packages Faster using BIxPress Package Templates: Creating a Template

December 31, 2011

The current project I am involved with has required me to develop packages as quickly as possible while also adhering design standards. The packages had a number of things in common with each other, including data sources, variables and components used to load the packages.  This is the perfect scenario for creating a template to speed up development of SSIS packages.  To create a template you can right click on the SSIS packages folder and select the BIxPress Package Builder Wizard

image

From the wizard specify the New Template from Package option. Give the template a meaningful name for example DimensionLoadTemplate.  Note that the templates are saved as .flow file.  Select the folder you want the template to be saved in.  A great tip I’ve picked up from the other consultants here at Pragmatic Works is to select a shared folder on the network to store any user created templates so that way any developer can have access to the templates.

image

On the next screen select the package you want to create the template from and then click next.

image

The next screen allows you to select which options for every  object in the package you want to set up as configurable by default. The list of configurable objects is on the left

image

Once you select an object from the object list the configurable properties pane is populated with all of the properties for that object.  Place a check mark next to the properties you want to be able to configure when using the template to create a new package. For the OLEDB Source Component I am choosing to make the Name of the component and the SQL Command default configurable.

image

Repeat this for every object you want to configure.  Once you are done with this selection process click next and then start.  Just like that you have now created a Template that you can use over and over again to speed up your SSIS development time significantly.  Try BIxPress yourself and tell me what you think.

h1

Document your SSIS Packages with BI Documenter Part3: Comparing Snapshots

December 1, 2011

In the last two posts we?ve discussed how to use BI Documenter to document your SSIS packages and reviewed the documents that is generated.  Another one of the very useful features of BI Documenter is the Snapshot Comparison. Let us suppose that you have scheduled your snapshots to run on a regular basis, and now you want to compare the different snapshots to view the changes that have been made to your document.  Doing this with BI Documenter is quick and easy.

First thing is to make sure you have 2 snapshots of the same object to compare.  I covered creating snapshots in the first blog of this series check it out here.  For this first comparison I actually didn?t change anything inside the package because I wanted to see what it looked like without any change.  To compare the snapshots launch BI Documenter and choose the appropriate solution.  From inside the solution click on the hyperlink “Compare Snapshots?

image

 

 

The new screen will pop up and you will tell the wizard which snapshots you want to compare and simply click the start button and your comparison will begin.

 

image

 

BI Documenter makes the comparison very easy to read.  As you can see above the color coding on the compared objects makes it easy to quickly identify which objects are New, Modified, have been deleted etc. . . Even though I didn?t change anything inside the package I still received a Modified indicator between the two snapshots on my StartTIme Variable.  Lets take a closer look at that.

image

 

The first thing item indicated as modified is the variable objects, and if we drill down into the variables we see that only one variable has been modified, the strStartTime variable.  We can drill down into the variable and see that the Variable value has been changed.  Lets take a look at why the variable value has been modified.  I didn?t change the expression at all, but the expression uses a system variable that captures the start date of the package, and since that is evaluated when the snapshot was taken the actual value was different for the two snapshots.

Now lets see what happens when I intentionally modify the package.  Lets delete one of the connection managers.

image

Just like that the new snapshot comparison makes it easy to differentiate the objects that have been changed or deleted from the ones that have gone untouched.  Go ahead and test out this feature by downloading the software here.  Tell me what you think of the software.  Be sure to check out my next blog entry when I review the impact analysis/object lineage features of BI Documenter.