MDX YTD Calculation Issus

If you are using the templates for MDX calculation built into SSAS 2008 you might run into some strange issues. In the image below you can see the calculation is working for all years except for 2008.

clip_image002

My first thought was data issue. After querying the table and finding no issues I returned to the MDX calculations in the cube and started trouble shooting. Here is what I found. The format string is set to standard when you drag over the Period to Date template.

clip_image004

But the word standard does not have double quotes around it. If you click on the drop down menu and select standard it has the double quotes around it.

clip_image006

This fixed my problem.

clip_image008

Posted in mdx, SSAS, Syndication | Tagged , | Leave a comment

MDX Scope with an If Then statement

The scope statement in MDX is great for applying calculations to a certain area of a cube, also called a subcube. Dustin Ryan wrote a great article on the scope statement here. The issue I am going to cover will be using the scope statement when you only want to cover all but one member of a dimension.

The normal scope statement would be:

Scope([Referral Type].[Referral Type Desc].members,[Measures].[ER to IP Admits]);

This = ([Measures].[ER to IP Admits] * 0);

End Scope

And this works great and causes the measure to become zero for all members in this dimension. The problem is there is one member in the dimension where I do not want it to be zeroed out.

Here is the member:

[Referral Type].[Referral Type Desc].&[ER TO IP]

This member needs to keep the measure at its original value. To accomplish this I place an “If Then” statement around the “This” part of the scope statement. If you are doing this in Analysis Services in the calculation tab, you will click the new script command button and enter the scope statement from above.

image

Below you can see the calculation screen before you save and close the cube.

image

After you create the Scope statement you can save and close the cube and the script will be broken into three different sections in the script organizer as seen below.

image

Then click on the second section of the scope statement (the one that contain “This =”). Then add the “if then” statement around the “This=” section. Makes sure you add the “End If” at the end. Below you can see the middle section of the MDX scope statement. I do not want this to be applied to the member “ER TO IP”. So I placed a “Not” in front of the Boolean expression.

If not([Referral Type].[Referral Type Desc].currentmember is [Referral Type].[Referral Type Desc].&[ER TO IP]) Then

This = ([Measures].[ER to IP Admits] * 0)

End if

Notice how I used the “is” comparison. This is faster than using equal to because SSAS does not have to convert the values to strings. Now the Scope will be applied to all members of the dimension except then one checked for in the “If then” statement. If you want to do the opposite, remove the “Not” from in front of the “if then” statement.

Here are the final results in the cube browser.

image

Posted in mdx, SSAS, Syndication | Tagged , | 2 Comments

Loop Through Excel Files in SSIS

You can loop through excel files using SSIS. This will use the For Each Loop container and a data flow task.

clip_image002

First Create a variable named strExcelfile as a string variable; you can leave the value blank.

clip_image004

Next, drag in a For Each Loop. Set it to For Each File, and point it to the folder where the excel files exist and type .xls or .xlsx for the file type. In this example the excel files are in c:\test\excelfiles. There are three files named USCustomers1.xls, USCustomers2.xls, and USCustomers3.xls.

clip_image006

Next, drag in a data flow task and drop it in the loop container. Open the Data flow and drag in an Excel Source. Set the Source to one of the excel files in the folder above. This will set the column names and the metadata for the files. Each file in the loop must have the same lay out. If they have different column widths are data types then you cannot use this technique.

Now you will map the excel file to the connection. Click on the Excel connection in the connection manager. This was created when you created the Excel source in the Data Flow. Click on Expressions in the properties windows and open the expressions editor for the Excel connection manager. Select the File Path property and drag in the strExcelFile variable.

clip_image008

One Last step is to set the data flow to delay validation. This is so the data flow will not check for the excel file until after the file name is loaded into the variable.

clip_image010

Posted in SSIS, Syndication | Tagged | 17 Comments

Junk Dimensions with no Loading Needed

When you are working with dimensional modeling there are some situations where several dimensional attributes don’t make since to be in any other dimension. When this occurs you can combine them into one dimension call a Junk Dimension.

In this example you will see a person type dimension that only made since to be in a dimension together. This client has 4 different types of people in their system. The junk dimension will contain each possible combination of these person types.

To avoid having to reload the fact table every time a person type is added or changed, you want to add the logic to the views. You should always use views as your source for the DSV in your cube.

The Junk Dimension query look likes so:

Select 100000 PersonTypeSK, ‘Non A’ A, ‘Non ‘ B, ‘Non C’ C, ‘Non D’ D

Union all

Select 100001 PersonTypeSK, ‘A’ A, ‘Non B’ B, ‘Non C’ C, ‘Non D’ D

Union all

Select 100010 PersonTypeSK, ‘Non A’ A, ‘B’ B, ‘Non C’ C, ‘Non D’ D

Union all

Select 100011 PersonTypeSK, ‘A’ A, ‘B’ B, ‘Non C’ C, ‘Non D’ D

Union all

Select 100100 PersonTypeSK, ‘Non A’ A, ‘Non B’ B, ‘C’ C, ‘Non D’ D

Union all

Select 100101 PersonTypeSK, ‘A’ A, ‘Non B’ B, ‘C’ C, ‘Non D’ D

Union all

Select 100110 PersonTypeSK, ‘Non A’ A, ‘B’ B, ‘C’ C, ‘Non D’ D

Union all

Select 100111 PersonTypeSK, ‘A’ A, ‘B’ B, ‘C’ C, ‘Non D’ D

Union all

Select 101000 PersonTypeSK, ‘Non A’ A, ‘Non B’ B, ‘Non C’ C, ‘D’ D

Union all

Select 101001 PersonTypeSK, ‘A’ A, ‘Non B’ B, ‘Non C’ C, ‘D’ D

Union all

Select 101010 PersonTypeSK, ‘Non A’ A, ‘B’ B, ‘Non C’ C, ‘D’ D

Union all

Select 101011 PersonTypeSK, ‘A’ A, ‘B’ B, ‘Non C’ C, ‘D’ D

Union all

Select 101100 PersonTypeSK, ‘Non A’ A, ‘Non B’ B, ‘C’ C, ‘D’ D

Union all

Select 101101 PersonTypeSK, ‘A’ A, ‘Non B’ B, ‘C’ C, ‘D’ D

Union all

Select 101110 PersonTypeSK, ‘Non A’ A, ‘B’ B, ‘C’ C, ‘D’ D

Union all

Select 101111 PersonTypeSK, ‘A’ A, ‘B’ B, ‘C’ C, ‘D’ D

You will notice the Person Type key look similar to a binary number, and that is exactly what it needs to be. The 10,000 added on is to ensure it is an integer with all the numbers showing even with all the person types at zero.

Now the view for the fact table can be altered to use this dimension right away, without having to reload it. This is because you can tell which person type a person is based on the measures. If the person is missing a measure in that person type field then they are not that person type and vice versa.

Here is the part of the fact table query that calculates the person type key:

100000+

(Case when Person A Measure IS null Then 0 else 1 end) +

(Case when Person B Measure IS null Then 0 else 10 end) +

(Case when Person C Measure IS null Then 0 else 100 end) +

(Case when Person D Measure IS null Then 0 else 1000 end)

as PersonTypeSK

Now you can add the person type dimension to the DSV and you cube. Don’t’ forget to create the relationship in the DSV.

Posted in Dim Model, SSAS, Syndication | Tagged | Leave a comment

Handling Large Many to Many bridge tables

In some scenarios you will need to create a many to many relationship in your cube in SSAS. One of the problems that arise from many to many bridge tables are the size of these tables. In this example we have a fact table that contains the history of a person and measures for that person at a bi weekly basis. Each person can be in a Program. Each person can be in more than one program at a time also. We have thousands of people so the fact table contains about 8 million rows.

Typically a bridge table will have a surrogate key for the dimension and a surrogate key to the fact table or a degenerate dimension based on the fact table. The key on the fact table in this case is the person id and the date key. So to create a many to many based on this key would be 8 million rows times the number of programs a person is in. There are over 300 programs, and some people are in as many as 6 programs at one time. This would make the bridge table over 24 million rows. This would also hurt performance quite a bit. I will admit that in some situation this type of bridge table cannot be avoided, but not here.

To create a bridge table that is much smaller all we need are all of the program combination that occur in our source data and come up with a unique key for each of those combinations.

The first step in this process is to look though the source data and determine all of the program combination. Each person has an program open date and close date. The fact table is at a bi weekly granularity, so we will check the programs at the same granularity.

Here you see an SSIS package that will loop through each date in the past bi-weekly and determine which programs people were in at that time. This package is loading all history. If you are running your package on a nightly basis then you will only need to check the current date after this history is loaded.

image

Here is the data flow in this package.

image

The query to get the programs is below, The date is hard coded here but this would be either getdate() or a variable for the loop getting history.

With Programs as (

Select Distinct f.DimDWPersonKey, f.DimProgramKey

From FactSAMISPerson f

Where f.DimDWPersonKey > 0 and

f.EpisodeOpenDate <= ‘2007-01-01’ and

isnull(f.EpisodeCloseDate,’9999-01-01′) > ‘2007-01-01’)

Select ‘a’ + convert(varchar(3),ROW_NUMBER() Over(partition by DimDWPersonKey Order by p.DimProgramKey)) RowNum,

p.DimDWPersonKey, p.DimProgramKey

from Programs p

Order by p.DimDWPersonKey, p.DimProgramKey

The pivot transform pivots on the person id. If you need to know how to use the pivot transform check out this article. So the incoming data looks like the following image:

image

The data after the pivot will look like the following image. Notice there is a person with two programs.

image

The reason for having 20 columns is to handle any situation where a person is in up to 20 programs at one time. This is way over kill because the highest in our data is 6 programs at a time. But you want to ensure that you can handle increases in the future.

The aggregate groups on all 20 program columns to eliminate duplicates. We do not need the person Id any more at this point so it is dropped. The lookup check the program combo table for duplicates. This ensures a unique id for each program combination and no repeats. Here is the program combo table.

image

Now every existing program combination is on this table and it has a unique id. The next step is to load the fact table and do a look up on this table to get the unique id. This is done using the same query above to load the program combo. Now you just need to do it for each person for each date you are loading in to the fact table. In the image below you can see this part of the fact table load. It works just like the package above, but this time is mapped to the incoming person id writing to the fact table. The lookup then gets the unique id by comparing all 20 columns in the query to all 20 columns on the program combo table.

image

The last step is to pivot the program combo table to create the bridge table. Here is the view used to do that.

SELECT ProgramComboID, ProgramSK

FROM (Select ProgramComboID, a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14, a15, a16, a17, a18, a19, a20 from ProgramCombos) as p

Unpivot (ProgramSK for id in (a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14, a15, a16, a17, a18, a19, a20 )) as s

Here are the results of that query.

image

Now you can add this view as the bridge table in your DSV for SSAS and create the relationships. This made the bridge table only 7404 rows. Much better than the 24 million we would have done with a traditional bridge table load.

Posted in SSAS, SSIS, Syndication | Tagged , | 1 Comment

Faster Video Learning

If you are watching video to learn things like T-SQL or Business intelligence, you can spend a lot of time watching these videos. There is a way to speed up your learning. Just speed up the video.

In Windows Media Player 12 right click in the video and select Enhancements and then select Play Speed Settings.

clip_image001

Then change the play speed to a higher speed like 1.5. If the person speaking in the video speaks real slowly, then you can bump the speed up even more. clip_image002

You can watch a 3 hour video in 2 hours at 1.5 speed

Posted in Business Intelligence, Community | Tagged | Leave a comment

Execute SSIS Packages with a Macro in BIDS

When running SSIS packages in BIDS it is common to click on the green arrow at the top of BIDS to run a package.

clip_image001

One problem that can occur when using this button is the deployment of project and the creation of the deployment manifest file. If you have turned on deployment for the project under the Project Properties then this green arrow will now run the package and deploy.

clip_image002

To avoid this issue you can right click on the package in the solution explorer which will only execute the package.

clip_image003

This is inconvenient for me, because I place the solution explorer behind the properties window. I do this because there are a lot of properties and it is easier to find the properties I am looking for when the properties window is stretched out.

clip_image004

To give you the ability to launch a package from the toolbar without deploying you can create a macro. It is very simple and only takes a minute to create it.

1. At the tops of BIDS click on View > Other Windows > Macro Explorer

clip_image005

2. In the Macro Window right click on Module1 > New Macro

3. Change the Sub Name to ExecutePackage()

4. Enter the following Code:

DTE.ExecuteCommand(“ProjectandSolutionContextMenus.Item.ExecutePackage”)

clip_image007

5. Save and Close the Macro Editor

6. Close the Macro Explorer

Now you have created the Macro that will execute a package. The next step is to add it to the toolbar.

1. Click on the toolbar drop down menu at the end of the toolbar where you want to add the macro and select Add or Remove Buttons > Customize

clip_image008

2. Click on the Commands Tab and select Macros from the Categories list

clip_image009

3. Drag the Execute Package Macro onto the toolbar where you want the button to appear

4. Right click on the new button and select Change Button Image, Select the image of your choice

clip_image010

5. Right Click on the new image in the toolbar and select Text Only in Menus.

Now you should have a new button on the toolbar. This macro button will launch the package that is currently focused on in BIDS.

clip_image011

You cannot use this button if you already have a package running in debug mode. You will see an error if you do this.clip_image012

Posted in SSIS, Syndication | Tagged | 1 Comment

Environment Variables in SSIS Packages and Configuration Tables

Configuration tables are a best practice in just about any SSIS environment. They make it easy to update multiple packages from a single change. But one of the issues with configuration tables is the location of the server is different on each server. You may have a Server name of Dev on your development server and a Server name of Prod on the production server. This has to be updated in the package before the package can be moved to the new server. If you have several configuration tables then you have to update several files. This can be time consuming and tedious. Environment variables can take away this work and make the transfer from server to server easy.

Environment variables can hold the name of the server on each server. In the Configuration Manger you place in the name of the Environment Variable. The Environment Variable does not hold the value of the variable or connection that is passed to the package. The environment variable holds the name of the server. This value tells the package where to look for the configuration table and reads the configuration table for the configured values to pass into the package.

You can think of the Environment Variables as pointers for the package. When you move a package to another server it will look for an Environment Variable. It does not matter on which server your package is running. As long as the server has an Environment Variable named the proper name and it contains the name of the proper server, the package will run properly.

You will now walkthrough a small example of building a package with a configuration table and with environment variables.

1. Open BIDS (Business Intelligence Development Studio)

2. Click File>New>Project

3. Select Integration Services Project

4. Named the Project Environment Test

5. Click OK

6. Right Click on the SSIS Packages Folder and click New SSIS Package

7. Change the Name of the Package to EnvironmentVar

8. Click yes to change package object as well

9. Right Click on the Control Flow of the package and select Variables

10. Create a String Variable name strServerName

11. Set the Value of the strServerName to “Localhost” (You local server name)

image

12. Close the Variable Window

13. Drag in a script task to the Control Flow

14. Double Click on the Script Task to open the Editor

15. Set the Read only Variables property to strServerName

16. Click Edit Script (Design Script in SQL 2005)

17. Replace the “Add your code here” comment with the following VB code

MsgBox(Dts.Variables(“strServerName”).Value)

(This will cause a message box to appear showing the value of the variable)

18. Save and Close the Script Editor

19. Click Ok in the Script Task Editor

20. Debug the Package

21. A Popup with the word “Development” will appear

image

22. Click OK in the popup box

23. Stop the package from debugging

Now you will create a two set of configuration files on your local machine. You will need to have two folder locations from which you can read and write. I have created two locations on my machine: C:\ConfigQA and C:\ConfigProd. These would represent the QA and Production machine on different servers. In your business environment you may have mapped drive or UNC paths on different servers. We also need to create an Environment Variable on your local machine. Each operating system is different on how to get to the Environment Variable Screen. The following are the instructions for Windows 7. Open the Control Panel, click on System and Security, click System, click Advanced System Settings, click on the advanced tab, and click the Environment Variables Button.

image

24. Create a New Environment Variable called ConfigLocation

25. Set the Value to C:\ConfigQA

26. Click Ok and return to BIDS

image

27. Right Click in the Control Flow and Select Package Configurations

28. Place a check next to Enable Package Configurations

29. Click Add

30. Click Next (If the Welcome window Appears)

31. Leave the Configuration Type to XML Configuration File

32. Place a dot in the Radio Button next to “Configuration Location is stored in an Environment Variable”

33. Select the ConfigLocation Environment Variable(You may need to restart BIDS for it to Show)

34. Click Next Finish and Close

35. Click

Posted in SSIS, Syndication | Tagged | 4 Comments

Custom Navigation controls in SharePoint 2010

There are several ways to create a custom navigation control in SharePoint. In the below image you can see the left navigation is hidden. This is done with the use of a master page and some custom CSS. Now you want to create your own list of links to other pages and content that you control. Notice this page is not as wide as the default SharePoint site. If your client has smaller monitors throughout the company this may be necessary as in this case.

clip_image001

The first step is to create a new web part page with a left column available.

Click on Site Actions > Edit in Share point designer.

clip_image002

Click Web part Page on the top ribbon and select the desired web part layout. In this example we are selecting the one with a left column that spans the page.

clip_image003

Give the page a name and click ok.

clip_image004

After this the page will show up on the site pages.

clip_image005

Right click on the page and select properties. Copy the URL. Open your browser and paste in the URL you just copied. Then click Site Actions > Edit Page

clip_image006

On the left column click on add a web part. At the top you will have lots of choices for the type of web part.

This first option we will look at is the list. Select List and Libraries, and let’s say you want to show dashboards in your navigation control, Select Dashboards.

clip_image007

This will add the dashboard list to the web part as seen in the below image.

clip_image008

This is not a good layout due to the width and the unnecessary columns. You can change the look of the web part in the editor. In the top right there is a small black arrow that appears next to the check box. Click on this down arrow and select Edit Web Part.

clip_image009

In the top right you will see the Menu in the following image. Click on the link below the first drop down menu named Edit the Current View.

clip_image010

Here you can change the columns that are showing in this web part. In this menu uncheck all of the columns except the Name Column. Click ok and this will take you back to the page out of edit mode.

clip_image011

Now you can see the list of dash boards on the left. If you deploy any new dashboards they will automatically show on this page.

The next method to add a list of links is the manually way. This gives you more flexibility and lets you leave out some dashboards if you want to. But this is more time consuming and is a lot more work to maintain. However, this method does give you more tools to use.

You are going to add a web part to the left columns just as you did before in the Edit Page mode by clicking Add a Web Part in the left column. In the list at the top you will select Media and Content and then select Content Editor and click add. This gives you an HTML editor in the web part on the SharePoint site.

clip_image012

In the Content editor in the web part click the link Click here to add new content.

clip_image013

Now you can type directly in the content editor any text you would like. You want to add links to the web part so click on the insert tab at the top in the ribbon. Select Link and from SharePoint. This will give you a menu of all of the site content. In this case you want to add the dash board link so click on the dash board folder and select the dash board you want to show.

clip_image014

In the window that appears select the dashboard or page you want to show and click ok.

clip_image015

You will see the link in the content editor now.

clip_image016

There are still a couple of things you can do to clean this up. First we want to take the aspx off the link. To do this click on edit HTML source under the format text and HTML menu.

clip_image017

This brings up the HTML editor where you can alter the links. Delete the .aspx from the link. Make not to alter the href section of the HTML. Then click ok to return to the web part.

clip_image018

The next change you will make is to the name of the web part. The default name is content editor. To change this click on the small drop down menu on the web part and select Edit Web Part.

clip_image019

The web part editor will appear in the top right. Expand the Appearance section and change the name from content editor to Dashboards then click ok.

clip_image020

You have completed adding link columns to the web part page now. One way is using a list that automatically updates but you don’t have much control. The other you have complete control but you will have to manually update it when you add new pages.

Let me know if you have any questions.

Posted in SharePoint, Syndication | Tagged | Leave a comment

Custom Coding in SQL Report Services

Reporting Services 2008 is a very powerful tool for creating reports. It contains many different tools and even allows you to place complex expressions on reports in every field. Even with all of these tools there are some instances where you need more functionality. This is where custom coding comes into play. You can write your own custom VB code on a report and use it anywhere on the report.

Before we start writing custom code let’s talk about some of the limitation of the custom code option in SSRS. The custom code allows you to reference most of the .NET framework. You are limited to working on the report environment though. You cannot use the custom code to write files and delete directories. This is done by design for security. You can reference items like System.Drawing and Microsoft.Office.Tools.Common. These tools allow you to extend the abilities of Reporting Services 2008 beyond the limitation of expressions.

The expression language built in to reporting services 2008 allow for a lot of customization of your reports. Custom code not only allows you to extend the reporting ability, it also makes it easy to reuse code.

We will now walk through building a report in Reporting Services 2008. In this example we will use the adventure works data base which is freely available on codeplex. This walkthrough will assume you already have some knowledge of reporting services and understand how to create data sources and datasets.

1. Create a blank report (do not use the report wizard).

2. Create a data source connecting to AdventureWorks2008.

3. Create a dataset with the following query.

SELECT Name, ProductID, Color, ListPrice, StandardCost

FROM Production.Product

WHERE (ListPrice > 0) AND (Color IS NOT NULL) and StandardCost > 0

ORDER BY StandardCost,ListPrice

4. Drag in a table and drop each column from the query on the table in the order of the query.

5. Set the format property of Standard Cost and the List Price to “C” (Currency).

6. Set the background color of the header row to a light blue color.

7. Set the header row to bold font.

8. Preview the report. It should look similar to the figure below.

image

A very common request is to format the currency or other measures on a report to be red if they are under or over a certain value. This can be done with expressions and we will do that first. We are going to format the currencies to red for a certain value, then a darker red for the next set of values, then black for everything else.

9. Go back to design view

10. Set the color property of the list price column to:

=iif(Fields!ListPrice.Value <20,”Red”,

iif(Fields!ListPrice.Value >=20 and Fields!ListPrice.Value < 30,”Maroon”,

“Black”))

11. Set the color property of the list price column to:

=iif(Fields!StandardCost.Value <20,”Red”,

iif(Fields!StandardCost.Value >=20 and Fields!StandardCost.Value < 30,”Maroon”,

“Black”))

12. Preview the report. It should look like the figure below.

image

Notice the three different colors in the list price and the standard cost columns. The “nested if” statements allow you to set the color to multiple colors. The red indicate the lowest numbers, the darker red indicate the next level and the rest of the values are black. This works great on a financial report in showing which values are lower and higher at a glance.

The downfall of this approach is the fact that we have to write duplicate code. In the future when management decides to change the value you will have to update two different expressions on this report. We can write all of this code in the report custom code. We can use the custom code anywhere on the report also. This gives us just one place the update the report.

13. Return to design view

14. On the text tool bar across the top click on Report>Report Properties >Code

15. Type in the following code:

Function GetColor(ByVal amt As integer)

If amt < 20 then

Return “Red”

ElseIf amt >= 20 and amt < 30 then

Return “Maroon”

Else

Return “Black”

End If

End Function

16. Change the expression on the color property of the list price column to:

=code.getColor(Fields!ListPrice.Value)

17. Change the expression on the color property of the standard cost column to:

=code.getColor(Fields!StandardCost.Value)

18. Preview the report. It should not have changed from the last figure.

Notice the custom code is in VB. This is a function you create that is now called using the code function in the expression editor. You type the word code followed by a period and the name of the function. You have one value being passed into the function. It is the value of list price or standard cost. Now if the value of one of the levels change you can update the value in the custom code and the both columns will be updated. Now the report can be updated very easily. This is a small example. Imagine a report with dozens of columns that need to be updated. This method can significantly reduce maintenance time in the future.

Posted in SSRS, Syndication | Tagged | Leave a comment