| Lorents's profileLorents' BI BlogPhotosBlogLists |  Tools  Help |
Lorents' BI Blog
|
January 02 SSRS: Textbox showing parameter returned System.Object[] or System.String[]I ran into a problem with a report today. The report had a textbox, showing report parameters. One of the textboxs, which earlier showed the period selected in report parametres, now returned System.Object[] or System.Strin[] (depending on how I used the parameter field). I used a bit of time, before I realized what caused the problem in the textbox, which had this expression: = "Period: " & Trim(Parameters!TimeInvoiceDateMonth.Label.ToString) The reason to the problem was that someone had changed the report parameter field to multi-value, and hence the parameter returns an object, and not a string/single value. The solution to my problem was to either to reference a specific item in the object list, like this (where I reference the first object using Label.(0): = "Period: " & Trim(Parameters!TimeInvoiceDateMonth.Label(0).ToString) Or, listing all selected values, as showed in my previous blog: = "Period: " & Replace(Join(Parameters!TimeInvoiceDateMonth.Label,", ")," ", "") In the actual case, I ended up with turning of the Multi-value property, as this functionality was not intended on the time parameter, and hence I used the first expression. Show chosen multi-value paramtere in reportIn one of my Reporting Services reports, I use a multi-value parameter as filter, without including this parameter in rows or columns. When users prints the reports, they need to be able to see all the chosen parameters, and they are printed in a text box. We have a couple of multi-value parameters, and when several members are chosen, al chosen members need to be listed. December 13 Analysis Services 2005 Aggregation Design StrategySQL Cat has published som 'rules of tumb' regarding aggregation design:
There is also a lot of other interesting articles published by SQL Cat. Assigning value to a ReadWrite variable in SSIS Script ComponentWhile building a SSIS job to import a pricelist from Excel, I needed to import 3 different types of information. The Excel sheet contained a cell wit a date, telling from which day the pricelist was valid/effective from. It also contained one cell, telling which currency the prices were stated in. And it also contained a data area, with all prices. The Excel sheet contains named ranges, one for each type of data. I decided to use a Data Flow task for each of the 3 different data types, like this: The EffectiveDate and Currency, I wanted to put into User Variables in SSIS. In my Data Flow task for EffectiveDate, I imported the range 'EffectiveDate' from my Excel sheet. At first me named range 'EffectiveDate' in the Excel sheet only consisted of one cell. I do not if ther is a bug in SSIS, or this is 'work as designed', but I was not able to se the named range in SSIS, until I expanded the range to at least two cells. I therefore expanded the range to two cells: My Data Flow task contains two elements, the Excel sheet as source, and a Script Component as destination. When adding the script component, I choosed 'Destination' I have not been working with User variables in Script Component earlier, and it was not as straight forward as I had expected. Working with User Variables in Script Task in the Control Flow is surprisingly quite different from User variables in Script Component, and when i first tried to use the same methode in Script Component, i got the follwing error 'The collection of variables locked for read and write access is not available outside of PostExecute'. After searching quite a bit on Internet, I finally managed to return a row data to the user variable, using this script:
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Dim vars As IDTSVariables90
Me.VariableDispenser.LockOneForWrite("EffectiveDate", vars) vars(0).Value = Row.EffectiveDate vars.Unlock() End Sub But where you need to add the user variable in the ReadWriteVariables when working with Script Task in the Control Flow, I had to leave the ReadWriteVariables empty when working with the Script Component in DataFlow. Now I was able to get Effective date and Currency into two user variables, and then put it into each line of prices in my fact table, when I loaded the data area with prices.
November 25 Add code completion and get your SQL code nicely formatted with one click for $149!I have in the last couple of months been testing SQL Assistant from SoftTree Technologies, and SQL Prompt/SQL Refactor from RedGate. Those programs provide code completion, format sql code, and have other interesting functions. Even though I have found situations where both of them fails to work properly, it has really been a great pleasure, and time saving, to work with both of them.
After testing SQL Assistant vs SQL Prompt, I had to make a decision on which tool to chose, and I finally chosen SQL Assistant. At the time of writing, SQL Assistant has been released in a 3.0 beta, which has several nice enhancements over the previous version, and now provides the functionality I missed in SQL Assistant 2.5, when comparing SQL Prompt 3.6.
When comparing SQL Assistant 2.5 with SQL Prompt 3.6, I was quite unsure which tool to choose, but when SQL Assistant 3.0 beta arrived, it was much easier to choose between them. Both of them are quite cheap, and the price should not be the primary decision factor.
But with redgate solution, you will need both SQL Prompt and SQL Refactor, if you want the same functionality as SQL Assistant. With SQL Assistant you get code completion and formatting of T-SQL code in one tool, where redgate has split those functionalities in two products.
This gives an advantage for SQL Assistant, as this tool costs $149, and with redgate SQL Compare costs $195 and SQL Refactor costs $295.
Another advantage for SQL Assistant, if you use Oracle or other databases in addition to SQL Server is, that SQL Assistant works for this list of databases:
One advantage of redgate tools is the pallet of SQL Server tools they provide. Other interesting tools are SQL Compare and SQL Data Compare, SQL Doc, SQL Dependancy Tracker, SQL Backup. You can buy the whole suite of programs (SQL Toolbelt, also includes programs not menthioned here) for $1,595 for a single user, $4,995 for 5 users and $8.995 for 10 users.
The bottom line is that this kind of tools saves you for quite a bit of work, and improved the quality of the SQL code you writes. There will be fewer errors in your code, and your code will be a lot easier to read for other users, unless you are an expert in formatting your code yourself.
Lately, I have been working with two different customers, which I have recommended to consider this kind of tools. One of them now has chosen the SQL ToolBelt from redgate, and the other customer has chosen SQL Assistant. The latter one also chooses SQL Examiner Suite 2008 from sqlaccessorie to do comparison of structure and data, between databases. A highly valuable tool when needing to synchronise different environments (development, test, production) partly or in full. Redgate provides SQL Compare and SQL Data Compare for the same purpose.
November 24 Analysis Services Query Performance Top 10 Best PracticesFound this interesting Microsoft article, listing top 10 best performanc tip regarding Analysis Services.
This article also reference two other interesting Microsoft articles:
August 16 BIDS HelperIf you are not aware of it already, have a look at the BIDS Helper www.codeplex.com/bidshelper. Codeplex.com is Microsoft’s open source project hosting web site.
BIDS Helper contains a set of small useful utilities and enhancements for SQL Server BI Development Studio, primarily focused on Analysis Services.
One really great feature is the ‘Deploy MDX Script’, which allow you to deploy just the calculation script, reducing the time to deploy MDX changes to nearly no time. Other nice utilities are Aggregation Manager, Dimension Health Check and Update Estimated Count. The last one is very useful when estimated counts get out of date, and you want to redesign aggregations. Be aware that when you redesign aggregations, existing estimated counts are not updated automatically. If you had a small amount of data first time you designed aggregations in a cube, those numbers will not automatically be updated later. The ‘Update Estimated Counts’ will then be a useful tool to ensure that all counts are updated, which will help the Aggregation Designer Wizard choose better aggregations.
There are also some nice tools to visualize/documenting the cube.
BIDS Helper is open source program, and brings some useful tools for free! May 02 SSAS-Info.com - interesting SSAS siteI did not know this site, before searching some informations on Google, where SSAS-Info.com had one of the links.
If you have not been there already, try to have a look at:
March 21 SSIS: Option Strict On disallows implicit conversion from 'Object' to 'Boolean'In one of my SSIS packets, I had an Execute SQL task which retrieved a Boolean value to a Boolean variable. In a Script task, i needed to check the value of the Boolean variable, but I got an error when I tried to check the Boolean value directly against the variable:
If Dts.Variables("Table_Sale").Value = True..... resulted in the error: 'Option Strict On disallows implicit conversion from 'Object' to 'Boolean'' This error occurs (my best guess) because the Script task automatically converts all variables defined in ReadOnlyVariables and ReadWriteVariables to the type Object. As the Script Object default has the option strict set to on, you are not allowed to compare an object with a Boolean value. I could either convert my variable to type Boolean, or I could set option strict in the General declaration section of the script to Off: Option Strict Off I choose to make an explicit data conversion, and found a list of conversion functions which we can use in VB .NET. CBool - use this function to convert to Bool data type This list I found at StartVBDotNet.com In my SSIS packet, i declared to Boolean variables in the script, and converted the external dts.variables to Boolean, using CBool: Dim TSale As Boolean = CBool(Dts.Variables("Table_Sale").Value) Then I could check if both variables was true like this: If (TSale And CSale) Connecting Excel 2003 to AS2005 - need OLE DB for Analysis Services 9.0If you need to connect Excel 2003 pivot table to SSAS 2005, you need to have OLE DB for Analysis Services 9.0 installed. Excel 2003 will default only have the ‘Microsoft OLE DB Provider fro OLAP 8.0’ installed, and this driver will not work for SSAS 2005. You will find the necessary software for installing this driver on: You will need two components:
(The link for AS 9.0 OLE DB provider is for x86 package. If you need for x64/IA64, you will find the link on Feature Pack for Microsoft SQL Server 2005 - November 2005.) If you download those two components, and install them in that order, you should hopefully be able to se the ‘Microsoft OLE DB Provider for Analysis Services 9.0’ in list of OLAP providers in Excel 2003. It worked for me! March 01 Be aware, using Nonempty without filterset, might give unexpected result.I have just used a bit of time to examine why the result from a MDX query dit not return the data I expected, missing some rows.
I found that the problem arised because we used a Nonempty function, without specifying a filterset. Doing this, the expression was evalueated using the default measure in the cube, not the measures i requested in the MDX. Because of that, I a lot of my data 'disappeared'.
When setting the filterset in Nonempty, data was returned correctly.
I found this blog describing the problem:
February 27 Clear the cache when doing MDX scriptingIt have been very scilent here for a while, so I will try to bring some new articles in near future. First, I will start wit a tip I found at Peter Koller's BI blog, which I have found useful. When tuning MDX queries, it is useful to be able to clear the cache, to avoid the uncertainty of weather a performance improvement is due to caching, or improvements: Citat SSAS Best practice: Clear the cache when doing MDX scripting December 15 Setting default member with MDXSometimes, it can be useful to set a default member in a dimension by code, and even make it dynamically. You can do this with MDX Scripting, or by Stored Procedure. I will not treat stored procedure here. Teo Lachev has an example of using stored procedure to set default member in time dimension, in his book ‘Applied Micrrosoft Analysis Services 2005’. Mosha has written an extensive article about setting default member, where he explains why you can not use MDX expressions in the user interface in 2005, using calculating measures, to set default member: http://www.mosha.com/msolap/articles/cubeinit.htm The logical reason why you can not reference calculating measures in the user interface (UI), is that MDX Expressions form the UI is evaluated before MDX Scripts, where calculated measures is created. Examples (AS 2005) You can set default members in MDX Script (the calculations tab in cube editor) , by using the Alter Cube statement. An example of using the Alter Cube statement is found in the Adventure Works example, where default destination currency is set whit this command: Alter Cube Update Dimension [Destination Currency].[Destination Currency], Default _Member = [Destination Currency].[Destination Currency].[USD; Another example: ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, DEFAULT_MEMBER=[Measures].[Profit]Your time dimension could have the latest month/date with data as default level: Either: Or:
December 04 LastNonEmpty MDXAt the moment, I am working quite a bit with semi additive calculations, and I will use this blog to describe my experiences with this kind of calculations. I will start with a MDX statement, to get LastNonEmpty. In Enterprise edition of SSAS 2005, LastNonEmpty is a built in aggregation function for measures.
I have not been able to have this fuction to work on my Developer Edition, even thou it should, and I have not been able to figure out what I have done wrong. But I have found other people describing the same problem on Developer Edition, and when the solution was moved to Enterprise Edition, it worked. I will try to investigaet this further, to se if this is a bug, or me doing something wrong. I have the CTP SP2 installed, but this does not help.
Instead of this function, I use the following MDX statement:
CREATE MEMBER CURRENTCUBE.[Measures].[LastNonEmptyPrice]AS CASE WHEN NOT IsEmpty([Measures].[Pris]) THEN [Measures].[Pris] ELSE ([Time].[Date].PrevMember) END In this example, I use it to find the latest price for a produc. Prices are only entered when changed, and I need MDX to seacre for the latest price. This MDX will try to look at [Mesures].[Price] for the last time periode, if the current time periode does not have a price. And it will look recursive back in time, until it finds a price. This MDX can also be used in Standard Edition to have a LastNonEmpty function, as the BI Intelligence is only included in Enterprise Edition. Changing database collationToday, i needed to change collation on a database (SQL Server 2000). I had a staging database, where source tables are loaded from source systems. Then I had an archive database, where archived data from the source systems exists. This archive database had been created with another collation, than the staging area. I needed to make union views between tables from the staging area and the archive database, and instead of doing collation conversions in the views, i decided to change the collation on the archive database. I guess there are several ways of doing this. One method is to create a new database with the correct collation order, scripting all the objects, replace the collation by search/replace before deploying the objects into the new data base, and then transfer data from the 'old' database. I also found the article 'SQL Server 2000 Collation Changer' by 'The Code Project' with VB.Net source code to change the collation on all objects. But I was not able to install a program on my customer’s site, so I decided to use SQL to make the necessary collation changes. My archive database did not have any foreign constraints and indexes to worry about, so I ended up running this script: First I altered the default collation on the database. ALTER DATABASE MM_Archive_data COLLATE Danish_Norwegian_CI_AS Then i ran this select statement, which created all the ALTER COLUMN statments i needed: select 'ALTER TABLE ' + Table_schema + '.' + Table_name My archive database had two different collations, and i needed to run the select statements once for each collation. I listed all the different columns with collation different from what I wanted it to be with this select: select * from Information_schema.Columns Be aware, that i only check for varchar and nvarchar in this case. If you need to do any equivalent with char and nchar, you need to add those in the where part. I also had a text field, which I changed manually. The text field does not have a length specified, and I did not care to write more complex SQL to take care of this one field. When writing this script, I found the article 'Using Collations' from Mimer developer useful. December 03 Vista does not run SQL Server 2000/MSDEBe aware that Windows Vista does not support any SQL Server previous to SQL Server 2005 SP2. Se article 'Running SQL Server on "Microsoft Windows Server Longhorn" or Microsoft Windows Vista".
One of the biggest issue might be clients having Windows Vista, trying to use applications using MSDE. December 01 Reporting Services and Analysis Services, having measures on rowsA couple of weeks ago, I experienced that it is not possible in Reporting Services, using Analysis Services, to have measures on rows, or to have multible columns on rows in a matrix report, when using the standard 'Microsoft SQL Server Analysis Services' driver.
It started when I tried to implement consepts from the SQL Server Magazine article Bring Cube Data into Focus at a customer. I needed members from a periodic dimension and also members from another dimension crossjoined as columns. But the only members I was able to get at columns, was my measures.
Trying to have measures on rows, or other dimensions on columns (in my MDX), I got the following error:
After searching for a while on internet, I found the an article/discussion in a news group where Deepak Puri gave an explanation and solution for the problem.
I needed to use the OLE DB provider, Microsoft OLE DB provider for Analysis Services 9.0, to be able to have measures on rows. My first blogHi.
My name is Lorents Nord-Varhaug, and I am woring as a senior consultant at Platon AS, an independant consultancy company woring with Business Intelligence and Information Management.
My primary interests are in Microsoft BI, working with Analysis Services, SQL Server, Reporting Services, and other technologies.
This is my first contribution to my blog, and I will use this blog to share some of my experiences, working with Microsoft BI.
All information, data, text, messages and other materials posted on this web log are the opinion and sole responsibility of me as private person, and not as a Platon employee.
|
Books I am using.
|
||||||||||||
Send a private message
Tell a friend
Add to My MSN
Add to your network
Sign up for alerts
|
|
