Wherescape Red and creating dimensions

My data source is a very denormalised reporting-style source system extract. I need to pluck out the dimensions and facts that are embedded in this source data, and I was hoping Red's much touted code generation would help me through this.

Dimension #1
- Dragged a load table into the dimension middle panel and specified the name
- Selected Normal dimension type
- Clicked OK to accept definition
- Clicked Finish to avoid creating the dimension based on the load table
- Deleted lots of irrelevant columns
- Right-clicked the dimension (in the left panel) and selected Create (recreate)
- Right-clicked the dimension and selected Properties
- Specified Update Procedure as ***Create New Procedure ***
- Selected the business key column
- Right-clicked the dimension and selected Update

Phew, I'm done! It didn't take long but I had a lot of steps to remember. It strikes me that a suitably constructed wizard could have completed a cycle of creating multiple dimensions and facts from one load table.

Dimension #2
More of the same, except that the Update failed with error:

-2 dim_2 update FAILED with error 2601 Step 100. Error Msg: Cannot insert duplicate key row in object 'dbo.dim_2' with unique index 'dim_2_idx_A'.

The dim_2 table was partially populated, so it appears a specific cursor row upset it partway through the data load. My first guess was there were multiple cursor rows for the same business key. However queries on the load table didn't find any.

Turns out that the generated update procedure doesn't handle nulls. To fix this I had to:
- right-click the dimension
- select properties
- click Rebuild for the update procedure
- tick Allow where clause editing and click OK to leave the business key as is
- type where business_key_column is not null and click OK

I guess there may be a tick box somewhere to make the procedure ignore nulls but I couldn't find it.

Now on to dimension hierarchies! I right-clicked the dimension and selected Add Hierarchy and it only offered me the option to reorganise the existing fields. I had to create separate dimension columns for calculated hierarchies by right-clicking the dimension and selecting Add Column.

Comments:
- The fields are mostly self explanatory but not very typo-proof, being free text fields.
- At various points it is necessary to update the dialog box which is done by clicking the Update button. Annoyingly this closes the dialog box and I had to reopen the column properties to be able to continue defining the column.
- The transformation tab could potentially hold some serious sql but is fixed size with limited IDE functionality.

Wherescape Red and Excel data source

I wanted to use a multi tab multi column Excel file as a data source. I didn't do any prior reading of the help file or documentation, apart from my previous walk through of the tutorial. Call it an ease of use test :)

I dragged the xls file in to the load table area and was presented with a dialog box Specify the type of load required. I selected the 2nd option File Load (columns parsed) which superficially appeared to be the right choice, but nothing appeared to happen. I couldn't get the corresponding load table to be displayed in the browser.

I closed and reopened the browser, and the load table was displayed in the browser. However as soon as I tried to look at its properties etc I got the message The specified load table could not be found. I performed a validity check of the metadata and a problem was reported: There are no objects in ws_load_tab with an object key of n.

I wasn't prompted with any metadata cleanup options and I couldn't find any repair functions on the menus. So I opened up the wslwarehouse database in SQL Mgt Studio* and poked around in the metadata, eventually identifying the load table object definition in ws_obj_object. I deleted the offending row and ran another validity check which advised that the metadata was clean.

I've revisited the help file and I can't find anything specifically saying Excel isn't usable as a source, but I can't find instructions for loading Excel either. I think I can assume by omission that it's unsupported. Regardless this was pretty ungraceful error handling.

So I saved each xls tab as a separate tab-delimited text file and dragged them in. This time a dialog box popped up and the load definition process started. My thoughts on the wizard:

- It doesn't make any effort to identify the delimiter, header row or row separator. And char(9) isn't adequate to specify a tab; it has to be CHAR(09)
- It makes rather poor data type suggestions given that it's obviously sampling the data.
- Manual entry of data types aren't added to the data type drop down.
- The data happened to have a column header called Procedure which caused table creation and population to fail because it's a sql keyword. The wizard didn't identify this and change the column name to something else.

* Yes I've given up on XE/APEX which has become unavailable on reboot too many times for my limited patience. SQL Server doesn't die inexplicably on reboot, doesn't get upset when used on a virtual machine with no network adapters, doesn't stop working because it was installed by a domain admin, etc etc.

The Wherescape Red Sucks Honeypot Post

I've been writing boring notes to self in this blog, fully expecting nobody to bother commenting. Now that someone has actually written a comment, I'm completely freaking out! I can't help but suspect that the commenter has undisclosed interests and is engaging in viral marketing. A lot of vendor-aligned consultants these days are expected to provide product discussion forum feedback as a background task - perhaps some vendors have stipulated public comms responsibilities too for their consultants?

Apologies if you think this is all too cynical and even somewhat misanthropic.

Oracle Express and VPC

The Apex site was unavailable afte reboot, and sqlplus returned this error:

ORA-1034: ORACLE not available
ORA-27101: Shared memory realm does not exist

This thread discusses possibilities and suggests searching on OTN forums but the forums are currently unavailable!

I think it may be something to do with assigned ip address but discussion suggests Oracle should still be available via sqlplus.

Wherescape Red and Oracle Express

Before installing the Red tutorials, do the following to save yourself manual tidyups:
* Create tablespaces called DATA and INDEX
* Create a user called wtutorial

What does this error mean during tutorial install?

insert rows into ws_hie_link(hl_hie_key, hl_obj_key, hl_child_key, hl_parent_col_key) values(ws_hierarchy_seq.currval, 71, 027city#) [Microsoft][ODBC Driver for Oracle][Oracle]ORA-00917: missing comma

Haven't figured out what to do about Red's attempts to create bitmap indexes.

PerformancePoint CTP4 appears overnight like a tent city

http://connect.microsoft.com

Announcement came in the form of a handful of posts by independent bloggers.

I'm guessing that a VPC image will be available in about 3 weeks time based on previous track record.

Don't use @ symbol in passwords if Oracle is in the building

The standard administrator password for Microsoft Virtual PC images is pass@word1

If you install an Oracle database on one of these images then the administrator will never be able to connect to the database.

connect user/password@destination

Forum post about asp.net locales

From bhughes10 on 9/28/2006 4:51:02 PM
I saw a question from Norman Yuan several months back with the same problem I'm having now. Never saw an answer.My currency that should be displayed as $200.00 is displayed as A200.00 randomly when my production server is under load. It doesn't happen on the dev computers. An IIS reset temp fixes it but soon returns. I'm running ASP.NET app, .NET 1.1, developed on VS.NET2003. Windows Server 2003 Std. with latest SP, IIS6.0

---

From bhughes10 on 10/4/2006 4:43:01 AM
It's been 2 days with heavy loads on my server and no problems. I took your suggestion and created a new application pool for the site. Thanks Carl.
"Carl Daniel [VC++ MVP]" wrote:
> Norman Yuan wrote:
> > Still no solution here. Originally, I though it could have something
> > to do with our incapable server (PIII700Hz, 512MB, running Win2003
> > IIS6 and SQL Server2000 on the same box). But after upgrading to a
> > brand new, powerful server, the problem is still there. The $ sign
> > randomly changed, but not alway on the same web page. That is,
> > browsing the same page with different query string parameters, the $
> > sign sometimes is OK, sometimes is not.
> > Very frustrated with no cause found.
>
> This is caused by something running on a threadpool thread that changed the
> default locale. We ran into this issue with an ASP.NET application as well
> and through trial and error identified another ASP.NET application running
> in the same app pool that was changing the locale to German for some reason.
>
> There are two approaches you can take that might help:
>
> 1. Explicitly specify your intended locale (i.e. IFormatProvider) every
> where you convert numbers to strings.
> 2. Separate all applications into their own completely isolated application
> pools to keep locale changes from one application from "leaking" into
> another.
>
> -cd

P2V, SSIS, asp.net and datetime

Recently I did a P2V of a production Windows 2003 server in order to create a dev environment.

Among other things it comprises an asp.net site that calls an SSIS package.

When I attempted to debug the existing SSIS package in visual studio, it failed with "string was not recognized as a valid datetime". When I checked the regional settings of the windows server I found they had not survived the P2V process and had reverted to US english. This post discusses the language translation issues that can occur with SQL Server, so as per previous blog post I updated the windows default regional settings. I left SQL Server as us_english as this is how it's set up in prod.

End to end debugging was fine after this. However when I deployed assemblies into the dev environment but I got exactly the same error at the point of calling the SSIS package from asp.net. Suspicion immediately fell on the app pool identity. However its database user has the same default language as all the other users - us_english.

I then noticed that Sharepoint on the same box was displaying US style dates. So it appeared that IIS or asp.net had a different default locale from windows! But no, updating the Sharepoint locale info didn't fix the asp.net site. It turns out that Sharepoint sites have their own locale info which is drawn from Sharepoint's own settings.

I decided to repair IIS but found that I couldn't get to Add/Remove Windows Components from the Add or Remove Programs control panel - I got an error as follows:

Setup library ocgen.dll could not be loaded, or OcOMEntry could not be found. Contact your system administrator. The specific error code is 0x7F.

Presumably this was another P2V side effect. I ran sfc /scannow but this didn't help.

Giving up on this, I went back to investigate the site's asp.net global configuration and noticed that the application settings had dates stored in the local format! So the translation was happening on the site itself. On the application tab I found that the culture and UI culture had changed to af-ZA (Afrikaans). Actually I suspected the values were blank and the dropdowns were just displaying the first entries on the lists. So I updated them to local culture and reset iis. The sites were now using local dates.

Some forum post

RE: SSIS, relative path configuration file references and asp.net David Norman2/28/2006 7:19:28 PM

Sorry, this information is not correct. I was viewing logs that excluded OnWarning messages.
As far as I am currently aware, the only guaranteed place to put configuration files where they will be found by an asp.net site using an app pool identity other than Admnistrator is in \windows\system32\inetsrv

"David Norman" wrote:
> Config files for child packages called from asp.net will also be found during
> execution relative to this directory:
>
> \Documents and Settings\App Pool Identity User\
>
> So to have two separate package collections on the same server e.g. for test
> and live, you need two sites using different app pools with different
> identities. Otherwise both collections will use the same config files.
> > I think the way to structure this so you still have the flexibility to run
> the packages both directly and via the site is to put copies of the config
> files in the app pool home dir. The downside is you can get out of sync and
> access to the directory needs to be opened up to more people.
>
> But I won't add my voice to the chorus of SSIS bloggers who trumpet the need
> to follow disciplined practices - template packages, carefully designed
> folder structures, etc. These are just workarounds for a not very easy to use
> environment.
>
> One thing I believe IS good practice is to not edit live packages directly.
> If you're happy to hack away at the config file paths in live .dtsx files
> then you can you ignore these posts entirely.
>
> Hope this helps somebody, it's taken me a long time to figure all this out.
> David
>
> "David Norman" wrote:
> >
> Found it!!!!! Desperation wins through!!!
> >
> > \Windows\system\inetsrv (app pool location)
> >
> > But it's not going to be a very well structured package environment. And I
> > don't know if I can different locations for test and live will be possible.
> >
> > All in all, fairly ridiculous if you ask me.> > > > "David Norman" wrote:
> >
> > > I have a package collection with one parent package that calls several child
> > > packages that share a common configuration file referred to using a relative
> > > path (everything is in the same folder).
> > >
> > > If I put the child packages and config file in a different folder execute
> > > the parent package, the child packages fail to find the config file. If I put > > > the config file with the parent package, the child packages find it.
> > >
> > > With an asp.net site - the child packages don't find the config file in
> > > either their own or the parent package folder.
> > >
> > > This has led me to believe that the location where the "executable" is
> > > launched dictates where the config file needs to be. On this basis I have
> > > tried putting it in the following (increasingly desperate) locations:
> > >
> > > virtual dir root
> > > virtual dir bin
> > > site root
> > > drive root
> > > \Program Files\SQL Server\90\Binn (location of MsDtsSrvr.exe)
> > > \Windows\System (location of W3SVC)> > > the "My Documents" directory of the app pool identity
> > >
> > > One exception is that the config file is found if the app pool identity is
> > > an administrator of the SQL Server box - but this is not an acceptable
> > > solution. However at the rate I'm removing security in my search for a
> > > working setup, it won't be long before everyone's an administrator anyway!
> > >
> > > Can anyone give me any guidance?
> > >
> > > Ta in advance,

Multiple SSIS environments on the same server

Got Dev and Test (and even Prod) environments on the same box? Want to avoid hacking the package xml when promoting SSIS solutions? There are a couple of configuration options:

1. XML configuration file

The key here is to use a relative path. The wizard guides you to click the Browse button and specify an absolute reference e.g. c:\ssisdev\config.dtsconfig. Instead, just type in config.dtsconfig. SSIS will look for the config file in the package location.

Depending on how you structure your solution, you don't necessarily want your config files in the same location as the packages that use them. I suggest you give up on this now and accept all config files will have to be in the same location as the packages that use them.

This is because longer relative paths such as ..\config.dtsconfig don't work (this particular example saves the config file to C:\Program Files\Microsoft Visual Studio 8\Common7 !!!)

2. SQL Server

This is a two phase approach. Firstly, set up a SQL Server configuration table. As part of this process you will reference or create a database connection. Then (and this is the cunning bit) create an XML config file with a relative path (as per option #1) that contains the connection string for the connection. You can then specify different configuration table locations in the xml config files.

This option also uses xml config files and is hampered by the same difficulties as option #1. You will need a separate config file for every package location, and you will need to maintain these config files individually. You could design to ensure your config files are all identical and write a script to copy them as part of the build; whether this is worth the effort depends on your solution.

SSIS package deployment does SOMETHING

Everything I read out there says that SSIS package deployment to file system does nothing more than a file copy.

I created some SSIS packages and called them from an asp.net site on the same box. However the asp.net site couldn't find the packages until I deployed them to the file system.

So deployment does something, either to the permissions of the deployed packages or by registering them as available somehow. Is there a hidden GAC equivalent for packages (GPC??)

SSRS setup

Tried to bring up http://server/reports and got the error:

The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) (rsRPCError) Get Online Help Bad Data. (Exception from HRESULT: 0x80090005)

Following instructions here got me nowhere as I got the same error on attempting to extract the symmetric key.

Apparently this is the wrong approach for SQL2K5, I found in the following comment:

For Sql Server 2005 you can change, backup and restore via Reporting Services Configuration Manager.
If you have no backup of the Encryption key you can delete the key and re-create using the change option. BUT BE WARNED!, you must re-enter connection strings and stored credentials in the reports and shared data sources after this.
Refer to the follow technet article BEFORE doing this.

Unfortunately I couldn't get the Initialization to change from x to tick. So I reinstalled SSRS and went through the Reporting Services Configuration Manager step by step to reconnect SSRS services to the SSRS database. At database setup it moaned about existing keys and the Initialization icon stayed as an X. So I went to the Encryption Keys step and deleted keys. At first this didn't look like it had improved things as the Initialization still appeared to have failed, but a refresh updated it to a tick.

Business Scorecard Manager development notes

It's a typical requirement to have a scorecard that needs regular updating to refer to a new current period. It's also typical that the database contains data for future periods which means Time Intelligence can't be used to get the scorecard to dynamically update itself. So what options does MOBSM give us for programmatic control?

For the scorecard, all the necessary objects are exposed as per the sample code which updates the current and previous members for all kpis in all scorecards.

IBpm pmServer = PmService.CreateInstance(BsmServerUrl); ScorecardCollection scorecards = pmServer.GetScorecards();
for (int i = 0; i <>
{
Scorecard scorecard = scorecards[i];
{
KpiCollection kpis = pmServer.GetKpisFromScorecard(scorecard.Guid);
for (int j = 0; j <>
{
Kpi kpi = kpis[j];
KpiMeasure kpiactual = kpi.Actual;
kpiactual.TimeIntelligenceCurrentMember = new Member(specify member here);
kpiactual.TimeIntelligencePreviousMember = new Member(specify member here);
pmServer.CreateKpi(kpi);
}
}
}

Unfortunately it's not quite so easy to update the periods displayed in chart report views. The Kpi.ReportView.CustomData attribute is an xml string that contains the report view definition, and lurking within this is more encapsulated xml containing the chart definitions. By the time you're finished you will know xml traversal like the back of your hand (and if you have the same attitude to xml as me, you will like the back of your hand a LOT better!).

Addendum:
I realised that I'd be a lot better off adding some calculated members and hierarchies to the time dimension and referring to these instead of updating the current and previous time intelligence members for the scorecard and traversing the chart's IncludedMembers.

The techniques described above are more appropriate for dynamic update based on user selection than as part of a period end process.

SSAS development notes

When the data source has changed, use menu option Data Source View:Refresh to update the dsv.

After creating dimensions, open the Cube definition and right-click in the Dimensions tree to add the new dimensions.

Deployment error:Failed trust relationship between workstation and server
http://prologika.com/CS/blogs/blog/archive/2007/06/11/trust-relationships.aspx
Fix: Remove all old domain references from roles

If you're having problems getting changes to display in your cubes, reprocess and redeploy absolutely everything. Reconnect and refresh the cube browser too.

I updated the solution deployment target to a different server but it kept reverting back to the previous target. Sourcesafe informed me that the only file that changed when I updated the deployment info was the .suo file. Further reading on the web suggests that source should be managed from within Visual Studio only, not via SourceSafe, and things are more likely to work properly.

Some guy wrote in a newsgroup:

Figured out how to get the members to appear in date order - it's not as simple as setting attribute orderby properties. The steps I took are as follows:

open the time dim
dimension:add business intelligence
specify attribute ordering
for my mmm-yy attribute, select ordering attribute = new attribute
select a suitable ordering attribute e.g. one with date data type
new attribute appears: "ordering mmm-yy onwhateverorderingattributeyouselected"
next and finish

Hope that helps somebody
David

"David Norman" wrote:
> If I have date data 2005-01-01, 2005-02-01, 2005-03-01 in Excel and use this
> as the X axis for a chart, I can format it as "mmm-yy" and the axis will> display Jan-05, Feb-05, Mar-05.
>
> How do I achieve the same result using the OWC chart component and cube data> source?
>
> If I drag the date dimension level from the field list to the category axis
> containing the same data, I don't have the equivalent date formatting options.
>
> If I create a named calculation Left(Month,3)+'-'+Year then the members list
> in alphabetical order instead of date order i.e. Feb-05 appears before Jan-05> on the axis.
>
> Any suggestions appreciated,
> David

New ordering attributes can break existing ordering that use the same attribute. It may be necessary to remove all ordering attributes and recreate them together.