 | | |
| | |
|
Spaces home  Steffo´s SQL Server BI ...ProfileFriendsBlogMore |  Tools   |
|
Steffo´s SQL Server BI BlogJune 26 Funny behavior in ProClarity Desktop ProRan across some pretty funny behavior yesterday related to the fact that ProClarity Desktop Professional(PDP) is a non-unicode application(i presume that that is the reason for it). In a cube that has data that can contain Czech characters it can´t differentiate between the following two keys :
Both members appears as 123456789PR in the Setup Panel(check out the image). And when you select the two of them it really behaves funny and combines the name to one row, but still has two cells for the measure. I am not sure if is a Czech character, but PDP can´t handle it anyway. Just another reason for using surrogate keys.
May 22 Roles and redeploymentI have had a bit of a problem with the issue outlined in this KB article. Since I am deploying the same project to 15-20 catalog(different connectionstrings/sql databases) doing it by hand get a bit tedious and boring. And all user permissions are handled on the server and not in the project(does not contain any roles at all). However, by combining what is written in this blog post by Thomas Kejser and some use of AMO I have it going automatically.
The key is to use the Scripter class in AMO, and script all the DatabasePermissions, CubePermissions and DimensionPermissions(maybe I forgot something but this is the security settings I use). The code is shown below(not the prettiest but it works for me and the blog editor messed up the indentations):
Server oServer = new Server(); oServer.Connect(sServer); Database oDatabase = oServer.Databases.FindByName(sDatabase); if (oDatabase != null) {
} Now you can create a .bat file that does it all. First script out all the security for a catalog/database into a file. Then do all the AS Deployment stuff. Then use ascmd to run the xmla file with the security settings. It is probably not foolproof, but it has been working for me at least. March 17 ProClarity Desktop Professional and Cyrillic charactersI used ProClarity Desktop Professional (PBR) against a cube with Cyrillic characters a while back and it did not go so well. And there were no difference with 6.3. The Cyrillic characters show up as question marks (?), like this CSK ??????. And it is only in ProClarity it does not work, EXCEL 2003, EXCEL 2007 and Reporting Services works fine. Have tried changing the collation on the cube, but that did not work. And Western European, Eastern European and Baltic character set works fine, so it is only Cyrillic that gives me a problem (never tried any other character sets so it may be more than Cyrillic that comes out like a ?). One thing I haven´t tried is to run it on a operating system that has a Cyrillic character set, which might fix the problem. Any ideas? March 13 Proclarity 6.3 and Business ReporterOne irritating thing with Proclarity 6.3 is that the Proclarity Business Reporter (PBR) has been removed. The suggestion is probably that you should use Excel 2007 instead. But then you have to install Excel 2007 for all clients, migrate all PBR excel sheets to 2007, educate users etc. etc. before you deploy Proclarity 6.3. And in some cases you must maintain both a Excel sheet and a Briefing Book. Of course I understand that they want people to move to Office 2007, but it would have made my life easier if they had waited one more release before removing it. March 12 Browsing dimensions in ProClarity performance - again!!I have tried ProClarity 6.3, and of course I checked if they had done anything to fix the queries that are sent when browsing a dimension. And of course they had. Now the following query is sent :
SELECT { SUBSET( ADDCALCULATEDMEMBERS( {[Customer.[Category].&[27].CHILDREN }), 0, 200 ) } PROPERTIES PARENT_UNIQUE_NAME, MEMBER_TYPE ON COLUMNS,{ } PROPERTIES PARENT_UNIQUE_NAME, MEMBER_TYPE ON ROWS FROM [Sales]
So no more "tricks" are needed.
February 03 MDX queries in RS 2005 the old fashioned wayDo you want to create MDX queries in RS2005 just as you did in RS2000? Do you feel that you really can´t do what you want with a MDX query in RS2005? Instead of choosing "Microsoft SQL Server Analysis Services" as DataSource type, choose "OLE DB". Then press edit and set the OLE DB Provider to "Microsoft OLE DB Provider for Analysis Services 9.0". Or just paste the following into "Connection String":
Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=MyCatalog
Now you got the old RS2000 way of doing MDX queries. Browsing dimensions in ProClarity performanceI saw this in Jesse Orosz blog about performance issues in ProClarity, when browsing a dimension in the Setup Panel. This was interesting since i do have the same problem sometimes. Users complain that when they want to go down one level in the setup panel it can sometimes take up to 30 seconds to get the children loaded. And this was not an issue when they used SSAS200(see below). When querying for the children, ProClarity sends this type of query to the server: SELECT { SUBSET( ADDCALCULATEDMEMBERS( { [Customer].[Category].&[27].CHILDREN }), 0, 200 ) } PROPERTIES PARENT_UNIQUE_NAME, MEMBER_TYPE ON COLUMNS FROM [Sales] And it returns the default measure in the cube for each member. So I could think of three ways to fix this: 1. Jesse´s way and beef up the aggregations 2. Point the default measure to the measure with the lowest amount of rows and dimension usage 3. Create a "fake" measure group with no rows and dimension usage. I was a little more lazy and opted for the second one. Now since this was not an issue in SSAS2000 it got me a little curious. So I issued the above query for the corresponding dimension in SSAS2000. And it did not return the same result. It returned the same amount of customers but with a measure of NULL in it. Now I do not know if ProClarity uses the same query when browsing SSAS2000 as in SSAS2005, but if they do it would explain the difference in performance in this case. Hopefully, they will fix it somehow in 6.3(7.0?).
|
|||||||||||||||||||
|
|
Invite as friend