Apparently there is a little buggette
"Customers installing Oracle BI Applications Version 7.9 must first install Oracle Business Intelligence Enterprise Edition 10g Release 3 version 10.1.3.2.0 with Quick Fix 150. "
tut tut
Tuesday, March 20, 2007
7.9 Is here, Huh?
So you thought that OBI EE was the new Analytics. Think again. Siebel analytics has jsut gone into version 7.9, but so far I have only seen Applications 7.9, so it looks like platform is either 7.8 or OBI EE :
There new names though:
Siebel Analytics Application now becomes
Siebel Analytics Repository now becomes
Spotted the trend yet!!
This is from the documentation.
Changes to Oracle BI Repository
The Siebel Analytics repository (SiebelAnalytics.rpd) was renamed to the Oracle Business
Intelligence Applications repository (OracleBIApplicationsApps.rpd).
The Oracle BI repository contains the following changes:
■ Naming conventions. Logical tables have been renamed to start either with "Dim," to indicate
dimension tables, or "Fact," to indicate fact tables.
■ Data security. The position security design was modified to use the new table W_POSITION_DH instead of W_PARTY_LOGIN. For more information about data security, see the Oracle Business Intelligence Applications Installation and Configuration Guide.
■ Presentation layer. Some old catalogs have been replaced with new catalogs. The catalogs that
have been replaced are designated with a STOP icon in the presentation layer; these catalogs
will be deleted in a future release. It is highly recommended that you migrate your reports to the corresponding new catalogs.
■ Time dimension. A common time dimension is used in all new catalogs to indicate a unified time dimension across all facts. Some secondary date dimensions are still exposed when necessary, but they are not conformed to all facts, and, thus, should be used cautiously.
I never did trust using the W_PARTY_LOGIN so I'm glad it has gone. I'll let you knwo what I think of the replacement when I have tested it.
Good to see a common time dimension, but how does it work? Again mre testing will tell.
There new names though:
Siebel Analytics Application now becomes
Oracle® Business Intelligence Applications (OBIA)
Siebel Analytics Repository now becomes
Oracle Business Intelligence Applications repository (OBIAR?)
Spotted the trend yet!!
This is from the documentation.
Changes to Oracle BI Repository
The Siebel Analytics repository (SiebelAnalytics.rpd) was renamed to the Oracle Business
Intelligence Applications repository (OracleBIApplicationsApps.rpd).
The Oracle BI repository contains the following changes:
■ Naming conventions. Logical tables have been renamed to start either with "Dim," to indicate
dimension tables, or "Fact," to indicate fact tables.
■ Data security. The position security design was modified to use the new table W_POSITION_DH instead of W_PARTY_LOGIN. For more information about data security, see the Oracle Business Intelligence Applications Installation and Configuration Guide.
■ Presentation layer. Some old catalogs have been replaced with new catalogs. The catalogs that
have been replaced are designated with a STOP icon in the presentation layer; these catalogs
will be deleted in a future release. It is highly recommended that you migrate your reports to the corresponding new catalogs.
■ Time dimension. A common time dimension is used in all new catalogs to indicate a unified time dimension across all facts. Some secondary date dimensions are still exposed when necessary, but they are not conformed to all facts, and, thus, should be used cautiously.
I never did trust using the W_PARTY_LOGIN so I'm glad it has gone. I'll let you knwo what I think of the replacement when I have tested it.
Good to see a common time dimension, but how does it work? Again mre testing will tell.
Connecting People
Is there a clear winner in the war for Networking platforms?
I started using LinkedIn a while back and have developed a great network of business contacts, but then I realised that wasn't the first one I had signe dup to. Friends Reunited was the leader originally and everyone flocked onto the system - until they started charging for it - I was quickly out of there but not before they had made their millions. I wonder if they would have made more money by not charging but by going down the free model with extra paid services?
Next came Xing (was previously OpenBC). This is exactly the same as Linked in but based in Europe (Linked In is avery American).
But now I see that IT Toolbox is getting in on the act. Do I really have to maintain a profile and network there too?
Then I get an email from someone who has a neat business card attached, from Plaxo. Looks like I should have that too.
Of course there's messaging too, obviously I went for MSN - everyone did, now Yahoo looks pretty good and half my contacts on on that, but along comes Skype and it's another list to maintain.
In the end I think I'll be spending all day working on my online presence and not actually talking to people. Why can't I just get one system that talks to every other system (hey isn't that what customers were asking for 20 years ago out of IT).
Come on Microsoft, help me out here - use your dominacne to buy all these companies and shut them down - I only want one system.
I started using LinkedIn a while back and have developed a great network of business contacts, but then I realised that wasn't the first one I had signe dup to. Friends Reunited was the leader originally and everyone flocked onto the system - until they started charging for it - I was quickly out of there but not before they had made their millions. I wonder if they would have made more money by not charging but by going down the free model with extra paid services?
Next came Xing (was previously OpenBC). This is exactly the same as Linked in but based in Europe (Linked In is avery American).
But now I see that IT Toolbox is getting in on the act. Do I really have to maintain a profile and network there too?
Then I get an email from someone who has a neat business card attached, from Plaxo. Looks like I should have that too.
Of course there's messaging too, obviously I went for MSN - everyone did, now Yahoo looks pretty good and half my contacts on on that, but along comes Skype and it's another list to maintain.
In the end I think I'll be spending all day working on my online presence and not actually talking to people. Why can't I just get one system that talks to every other system (hey isn't that what customers were asking for 20 years ago out of IT).
Come on Microsoft, help me out here - use your dominacne to buy all these companies and shut them down - I only want one system.
Monday, March 19, 2007
Friday, March 09, 2007
Make the most of your repository
Quick and Easy Mail Merge
Why not make the most of your repository, as well providing the backbone of your reports it is also a great source of clean organised data that is easily accessible by other programs. Here’s one way to address envelopes or letters using MS Access and Word.
Create a new blank database in Access.
Create a new query in design mode, close the Show Table dialogue and go to Query>SQL Specific>Pass-Through.
Now click on the Properties icon on the toolbar and click the ellipsis next to ODBC Connect Str, this will open the Select Data Source dialogue.
Click the Machine Data Source tab and click New and follow the instructions to create a Data Source Name based on Oracle BI Server from the drivers list, follow the instructions and enter appropriate user names and passwords to suit your systems configuration. Enter your repository user name and password and answer yes to save this information in the connection string.
Type your SQL query. Click the icon to run the query and test you SQL.
Now create another new query in design view, click the queries tab in the Show Table dialogue and select your pass through query, add the columns to the new query.
Click the drop down arrow next to the Query Type icon and select Make-Table Query, save the query with an appropriate name, and click the Run Query icon.
You will be presented with a warning about the number of records to be pasted into the table, dependent on your needs you may decide to set some selection criteria to break the list into more manageable chunks, this may depend on whether you are merging to a letter, labels or envelopes. You could create multiple Make- Table queries to populate appropriately named tables based on select by County, State, City or Name beginning with…... They can all be based on the one pass through query.
If you have the coding skills you can automate the running of these queries and refreshing of the tables.
Once the make table query has run, click Tables in the objects list and you will see the table you have created, select it navigate to the Tools>Office Links>Merge it with Microsoft Office Word, or click the down arrow next to the Office Links icon, follow the instructions in the dialogue, simple.
Why not make the most of your repository, as well providing the backbone of your reports it is also a great source of clean organised data that is easily accessible by other programs. Here’s one way to address envelopes or letters using MS Access and Word.
Create a new blank database in Access.
Create a new query in design mode, close the Show Table dialogue and go to Query>SQL Specific>Pass-Through.
Now click on the Properties icon on the toolbar and click the ellipsis next to ODBC Connect Str, this will open the Select Data Source dialogue.
Click the Machine Data Source tab and click New and follow the instructions to create a Data Source Name based on Oracle BI Server from the drivers list, follow the instructions and enter appropriate user names and passwords to suit your systems configuration. Enter your repository user name and password and answer yes to save this information in the connection string.
Type your SQL query. Click the icon to run the query and test you SQL.
Now create another new query in design view, click the queries tab in the Show Table dialogue and select your pass through query, add the columns to the new query.
Click the drop down arrow next to the Query Type icon and select Make-Table Query, save the query with an appropriate name, and click the Run Query icon.
You will be presented with a warning about the number of records to be pasted into the table, dependent on your needs you may decide to set some selection criteria to break the list into more manageable chunks, this may depend on whether you are merging to a letter, labels or envelopes. You could create multiple Make- Table queries to populate appropriately named tables based on select by County, State, City or Name beginning with…... They can all be based on the one pass through query.
If you have the coding skills you can automate the running of these queries and refreshing of the tables.
Once the make table query has run, click Tables in the objects list and you will see the table you have created, select it navigate to the Tools>Office Links>Merge it with Microsoft Office Word, or click the down arrow next to the Office Links icon, follow the instructions in the dialogue, simple.
Wednesday, March 07, 2007
An MI Strategy for ….?
I was doing some research today and decided I needed to reference some work I did TEN years ago.
This is not such a big deal, I keep everything. Absolutely everything.
All my emails, texts, databases, spreadsheets, code – the lot. With storage technology so cheap this is not such a difficult thing to do.
The only hurdle is to remember old passwords. I don’t have a password repository (keep meaning to get one though) so sometimes have to resort to OfficeKey – It tells me what the passwords are on my Office files.
Whilst looking I came across an article which amused me, it’s not funny, it’s just that it’s still relevant today, 10 years later.
Her are some extracts
“This document lays out some ideas on how to manage the gathering and assimilation of the necessary information to help run the Claims function of XYZ.
There are three main areas to consider for capturing information
- Claims,
- Telephones and
- People/Costs.
For each I have laid out some ideas on principles and methods that could be adopted.
The second part of the document looks at who and specifically what is needed for effective Information Management(IM).”
So, XYZ had some Information needs, and I’ll bet they still do!!
"Claims
Efforts should be made to get the <
I would recommend SQL Server, Oracle, DB2 or similar package.
Specific application development should be limited to 'must have for operational effectiveness’ systems.
If the data is on an open platform then user friendly packages such as Crystal reports, Access and Ms Query can extract quickly and cheaply the information required. The added benefit of this method is that there is a transfer of skills from IT to the Claims department.
Whilst the IT department is the guardian of the data they should not be the owners. Keeping the data on the mainframe and developing small but expensive solutions to use the data only serves the IT department not the users."
IT not the owners of data, heresy!
"Telephone Activity
All Aspect related data is already kept in a data warehouse on an Open platform. The data is available to view and download using Crystal Reports.
Relevant extracts should be taken from the warehouse and put into a Claims Information System.
Only one system should be used for reporting Claims Telephone activity to all users within the department, and outside of it. All reporting activity undertaken by Team leaders and others within the Claims dept. should stop and be given to one person/team.
Now why did I say this bit? All users should stop creating reports – Sounds mad but actually when you see HOW they are doing it, then they should stop – everyone was downloading some data into Access or Excel and creating their own reports. No-one managed with the same set of consistent KPI’s.
People/Cost Management
There is now more focus on individual, team, section and department performance. To aid this process Claims are currently using Workware. Having searched the market on several occasions I have not found a better product than Workware for planning and monitoring workloads. The alternative would be to use spreadsheets which I have seen in action but they become unstable and difficult to manage very quickly. I understand that Workware is in constant development and therefore I would recommend that the software is used after the initial period.
The normal Absent, sickness and holiday planning and monitoring should be integrated with roster production and performance tracking. This integration can be developed further by including training plans/actual courses, skills matrices and PPP production. The main aim must be to reduce the re-keying of staff lists and the elimination ‘re-inventing of the wheel’
With the advent of section managers taking responsibility for the cost centres representing their span of control it is more important than ever that cost information is captured, verified and analysed.
There are two main sources to aid cost centre management;
Finance reports
Personnel Databases
Each month the cost centre reports need to be checked for their accuracy, i.e. people salaries in the right cost centre.
All costs should be tracked over time and used in ratio analysis, such as cost per Claim Closed.
All costs that are incurred in a month should be shown in the cost centre reports. It has been common practice for the temp staff costs and other invoices to be shown in the accounts 2-3 months after they were incurred. This gives a false picture as to what is going on and can lead to poor decisions being made with regards to expenditure.
The budgeting process within claims should not be a huge exercise, which is separate from normal business planning. Re-forecasting the next 12-16 months should be a regular exercise, perhaps linked to the overall plans within Workware.
An understanding of settlement cost behavior must be developed in order to determine where resources are best deployed. This is best achieved by tapping into the skills of the actuarial department.
For all new projects and tactical/strategic decision making there should be an understanding of the impact from a cost point of view. Models must be developed to add weight to the decision or prevent mistakes."
WOW, Is Workware really that good. Can you run a large number of teams using it? I thought so at the time, and I still think so.
Now this is the good bit….
"Information Management
As a general rule all information should be available to all (with a few exceptions)
Ideally all information users should be identified before solving their needs for them!
Data should only be captured and information created if it aids decision making, such that the decision is influenced by having the data. For example keeping sickness records allows for changes to pay if the threshold is reached.
A small team 2/3 FTE should be created within claims. These people can be the local 'IT' experts but with a good knowledge of claims Dept. processes. They can facilitate the routine information needs and provide detailed analysis on Claims activity, individual performance and other ad-hoc. They can also be used to help improve the PC skills across the department, but should not be used as a 'Help desk'.
Any data capture and report production currently undertaken elsewhere within the Claims dept. should be brought within the control of the MI team. This includes all reports supplied by Secretaries, Team Leaders, Section managers and support areas.
The MI team should also be capable of supporting the use of Workware (or whatever system is used for work management)
The MI team should make full use of the latest available technology and not be held back by XYZ Co. mass standard PC policies, i.e. Use Crystal Reports and Intranet technology and NOT the current standard Office95.
To capture, analyse and report data a Claims Information System(CIS) should be developed using Ms Office technology. This is not an all singing-all dancing solution to all information needs but an integrated set of databases and spreadsheets that minimise the effort required and reduce errors in keying. The creation and maintenance of the system should be the sole responsibility of the members of the MI team.
To deliver information to end users the ideal solution is by the use of an intranet. This is a cost effective method of getting data to the desktop of the users. This can be introduced quickly and cheaply and used for all department information and not just for activity reports e.g. Working Practices can be kept online and updated easily. This solution is available now to all those with a PC.
The profile of information in general needs to be raised across the whole dept. For example; use can be made of notice boards to show graphs of output, service and quality. The whole dept should know what the MI team are producing and what data they hold so that tasks are not duplicated.
The Claims management should fully support the work of the MI team, especially in the data capture and processes they introduce. The MI team should be able to supply information directly to the Claims manager, or any manager without fear, i.e. do not shoot the messenger if the results are not favorable.
Summary
To enable a better decision making process more information is required by all members of the Claims department. This can be enabled in a cost-effective manner by Claims staff using their own skill and knowledge. The specialists’ within the IT department or elsewhere should not dictate when and how the Claims staff get the information they require, and expensive development work is not always the answer (Just think about what has been delivered to date!). A small team with skills in communication, claims and IT within the claims dept can deliver when and how YOU want them to.
Powerful stuff. Information available to all, using Web technology. I wish I had seen Siebel Analytics back then (when did NQuire start up?)
Obviously the tools are now there to reduce the need for Office applications (Access Db’s for reporting), specifically Oracle BI (ex Siebel Analytics), Essbase, Microstrategy and dozens of others.
I wonder if XYZ Co ever put a reporting system in?
Saturday, March 03, 2007
Friday, March 02, 2007
Oracle to buy Hyperion!
Ha. Good for Oracle.
Essbase is a horrible tool in my view but the accoutants love it, so Oracle have taken on a popular product, and have a foothold in hundreds more companies.
Trouble is, now I'm stumped as to how they are going to integrate Essbase into the new Oracle BI strategy, can and should Analytics work alongside (or on top of) Essbase cubes?
Also, I thought that the majority of Essbase systems were based on other database technologies.
Who next? Cognos? Business Objects themselves! SAS!!!
News item here
Essbase is a horrible tool in my view but the accoutants love it, so Oracle have taken on a popular product, and have a foothold in hundreds more companies.
Trouble is, now I'm stumped as to how they are going to integrate Essbase into the new Oracle BI strategy, can and should Analytics work alongside (or on top of) Essbase cubes?
Also, I thought that the majority of Essbase systems were based on other database technologies.
Who next? Cognos? Business Objects themselves! SAS!!!
News item here
Thursday, March 01, 2007
Bad News
Just heard that the Keel is still not attached to the bottom of our boat, and there's no mast either!
Looks like we're going to miss the first race of the season. Damn.
New Phone
Just a quck update to say that I ended up stayng with O2 but got a new phone, the Sony w880i
I just phoned them up to say I was going to switch and they haggled a bit, I pointed out better deals on Three and they matched them.
Now I'm due to pay £17.50 per month for a year for 600 minutes and 500 texts. Thats still only 20 minutes per day but it does work out at 2.9 pence per minute.
The phone looks really cool too
.
Wednesday, February 28, 2007
How good IS good?
I've been interviewing loads of candidates recently (you may have seen my ads on Jobserve and LinkedIn) for Siebel Analytics consultants.
By the way, if you would like to connect on linkedIn (www.linkedin.com) please send an invite to adrian.ward@majendi.com; in the note say you are reading the blog.
Most of the interviewees have stated that they are pretty good at developing Analytics applications. One said that he was an expert and knew all of the components in detail.
So, to test this I ask a few simple questions (see my previous blog for some examples). Before long it becomes obvious that they have over estimated their own ability. This makes for an awkward end to the interview because it's obvious that they have been exposed. So why do they do it? Do people really believe that they know the system like the back of their hand, or is it bluff to try and get past the interview and into the job where they will learn?
Perhaps my questions are too difficult?
Try this one, and see what you think.
Q. What Types of Physical Table are there?
A. There are 4 types of Physical table
- Ordinary
- Alias
- Stored Procedure
- Select
Not really a difficult questions, is it?
But no-one could answer it.
By the way, if you would like to connect on linkedIn (www.linkedin.com) please send an invite to adrian.ward@majendi.com; in the note say you are reading the blog.
Most of the interviewees have stated that they are pretty good at developing Analytics applications. One said that he was an expert and knew all of the components in detail.
So, to test this I ask a few simple questions (see my previous blog for some examples). Before long it becomes obvious that they have over estimated their own ability. This makes for an awkward end to the interview because it's obvious that they have been exposed. So why do they do it? Do people really believe that they know the system like the back of their hand, or is it bluff to try and get past the interview and into the job where they will learn?
Perhaps my questions are too difficult?
Try this one, and see what you think.
Q. What Types of Physical Table are there?
A. There are 4 types of Physical table
- Ordinary
- Alias
- Stored Procedure
- Select
Not really a difficult questions, is it?
But no-one could answer it.
Whilst I’m on the subject of tables, the new OBI EE has a slightly different way of creating Aliases.
You now have to include the Orignial underlying Table in the model (something I always do but found others do not). Right click on the table and select New Object.. Alias.
Monday, February 26, 2007
Technology!!
I have the worst phone in the world.
It's the O2 XDA lli. Oh it may also be a PDA, has windows, can pay music, runs Excel/Word/PDF, has a web browser to connect to the internet, has email and can syncronise with my Outlook, has vidoe and a camera, plays games; but it's the worst phone in the world.
You'd think that is was brilliant with all those features, but sometimes you just need a phone.
You know how you see people on the tube holding on with one had whilst texting their mates with the other - I can't do that (there are no keys on the xda and it a big bulky thing).
Voice activate their phone to call their mates whilst driving - I can't do that
Have video calls with the kids - I can't do that.
and the battery only last two days.
It doesn't even connect to the web very well, it took me two years to get it configured and it's rubbish.
So, I have finally given up. I'm now looking for a new phone.
But here's the catch - last time I went looking for a great phone the xda sounded fantastic - All those features!! wow gotta have it. Now hate it.
for the new phone,
Do I get 3G?
Should I get Balckberry?
What about the camera, 2 megapixels or 3?
Do I need PDF viewer?
What about my Sudoku game I bought for the xda and Dope wars?
Do I need an RSS viewer?
Then there the question of service provider.
It's the O2 XDA lli. Oh it may also be a PDA, has windows, can pay music, runs Excel/Word/PDF, has a web browser to connect to the internet, has email and can syncronise with my Outlook, has vidoe and a camera, plays games; but it's the worst phone in the world.
You'd think that is was brilliant with all those features, but sometimes you just need a phone.
You know how you see people on the tube holding on with one had whilst texting their mates with the other - I can't do that (there are no keys on the xda and it a big bulky thing).
Voice activate their phone to call their mates whilst driving - I can't do that
Have video calls with the kids - I can't do that.
and the battery only last two days.
It doesn't even connect to the web very well, it took me two years to get it configured and it's rubbish.
So, I have finally given up. I'm now looking for a new phone.
But here's the catch - last time I went looking for a great phone the xda sounded fantastic - All those features!! wow gotta have it. Now hate it.
for the new phone,
Do I get 3G?
Should I get Balckberry?
What about the camera, 2 megapixels or 3?
Do I need PDF viewer?
What about my Sudoku game I bought for the xda and Dope wars?
Do I need an RSS viewer?
Then there the question of service provider.
- o2? (who I now hate),
- three?,
- Vodaphone? (no because I don't like Michael Schumaker),
- Virgin Media? (got their broadband and it's really good).
And what price package? Turns out that o2 200 means paying £60 per month not £35.
The main problem is size (isn't it always the case!) For applications, games, emails, etc you need a decent size screen, but for normal phone operations you only need a 40mm square, and the ability to hold the thing in one hand, texting.
Is there a phone out there that has a fold out screen - ideally I would like 15 inch screen, and also a folder out keyboard for faster typing, runs applications and plays games.
You know, I think I'll just go and buy a data card for the laptop and go and get the smallest 3g phone in the world!
I'll get the wrong one, but it'll give me somethign to moan about.
Friday, February 23, 2007
On the bandwagon....again
Well if we are all spilling the beans on where we're going this year then here is my diary...
Spring Series - Warsash
One race each Sunday, starting March 11th. We have a full crew and repaired boat. Lets hope it's not too cold.
Cowes Week
August 4th - 6 days racing, 6 days partying. Cobra are sponsoring the beer, Planet Pursuits are sponsoring the clothing again. Need to find our Dobby.
Still have 4 places to fill.
Little Britain Challenge
September 6th-9th. A great opportunity to network in the building trade!
All the above on Beefeater, our Sigma38. She used to be called Yeoman and was sailed by Prince Phillip in Cowes week.
Zone Squad
(The RYA Team GBR that my son is in)
Hayling Island - March 24th/25th
Rutland WATER - April 14th.
Lake Garda
Big international event for Oppies with 500 boats expected.
IOCA National Events
Inland Championships - May - Grafham - Two days
National Championships - July 28th - August 4th (+plus pre-event training)
EOF - End of Seasons – October 18th/19th
Open Circuit
Warsash – May
Maidenhead – May
Island of Wight - June
Lymmington Main fleet - June
Lymmington Regatta Fleet - June
Hayling Island - June
Wraysbury - June
Mudeford - July
Papercourt - September
Poole- September
Chichester - September
Spinnaker Open - September
Now you know where I'll be during the weekends and holidays.
As for work time, probably on client site - Don't have too much time to go to conferences. Not that I'd know which one to go to anyway. Is there one for Siebel Analytics experts who knows a thing or two about sailing?
I’m also going to try to fit in a RIB treasure hunt so watch this space.....
Spring Series - Warsash
One race each Sunday, starting March 11th. We have a full crew and repaired boat. Lets hope it's not too cold.
Cowes Week
August 4th - 6 days racing, 6 days partying. Cobra are sponsoring the beer, Planet Pursuits are sponsoring the clothing again. Need to find our Dobby.
Still have 4 places to fill.
Little Britain Challenge
September 6th-9th. A great opportunity to network in the building trade!
All the above on Beefeater, our Sigma38. She used to be called Yeoman and was sailed by Prince Phillip in Cowes week.
Zone Squad
(The RYA Team GBR that my son is in)
Hayling Island - March 24th/25th
Rutland WATER - April 14th.
Lake Garda
Big international event for Oppies with 500 boats expected.
IOCA National Events
Inland Championships - May - Grafham - Two days
National Championships - July 28th - August 4th (+plus pre-event training)
EOF - End of Seasons – October 18th/19th
Open Circuit
Warsash – May
Maidenhead – May
Island of Wight - June
Lymmington Main fleet - June
Lymmington Regatta Fleet - June
Hayling Island - June
Wraysbury - June
Mudeford - July
Papercourt - September
Poole- September
Chichester - September
Spinnaker Open - September
Now you know where I'll be during the weekends and holidays.
As for work time, probably on client site - Don't have too much time to go to conferences. Not that I'd know which one to go to anyway. Is there one for Siebel Analytics experts who knows a thing or two about sailing?
I’m also going to try to fit in a RIB treasure hunt so watch this space.....
Thursday, February 22, 2007
Documentation, It's MAD!
Let's face it, there's not many people who like documentation, except a good BA of course, but every single project needs it.
Now if the title and first sentence haven't put you off I'm going to let you into a few secrets.
1. A good set of design docs will save your project money
2. Post project documentation (actual designs, architecture maps, etc) will rarely, if ever, get read
3. Your documentation will be instantly out-of-date with release x.1
Still with me? OK, they're not really secrets, but they are issues we have to contend with. The motivation to write something detailed that will never get read and will be obsolete in 5 minutes, without the proper tools is pretty low. Especially if you have just had the go-live party the night before :)
So a typical Analytics (Oracle BI EE for the late comers) will need the following
a. Design of Datawarehouse - This includes Tables, fields, indices, schemas, tablespaces, parameters, views, Materialized views, etc
b. Design of DAC - EP's, Subjects, Table Groups, Tables, Indices (again!), Tasks
c. Design of ETL
d. Design of Analytics Repository (Oracle BI Repository)
e. Design of Webcat
f. Integration designs
g. Technical Architecture Map
h. Support Guide - What to do if.... scenarios
what did I miss?
(We are going to assume Analytics with Informatica ETL for this example)
Here's the catch. You have a reference to a physical table in at least four places - Db, DAC, Informatica, Physical layer. In your design you want to ensure referential integrity, i.e. don't refer to a table in the DAC that does not actually exist in the Db!. Another example where you have this is with tasks in the DAC that refer to Informatica Workflows.
My prefered approach is to create all these documents up front, i.e. in the design phase (say that last bit slowly with a deep voice), then update the documents at the end for what actually got built.
What tools do you have?
Most project sites that I go to will have MS Office, but you really need the professional version for Access and Visio Professional. You could try to publish the designs in Word or HTML, and you could even use Analytics to present the design!!
The Word vesion is probably the easiest (least technical) but requires most work, the Analytics version will need a place to be set up, with Webserver, db and Analytics server licence.
What do I use?
Glad you asked that because we have developed the MAD - Majendi Analytics Designer, system. This is a semi automated system that can document an existing Analytics aplication, from DB to Webcat. It produces MS Word documents which details all of the items in a) to e) above. I supplement this with Visio diagrams for star schemas (This also uses the design repository for the source tables).
As well as the Word documents templates being populated we also have a miniture Analytics system which details the meta data. The heart of the system is a set of Access databases which import metadata from txt files or directly using ADO (so it can work on any database) to conect to the databases. A side benefit is that building the system teaches you all the underlying data structures in the ETL and DAC.
How do you get the information you need?
You can query the repositories directly in the Oracle/SQL database.
You can use the documentation utilities of Analytics and the Web catalog manager
I also use the UDML file that you can get from Analytics, and parse this into an Access Db.
(If you want to know about UDML files I'll be posting a short note on that later).
The beauty about the MAD system is that I can document a whole complex system in a couple of days, even better is the ability to analyse an existing application to help with an upgrade, or with new releases - your documents can very quickly reflect the underlying new system.
So there you have it, Documentation may be boring but you can make it interesteing by using your technolgy skills to reduce the problem down to automated tasks, just like we did. :)
Thursday, February 15, 2007
Questions, Questions
Why have I not written a blog for ages ?
Does Siebel Analytics work on IE7 ?
Is there a version 8 of Analytics, if so what is it called ?
What is the difference between Siebel Analytics and Oracle BIEE ?
Where are all the best Analytics consultants ?
How do I find good crew for the next racing series ?
Is disconnected Analytics a good idea despite the technical challenges ?
How do you get more skilled in Analytics ?
What is the MAD system ?
What makes a good consultant ?
What does Siebel 8 look like ?
Is it better to be an employee or go contracting ?
When is a good time to buys flights to Portugal ?
Where is the next big Analytics Project ?
How do I pay my tax bill ?
Should I go on the Marketing Server Architect course ?
What technical questions should I ask an Analytics candidate ?
I better stop there. Since my last blog things have been busy, very busy. During that time I have been trying to think of a subject to write about, but so many things come to mind. Should it be technical, personal (to show that I'm a nice guy!) or political (don't get me started). So I'm going to give my views on all of the above.
Why have I not written a blog for ages ?
Because I went on a skiing holiday, went live at the Royal Mal on their 7.8 project, spent every weekend taking the kids sailing, did my tax return, invoiced clients, did my LTd company accounts for 2005/2006, had Xmas lunches and dinners(the Majendi one was really good), watched some rugby, developed the MAD system, had a kids night out due to sons birthday, paid lots of bills, updated the Majendi Accounts system, interviewed some candidates, ran some Analytics master classes and finally got some sleep. phew!
Does Siebel Analytics work on IE7 ?
I installed Analytics on my new laptop this week and it seemed to work fine. I've had some trouble with Oracle 10g Enterprise manager. It became corrupted so I had to uninstall everything and start again. EM is now a web based system which looks much better than the previous version and is easy to use but this corruption has been a royal pain.
Is there a version 8 of Analytics, if so what is it called ?
I don't think so. I've had a good look at the Oracle website and can still only find 7.8.5. I'll keep my eye open for news on this front, but let me know if you find anything.
What is the difference between Siebel Analytics and Oracle BIEE ?
Good question. I installed both this week on my Laptop, BIEE first. The first product worked well except the manual said the username/pwd was admin/admin, turns out it was Administrator/Administrator - Thanks! I liked the xml Publisher which is new, but the rest was mainly a renamed Analytics system, placed into a different folder structure, for example, C:\OracleBI\Server\.
Oracle BIEE looks like it's just a replacement for Analytics Platform, I can't see where you get the applications part from, other than installing Analytics Applications itself. When I tried this, it recognised that there was an existing Analytics platform so didn't create it owns structures (e.g. C:\SiebelAnalytics). I'll investigate more this week so let me know if you have a specific issues.
(I'm off to check Mark Rittman blog for more details)
Where are all the best Analytics consultants ?
I wish I knew. Majendi is always looking for consultants who have lots of experience with Analytics. I am looking for permanent consultants for Inforte. If you know of anyone who has at least a couple of Analytics project sunder their belts then please let me know ASAP.
How do I find good crew for the next racing series ?
I nearly didn't enter the Warsash Spring Series due to lack of crew. Biggest problem is that one of our team is getting married in the middle of the series, even worse, he's having a stag do the week before so taking out both weekends.
I would like to be competitive so need the best possible sailors. After some badgering I've now got a team that can compete most weeks so we should get better as the series progresses. Follow our results at WSS.
Is disconnected Analytics a good idea despite the technical challenges ?
I still don't think so. In the age of 3G and broadband everywhere it must be far better to get people to work online than go through the major hassle of disconnected Analytics
How do you get more skilled in Analytics ?
I read the ITToolbox posting with a mixture of glee and annoyance. So many beginners are being placed on analytics projects that clearly have not been on a course. They ask stupid questions which demonstrate a laziness to read the manuals, support web and even previous toolbox postings. The consulting firms that hire these people are not investing properly in their people and dong their clients a disservice. The clients who outsource to the cheapskates get the system they deserve, but the cost is that the general image of Analytics is undermined and investment is moved to other reporting tools. As an industry we really are shooting ourselves in the foot. I would like to see Oracle step in and really classify their partners so the clients know what they are getting.
What is the MAD system ?
It's a brilliant Analytics Design and Documenting system that we use to design a and document Analytics systems. It will analyse your current system and look for redundancy and flaws. It saves me weeks in a project and removes the boring work. I'm trying to get some reverse engineering in there now, along with embedded Visio diagrams.
Perhaps I should sell it?
What makes a good consultant ?
Patience, hard work and diplomacy. One of my pet hates is to see consultants surfing the net in Client time, but there are other things which are more subtle like remaining positive about Analytics even when it does do what you want - never let the client see you undermine the product as it is our lifeblood; Also, Keep you politics to yourself, even outside the office. You are never off duty with a client.
Is it better to be an employee or go contracting ?
On balance, being an employee gives you a better lifestyle. Having spent half my working life employed the other half a contractor I am leaning towards the employed model. Whilst contracting I have spent so many nights and weekends doing accounts, payroll, tax returns, company returns, VAT returns, meetings with solicitors, accounts and HR people. On the face of it the financial benefits are greater, but this UK government has found many new ways to tax anyone who dares to start up a business, even if you get away with IR35 there is other taxes that they will apply, and There's no getting away from National Insurance. Better to find a good employer who cares about your development and sees a partnership between you than try to go it alone.
How do I pay my tax bill ?
Don't you just hate Tax, especially when the government don't have a clue how to spend the money wisely. I paid mine online, ontime; lets see if I still get a fine.
When is a good time to buys flights to Portugal ?
Last year. We saw prices on BA flights go up by the day in January. Trying to buy online the system failed several times, and at the very end of the process - all very annoying, but even worse when you log in the next day and the prices have gone up 20 pounds, and the next. Ended up paying 150 pounds more for the flights, and cut down the holiday by two days. Next time we book really early. Come on BA get your act together.
Where is the next big Analytics Project ?
I think I know! The UK analytics market is getting more mature due to the reduction in the number of new CRM installations - companies are concentrating on upgrades. The German market is going very strong so time to brush up on those accents. The majority of customers I am speaking to are interested in Marketing, which as you know has Analytics at it's core. The other hot topic is data quality - many projects are delayed or failed due to poor quality of data, so I'm seeing what can be done to smooth the process of migration and cleansing. If you have any views let me know.
Should I go on the Marketing Server Architect course ?
Oh yes. Like I just said, this area is building up a strong demand. As an Oracle partner we get discounts for Oracle courses so will be putting as many people as we can on the course.
What technical questions should I ask an Analytics candidate ?
I am forever interviewing people, mainly using technical questions, but also using some competency based q's
Here is a selection of questions that I ask (I have an AQS - Analytics Question System which I have extracted some questions from)
Questions for you to establish if they know anything about analytics
W0 Warehouse
W1 What schemas do you recommend for an Analytics system
W2 How do you save changes to data in SQL
W3 What is the structure of a PL/SQL script
W4 What tools do you use to manage databases
W5 What is the difference between an OLTP and an OLAP
W6 Do you use indexing on a staging table?
I0 Informatica
I1 How do you call a stored procedure in Informatica
I2 When do you use re-usable objects
I3 Where do you create your own mappings in the Siebel Informatica Repository
I4 How would you add a field from S_ORG_EXT_X to the warehouse
D0 DAC
D1 What is the main purpose of the DAC
D2 What objects are in the DAC
D3 How do you add an Index for a table (existing and not existing)
D4 How do you run a full load (not incremental)
D5 How does Change capture work
Con0 Analytics Config
Con1 Where do you change the config settings
Con2 How do you turning caching on
Con3 How do you add a new style
Con4 What default logging level is best for a 1000 seat company?
R0 Repository
R1 What are the three layers
R2 How do you set session variables and how do you set Repository variables?
R3 How do you update alias table definitions?
Web0 Webcat
Web1 How do you copy requests from one webcatalog to another?
Dash0 Dashboards
Dash1 How do you create a dashboard?
Dash2 How do you set the permissions on a dashboard
Ans0 Answers
Ans1 What is the Formulae to use in a request, to see a Repository Variable?
Del0 Delivers
Del1 Where are iBots stored ?
Mkt0 Marketing
Mkt1 What is a QLI ?
Int0 Integration
Int1 What is an Action link used for?
Too many questions, and not enough time to get all the answers (How many of the above do YOU know?
Does Siebel Analytics work on IE7 ?
Is there a version 8 of Analytics, if so what is it called ?
What is the difference between Siebel Analytics and Oracle BIEE ?
Where are all the best Analytics consultants ?
How do I find good crew for the next racing series ?
Is disconnected Analytics a good idea despite the technical challenges ?
How do you get more skilled in Analytics ?
What is the MAD system ?
What makes a good consultant ?
What does Siebel 8 look like ?
Is it better to be an employee or go contracting ?
When is a good time to buys flights to Portugal ?
Where is the next big Analytics Project ?
How do I pay my tax bill ?
Should I go on the Marketing Server Architect course ?
What technical questions should I ask an Analytics candidate ?
I better stop there. Since my last blog things have been busy, very busy. During that time I have been trying to think of a subject to write about, but so many things come to mind. Should it be technical, personal (to show that I'm a nice guy!) or political (don't get me started). So I'm going to give my views on all of the above.
Why have I not written a blog for ages ?
Because I went on a skiing holiday, went live at the Royal Mal on their 7.8 project, spent every weekend taking the kids sailing, did my tax return, invoiced clients, did my LTd company accounts for 2005/2006, had Xmas lunches and dinners(the Majendi one was really good), watched some rugby, developed the MAD system, had a kids night out due to sons birthday, paid lots of bills, updated the Majendi Accounts system, interviewed some candidates, ran some Analytics master classes and finally got some sleep. phew!
Does Siebel Analytics work on IE7 ?
I installed Analytics on my new laptop this week and it seemed to work fine. I've had some trouble with Oracle 10g Enterprise manager. It became corrupted so I had to uninstall everything and start again. EM is now a web based system which looks much better than the previous version and is easy to use but this corruption has been a royal pain.
Is there a version 8 of Analytics, if so what is it called ?
I don't think so. I've had a good look at the Oracle website and can still only find 7.8.5. I'll keep my eye open for news on this front, but let me know if you find anything.
What is the difference between Siebel Analytics and Oracle BIEE ?
Good question. I installed both this week on my Laptop, BIEE first. The first product worked well except the manual said the username/pwd was admin/admin, turns out it was Administrator/Administrator - Thanks! I liked the xml Publisher which is new, but the rest was mainly a renamed Analytics system, placed into a different folder structure, for example, C:\OracleBI\Server\.
Oracle BIEE looks like it's just a replacement for Analytics Platform, I can't see where you get the applications part from, other than installing Analytics Applications itself. When I tried this, it recognised that there was an existing Analytics platform so didn't create it owns structures (e.g. C:\SiebelAnalytics). I'll investigate more this week so let me know if you have a specific issues.
(I'm off to check Mark Rittman blog for more details)
Where are all the best Analytics consultants ?
I wish I knew. Majendi is always looking for consultants who have lots of experience with Analytics. I am looking for permanent consultants for Inforte. If you know of anyone who has at least a couple of Analytics project sunder their belts then please let me know ASAP.
How do I find good crew for the next racing series ?
I nearly didn't enter the Warsash Spring Series due to lack of crew. Biggest problem is that one of our team is getting married in the middle of the series, even worse, he's having a stag do the week before so taking out both weekends.
I would like to be competitive so need the best possible sailors. After some badgering I've now got a team that can compete most weeks so we should get better as the series progresses. Follow our results at WSS.
Is disconnected Analytics a good idea despite the technical challenges ?
I still don't think so. In the age of 3G and broadband everywhere it must be far better to get people to work online than go through the major hassle of disconnected Analytics
How do you get more skilled in Analytics ?
I read the ITToolbox posting with a mixture of glee and annoyance. So many beginners are being placed on analytics projects that clearly have not been on a course. They ask stupid questions which demonstrate a laziness to read the manuals, support web and even previous toolbox postings. The consulting firms that hire these people are not investing properly in their people and dong their clients a disservice. The clients who outsource to the cheapskates get the system they deserve, but the cost is that the general image of Analytics is undermined and investment is moved to other reporting tools. As an industry we really are shooting ourselves in the foot. I would like to see Oracle step in and really classify their partners so the clients know what they are getting.
What is the MAD system ?
It's a brilliant Analytics Design and Documenting system that we use to design a and document Analytics systems. It will analyse your current system and look for redundancy and flaws. It saves me weeks in a project and removes the boring work. I'm trying to get some reverse engineering in there now, along with embedded Visio diagrams.
Perhaps I should sell it?
What makes a good consultant ?
Patience, hard work and diplomacy. One of my pet hates is to see consultants surfing the net in Client time, but there are other things which are more subtle like remaining positive about Analytics even when it does do what you want - never let the client see you undermine the product as it is our lifeblood; Also, Keep you politics to yourself, even outside the office. You are never off duty with a client.
Is it better to be an employee or go contracting ?
On balance, being an employee gives you a better lifestyle. Having spent half my working life employed the other half a contractor I am leaning towards the employed model. Whilst contracting I have spent so many nights and weekends doing accounts, payroll, tax returns, company returns, VAT returns, meetings with solicitors, accounts and HR people. On the face of it the financial benefits are greater, but this UK government has found many new ways to tax anyone who dares to start up a business, even if you get away with IR35 there is other taxes that they will apply, and There's no getting away from National Insurance. Better to find a good employer who cares about your development and sees a partnership between you than try to go it alone.
How do I pay my tax bill ?
Don't you just hate Tax, especially when the government don't have a clue how to spend the money wisely. I paid mine online, ontime; lets see if I still get a fine.
When is a good time to buys flights to Portugal ?
Last year. We saw prices on BA flights go up by the day in January. Trying to buy online the system failed several times, and at the very end of the process - all very annoying, but even worse when you log in the next day and the prices have gone up 20 pounds, and the next. Ended up paying 150 pounds more for the flights, and cut down the holiday by two days. Next time we book really early. Come on BA get your act together.
Where is the next big Analytics Project ?
I think I know! The UK analytics market is getting more mature due to the reduction in the number of new CRM installations - companies are concentrating on upgrades. The German market is going very strong so time to brush up on those accents. The majority of customers I am speaking to are interested in Marketing, which as you know has Analytics at it's core. The other hot topic is data quality - many projects are delayed or failed due to poor quality of data, so I'm seeing what can be done to smooth the process of migration and cleansing. If you have any views let me know.
Should I go on the Marketing Server Architect course ?
Oh yes. Like I just said, this area is building up a strong demand. As an Oracle partner we get discounts for Oracle courses so will be putting as many people as we can on the course.
What technical questions should I ask an Analytics candidate ?
I am forever interviewing people, mainly using technical questions, but also using some competency based q's
Here is a selection of questions that I ask (I have an AQS - Analytics Question System which I have extracted some questions from)
Questions for you to establish if they know anything about analytics
W0 Warehouse
W1 What schemas do you recommend for an Analytics system
W2 How do you save changes to data in SQL
W3 What is the structure of a PL/SQL script
W4 What tools do you use to manage databases
W5 What is the difference between an OLTP and an OLAP
W6 Do you use indexing on a staging table?
I0 Informatica
I1 How do you call a stored procedure in Informatica
I2 When do you use re-usable objects
I3 Where do you create your own mappings in the Siebel Informatica Repository
I4 How would you add a field from S_ORG_EXT_X to the warehouse
D0 DAC
D1 What is the main purpose of the DAC
D2 What objects are in the DAC
D3 How do you add an Index for a table (existing and not existing)
D4 How do you run a full load (not incremental)
D5 How does Change capture work
Con0 Analytics Config
Con1 Where do you change the config settings
Con2 How do you turning caching on
Con3 How do you add a new style
Con4 What default logging level is best for a 1000 seat company?
R0 Repository
R1 What are the three layers
R2 How do you set session variables and how do you set Repository variables?
R3 How do you update alias table definitions?
Web0 Webcat
Web1 How do you copy requests from one webcatalog to another?
Dash0 Dashboards
Dash1 How do you create a dashboard?
Dash2 How do you set the permissions on a dashboard
Ans0 Answers
Ans1 What is the Formulae to use in a request, to see a Repository Variable?
Del0 Delivers
Del1 Where are iBots stored ?
Mkt0 Marketing
Mkt1 What is a QLI ?
Int0 Integration
Int1 What is an Action link used for?
Too many questions, and not enough time to get all the answers (How many of the above do YOU know?
Monday, January 29, 2007
Just Another Date
Well there's lucky, Justin had dates in mind and so did I! I have been fortunate to
enough to have easy access to W_DAY_D and a plethora of pre-calculated YTD and
account period end figures so all plain sailing.
As we are working on Analytics for a Siebel Financial Services implementation we
have a large number of growth comparison figures and related % figures, all fairly
easy maths, divide one by the other and multiply by 100. If you are lucky enough the
have most of the figures pre calculated this is a quick and easy task, open the LTS
for your table, create a new column, open the expression builder and create you
expression to calculate the percentage. But beware, although analytics appears to
accept quite complex statements here, there are a few pitfalls, Siebel 7.8 does not
support nested aggregation statements so you can't for example do a SUM(Case.../
SUM(case....where ..Count(...... x100.
It will let you do Count(Case when...........) / Count(case when.....) x 100, but I
would recommend you avoid this, you may well find that if the resultant column is
included in a report with other similar columns the resulting SQL can look very odd
indeed and may not be an accurate reflection of what you originally intended, this
appears especially true if the report also includes other columns sum or count
aggregation set. Look at the SQL issued via your Admin/Manage Sessions option to see
the resulting SQL.
I would recommend creating seperate calculated columns and then a final column based
on them, to provide the result from a simple expression, this may add a small
processing overhead, the sql will be a little longer, but it will be clearer and
easier to trouble shoot. You will also have reusable metrics that may be of use in
further reports or perhaps to your client should they implement ad-hoc reporting in
the future.
Next up was small issue concerning the year, or last year to be precise, as our
current project is Siebel Financials, we started off with variables for this year,
last year, two years ago and so on, whilst these appear to offer everything you
might need, beware. Come the first week of January and you may think 'hang on a
minute, this says Dec '07, '06 and '05', this is the consequence of a report that
uses THIS_YEAR, LAST_YEAR and LAST_MONTH from the date variables to report on the
last complete month and previous years. The reporting is always a month behind, so
in January '07 they are reporting on Dec '06, the solution of course is easy, and
there are probably many variations on the theme, we have used a case statement added
to the Initialization block to recognise when the month is January, eg
Case when CURRENT_MONTH='1' then YEAR(GETDATE())-1 ELSE YEAR(GETDATE())
Use this for suitably named variable such LAST_REPORT_YEAR, if you need to report
further back use the VALUEOF(LAST_REPORT_YEAR)-1 etc to ensure the other years are
in sync with the current year.
The final part of these date related fun and games has been creating a report that
looks 3 months into the future, a rare requirement judging by the various message
boards etc which are stuffed with date range queries relating to periods past. We
decided to use more variables, 1 because we like variables and 2 because should the
business ever decide they wish to report on 6 months in the future they can either
edit the init block or use it as a template to create a new range.
We used the following query in our init block and created two variables called
THREE_MONTH_START and THREE_MONTH_END;
SELECT Q1.ST , Q2.END FROM
(SELECT CONVERT(DATETIME,(LEFT(CONVERT(VARCHAR(10),GETDATE(),112),6))+'01') AS ST) Q1,
(SELECT DATEADD(DAY,
-1,DATEADD(MONTH,+3,(LEFT(CONVERT(VARCHAR(10),GETDATE(),112),6))+'01')) AS END) Q2
As an alternative to this you could of course use similar SQL in an expression based
filter for dates between X & Y.
enough to have easy access to W_DAY_D and a plethora of pre-calculated YTD and
account period end figures so all plain sailing.
As we are working on Analytics for a Siebel Financial Services implementation we
have a large number of growth comparison figures and related % figures, all fairly
easy maths, divide one by the other and multiply by 100. If you are lucky enough the
have most of the figures pre calculated this is a quick and easy task, open the LTS
for your table, create a new column, open the expression builder and create you
expression to calculate the percentage. But beware, although analytics appears to
accept quite complex statements here, there are a few pitfalls, Siebel 7.8 does not
support nested aggregation statements so you can't for example do a SUM(Case.../
SUM(case....where ..Count(...... x100.
It will let you do Count(Case when...........) / Count(case when.....) x 100, but I
would recommend you avoid this, you may well find that if the resultant column is
included in a report with other similar columns the resulting SQL can look very odd
indeed and may not be an accurate reflection of what you originally intended, this
appears especially true if the report also includes other columns sum or count
aggregation set. Look at the SQL issued via your Admin/Manage Sessions option to see
the resulting SQL.
I would recommend creating seperate calculated columns and then a final column based
on them, to provide the result from a simple expression, this may add a small
processing overhead, the sql will be a little longer, but it will be clearer and
easier to trouble shoot. You will also have reusable metrics that may be of use in
further reports or perhaps to your client should they implement ad-hoc reporting in
the future.
Next up was small issue concerning the year, or last year to be precise, as our
current project is Siebel Financials, we started off with variables for this year,
last year, two years ago and so on, whilst these appear to offer everything you
might need, beware. Come the first week of January and you may think 'hang on a
minute, this says Dec '07, '06 and '05', this is the consequence of a report that
uses THIS_YEAR, LAST_YEAR and LAST_MONTH from the date variables to report on the
last complete month and previous years. The reporting is always a month behind, so
in January '07 they are reporting on Dec '06, the solution of course is easy, and
there are probably many variations on the theme, we have used a case statement added
to the Initialization block to recognise when the month is January, eg
Case when CURRENT_MONTH='1' then YEAR(GETDATE())-1 ELSE YEAR(GETDATE())
Use this for suitably named variable such LAST_REPORT_YEAR, if you need to report
further back use the VALUEOF(LAST_REPORT_YEAR)-1 etc to ensure the other years are
in sync with the current year.
The final part of these date related fun and games has been creating a report that
looks 3 months into the future, a rare requirement judging by the various message
boards etc which are stuffed with date range queries relating to periods past. We
decided to use more variables, 1 because we like variables and 2 because should the
business ever decide they wish to report on 6 months in the future they can either
edit the init block or use it as a template to create a new range.
We used the following query in our init block and created two variables called
THREE_MONTH_START and THREE_MONTH_END;
SELECT Q1.ST , Q2.END FROM
(SELECT CONVERT(DATETIME,(LEFT(CONVERT(VARCHAR(10),GETDATE(),112),6))+'01') AS ST) Q1,
(SELECT DATEADD(DAY,
-1,DATEADD(MONTH,+3,(LEFT(CONVERT(VARCHAR(10),GETDATE(),112),6))+'01')) AS END) Q2
As an alternative to this you could of course use similar SQL in an expression based
filter for dates between X & Y.
Thursday, January 11, 2007
Date Formats in the Analytics Application
Justin Townsend, a member of the Majendi team has some useful advice...
Dates are often a point of consternation - for users and developers alike - in the
analytics application, but there are many reasons why they are used. Dates and
their derivations are often drawn from the warehouse table W_DAY_D, but what do you
do when this table isn't available in your subject area or your repository?
This post gives an example of how dates can be manipulated to provide the format you
need. Additionally, the example format below can be used to sort data correctly at
this level of aggregation (Year/Month).
Take the example of a warehouse table with a date present, e.g. W_ASSET_D.END_DT.
The corresponding column in Answers could simply be 'End Date'.
Select the 'End Date' and hit the Results tab, you probably get something in a
format similar to this: 09-Sep-07. The format isn't so important here. When you
make changes to the date from Answers, the function is shipped down to the database
in any case. Its important though, to get your syntax right.
So we're going to create the format YYYY / MM from the date 09-Sep-07. You need a
second column in your request that you can use to create the new format, any column
will do.
We can break this date function up into a few sections:
- we need the 4-digit year (YYYY), so we can use the standard Analytics function
called Year and we get a syntax like:
Year(End Date), which gives you 2007
- then we need the constant '/'. It looks like a bit of space either side would
make it more readable, so be sure to include that. So now it probably looks a bit
more like ' / '.
- next we need the 2 digit month (MM), so we can use the standard function called
Month and the syntax looks like this:
Month(End Date), which gives you 9. That's OK, but if the date was in October then
it would be 10 so it doesn't look even. We straighten that out later on.
- then we need to bring the 3 parts above together using concatenation. The CONCAT
function will help us here. It only likes string formats so we need to convert the
date bits to text, something like:
- CAST(Year(End Date) as VARCHAR(4))
- CAST(Month(End Date) as VARCHAR(2))
N.B. The month has been cast as width 2 to account for 10, 11, 12.
Once we've cast the dates, then we can add them to a CONCAT function below:
CONCAT(CAST(Year(End Date) as VARCHAR(4))|| ' / ', CAST(Month(End Date) as
VARCHAR(2)))
You'll notice a couple of things about the expression, we're using some
double-pipes and there's only 1 comma. The Analytics CONCAT function works only
with 2 strings separated by a comma, so CONCAT('String1','String2'). The way to
add more sections to the string is by using the || (double pipes). There appears
to be no limit on the use of double pipes.
So this format is beginning to look pretty good, for 09-Sep-07, I now get '2007 /
9'. It's not quite perfect though, with dates in a list it will not look even when
compared with date '2007 / 10':
2007 / 9
2007 / 10
We can make a change to the string after the comma to add a leading zero to all
months that aren't 2-digits long.
- so a CASE statement is needed to complete the format to our satisfaction:
CASE WHEN Month(End Date) < 10 THEN CONCAT('0', CAST(Month(End Date) as
VARCHAR(1))) ELSE CAST(Month(End Date) as VARCHAR(2)) END
To finish this off, we insert it into the original CONCAT statement after the comma
and before the final ')', the resulting expression is:
CONCAT(CAST(Year(End Date) as VARCHAR(4))|| ' / ', CASE WHEN Month(End Date) < 10
THEN CONCAT('0', CAST(Month(End Date) as VARCHAR(1))) ELSE CAST(Month(End Date) as
VARCHAR(2)) END)
Now all our dates look the same, '2007 / 09' and '2007 / 10'.
If you know you're repository developer well, ask him to add the logic to the
repository. That way you won't have to reproduce it every time you need it.
Look out for more on dates in the future.
Thanks to Justin Townsend for this blog.
Dates are often a point of consternation - for users and developers alike - in the
analytics application, but there are many reasons why they are used. Dates and
their derivations are often drawn from the warehouse table W_DAY_D, but what do you
do when this table isn't available in your subject area or your repository?
This post gives an example of how dates can be manipulated to provide the format you
need. Additionally, the example format below can be used to sort data correctly at
this level of aggregation (Year/Month).
Take the example of a warehouse table with a date present, e.g. W_ASSET_D.END_DT.
The corresponding column in Answers could simply be 'End Date'.
Select the 'End Date' and hit the Results tab, you probably get something in a
format similar to this: 09-Sep-07. The format isn't so important here. When you
make changes to the date from Answers, the function is shipped down to the database
in any case. Its important though, to get your syntax right.
So we're going to create the format YYYY / MM from the date 09-Sep-07. You need a
second column in your request that you can use to create the new format, any column
will do.
We can break this date function up into a few sections:
- we need the 4-digit year (YYYY), so we can use the standard Analytics function
called Year and we get a syntax like:
Year(End Date), which gives you 2007
- then we need the constant '/'. It looks like a bit of space either side would
make it more readable, so be sure to include that. So now it probably looks a bit
more like ' / '.
- next we need the 2 digit month (MM), so we can use the standard function called
Month and the syntax looks like this:
Month(End Date), which gives you 9. That's OK, but if the date was in October then
it would be 10 so it doesn't look even. We straighten that out later on.
- then we need to bring the 3 parts above together using concatenation. The CONCAT
function will help us here. It only likes string formats so we need to convert the
date bits to text, something like:
- CAST(Year(End Date) as VARCHAR(4))
- CAST(Month(End Date) as VARCHAR(2))
N.B. The month has been cast as width 2 to account for 10, 11, 12.
Once we've cast the dates, then we can add them to a CONCAT function below:
CONCAT(CAST(Year(End Date) as VARCHAR(4))|| ' / ', CAST(Month(End Date) as
VARCHAR(2)))
You'll notice a couple of things about the expression, we're using some
double-pipes and there's only 1 comma. The Analytics CONCAT function works only
with 2 strings separated by a comma, so CONCAT('String1','String2'). The way to
add more sections to the string is by using the || (double pipes). There appears
to be no limit on the use of double pipes.
So this format is beginning to look pretty good, for 09-Sep-07, I now get '2007 /
9'. It's not quite perfect though, with dates in a list it will not look even when
compared with date '2007 / 10':
2007 / 9
2007 / 10
We can make a change to the string after the comma to add a leading zero to all
months that aren't 2-digits long.
- so a CASE statement is needed to complete the format to our satisfaction:
CASE WHEN Month(End Date) < 10 THEN CONCAT('0', CAST(Month(End Date) as
VARCHAR(1))) ELSE CAST(Month(End Date) as VARCHAR(2)) END
To finish this off, we insert it into the original CONCAT statement after the comma
and before the final ')', the resulting expression is:
CONCAT(CAST(Year(End Date) as VARCHAR(4))|| ' / ', CASE WHEN Month(End Date) < 10
THEN CONCAT('0', CAST(Month(End Date) as VARCHAR(1))) ELSE CAST(Month(End Date) as
VARCHAR(2)) END)
Now all our dates look the same, '2007 / 09' and '2007 / 10'.
If you know you're repository developer well, ask him to add the logic to the
repository. That way you won't have to reproduce it every time you need it.
Look out for more on dates in the future.
Thanks to Justin Townsend for this blog.
Subscribe to:
Posts (Atom)
The Cowes
Cowes Racing
Posts
