I regulary have to load some Excel sheets into a datawarehouse, what happens a lot is that some values in an Excel column may be returned as Null when the underlying value is not a Null. This typically occurs when numeric and text datatypes are intermixed within the same Excel column.
This appears to be a strange behavior of the Excel ISAM driver, when you load a column it reads the first 8 rows and tries to determine what it should load the data values as. Now if your data has a mix of characters and numbers, you need to make sure you reflect this in the first 8 rows or it will not load correctly. The official explanation and workarounds can be found at ms support: http://support.microsoft.com/default.aspx?scid=kb;en-us;194124
UPDATE: another great i found at http://developers.de/blogs/nadine_storandt/archive/2006/12/05/SQL-2005-SSIS-Excel-Source-Insert-NULL-Values-for-all-Records.aspx and you could do the following:
To fix this problem you have to add the following property to the Connection String of your Excel Connection Manager:
Excel 8.0;HDR=YES;IMEX=1
The property IMEX = 1 specifies that all datatypes should be import which are defined in the Excel Source.
My users want their report to default select the last year and month available in the cube, most of the times this wont be the current month as the dataware house is behind. In a previous blog post I talked about creating date time sets like current year, month, this way we can select the last month and year. I want to use these sets as default value.
I found a litte trick to make selecting the default parameter a walk in the park
I created my report as i would usually and created 2 parameters year and month, thus automaticly creating 2 datasets with all the data from year and month attributes.
Now here comes the tricky part, i copied the mdx created by visual studio and added a filter using the set i have in my cube and created a new dataset called CurrentMonth. The MDX ends with “ON ROWS FROM [CUBE]“ and you can change it to ”ON ROWS FROM ( SELECT ( [CurrentMonth] ) ON COLUMNS FROM [CUBE])” using the set which returns the current month. This new dataset returns the current month and the All dimension, i excluded the all dimension in a filter on the dataset.
Now you can use the new dataset to set the default value in the parameter by selecting “get values from a query” and selecting the new dataset and as value ParameterValue.
This will enable you to set the default value on default generated parameters.
You regulary have big reports with a lot of data (e.g. sales of your product by country per city, think adventureworks). To make life a little easier for your report users you can add Bookmarks so they can jump to the country they want immediately. To attain this is very easy using the bookmark action.
In this sample i use the adventure works cube to show internet order count by city, by state provice, by country. Since this is a big list i want users to jump to the country they want to see.
To start i made the report i wished the users to see:
I want users to select the country from a list of country’s on top, add a table and group it on country.
Now to add the bookmark go the the textbox properties of the country field of the list and select bookmark and set country as value:
Now you have to add the destination of the bookmark jump in the data tablix, go the the country field and set the bookmark propertie to the value country:
Now you have bookmarking in place you can add some visual extra’s like page breaks after the country list, making it visible/invisible depending on the number of available country’s (thin when using security).
When you want to want to govern the acces your users have to your data in the SSAS cube’s you have the ability to use Roles to set secutiry on all sorts of levels, from cube level to dimension data.
We want our users to get only acces to different parts of the data, like department managers to only data from sales in their department. When setting security on department levels Autoexists take care of the security on other dimensions and present the reports through reporting services to only show data with the department data selected in the query as well so Autoexists can do its task. This makes usermanagement of roles somewhat easier to maintain. But users who have acces to the cube can also connect a cube through Excel and thus bypass the dimension Departments where we have put our secutiry, to counter this you can set secutiry on all dimensions you want the user to see or make sure the user can’t connect to the SSAS cube you don’ t want want to connect him to. My collegue Marc Valk found out you can add a group policy that disables the “get external data”, ” from other sources” tab in excel:
Want to see a cool example of data visualization? This case study video that shows how the Tampa Police Department used Microsoft Surface to help law enforcement and emergency response crews provide safety, analyze what was going on and communicate better during the Super Bowl. The Super Bowl attracts millions of tourists and the Tampa Police Department was looking for a method of processing incident and event details in a manner that was intuitive and easily understood.
The solution takes advantage of the expression evaluation rules in RS 2008 that have changed as a result of the new on-demand processing model. As a result, state in class-level instance variables is discarded as you page through the report which makes maintaining state trickier between page requests. However, RS 2008 introduces report variables that guarantee one-time evaluation semantics.
1. The report has a EvenRow code-behind function that toggles each time it’s executed.
2. In the Category group (double-click it to access its properties), a EvenRow group level variable is defined that invokes the EvenRow function once per each group instance.
The rest is easy. I set the BackgroundColor property for each textbox to use this variable.
Practical:
Go to report properties and then add the evenrow function to the Code box (just a simple state toggler):
Public _evenRow As Boolean
Public Function EvenRow() As Boolean
_evenRow = Not _evenRow
return _evenRow
End Function
Go to the row group properties you want to alternate, go to variables, add the variable “EvenRow” with the function “=Code.EvenRow()”, this will toggle each time the row is called upon
Now go to the text box properties of the row in the group you want to alternate, go to fill, fill color and add the function “=IIF(Variables!EvenRow.Value=true,”Red”,”Green”)”
Thats it the background color will alternate each time a new group element is started and this is the result:
New in Sql Server 2008 are the integrated Best Practice Design shown as warnings, a lot of times you want to ignore these warnings because you know it isn’t applicable in your situation.
Before you deploy your project, you should clean up – to the extent you wish – the current warnings in your project. Warnings won’t stop your deployment, but you should make a conscious decision whether to ignore the surfaced warnings. A comprehensive list of all warnings in your project can be found in the Error List window (which you can open with Ctrl+E). Double-click on an error to access the designer and fix the problem. Alternatively, you can right-click the error and click dismiss to clear it off the list if you don’t intend to fix it. You can even add a comment to document your reason for ignoring this error. This method of clearing the error is instance-based and will not clear the same error if it’s found in a different dimension or cube. To globally dismiss a particular type of error, whether proactively before you start development or after the fact, you can access the new Warnings tab in the Database editor (which you can open on the Database menu by clicking Edit Database). Incidentally, the Warnings tab also contains a list of the warnings dismissed individually and the related comment.
