You didn't miss Part I, at least not here you didn't.
Many of you know Mike Riley. If you don't, here's a little history. He's the past president of ODTUG (for like 37 years or something) and for the last two years, he's served as Conference Chair for Kscope. Yeah, that doesn't really follow, but you know I'm a bit...scattered.
Did you read the link above? OK, well, here's the skinny. Mike has rectal cancer. Stage III. If it weren't for the stupid cancer part, the jokes would abound. Oh wait, they do anyway. Mike was diagnosed shortly after #kscope13, right around his 50th birthday (Happy Birthday Mike, Love, Cancer!). Ugh. (I want to say, "are you shittin' me?" see what I mean about the jokes? I can't help myself, I'm 14). Needless to say, cancer isn't really a joke. We all know someone affected by it. It is...well, it's not fun.
Go read his post if you haven't already. I'm going to give my version of that story. I'll wait...
So, Sunday morning, Game 3 of the World Series went to the Cardinals in a very bizarre way. I was watching highlights that morning as I had missed the end of the game (doesn't everyone know that I'm old and can't stay up that late to watch baseball?). Highlights. Mike lives in St. Louis. He's a Cardinal's fan. Wouldn't it be cool if he and his family could go to the game (mostly just his family, I don't like Mike that much). So I make some phone calls to see what people think of my idea. My idea is met with resistance. OK, I'll skip the people. Let's call Lisa (Mike's wife).
Apparently Sunday's are technology free days in the Riley household, no response. I go for a bike ride, but I take my phone, just in case Lisa calls me back. After the halfway point, my phone rings, I jump off the bike to answer.
So I talked to Lisa about my idea, can Mike handle the chaos of a World Series game?
We hang up and she goes to work. BTW, I asked her to keep my name out of it, but she didn't. We'll have words about that in the future.
She calls back (I think, it may have been over text, 2 weeks is an eternity to me). "He doesn't think he can do it."
So I call Mike directly (Lisa had already spoiled the surprise.)
"What about Box seats? You know, where the people with top hats and monocles sit? Away from the rift-raft, much more comfortable and free food and beer."
Backstory. Mike had finished his first round of chemo less than a week before Sunday. To make things worse, he decided it was a good time to throw out his back. He wasn't in the best of shape.
Mike said he thought he could do it.
OK, nay-sayers aside, let's see what we can do. I emailed approximately 50 people, mostly ODTUG people; board members, content leads, anyone I had in my address book. "Hey, wouldn't it be great to send Mike and his family to Game 5 of the World Series? We need to do this quick, tickets will probably double in price tonight especially if the Cardinals win." (that would mean Game 5 would be a clincher for the Cardinals, at home, muy expensive).
Within about 20 minutes, a couple of people pledged $600.
Holy shit!
At the prices I had seen, I was hoping to get between $50 and $100 from 50 people, hoping. I had $600 already. Game starts. Now it's up to $1100 in pledges. Holy shit, Part II. This might just be possible. Another 30 minutes and were about an hour into Game 4. Ticket prices have already gone up by $250 a ticket. Given that maybe 4 people have responded and I have $1600 in pledges, I pull the trigger. I bought 4 box seat tickets for the Riley family. (I had to have a couple of beers because I was about to drop a significant chunk of change without actual cash in hand, I could be out a lot of money, liquid courage is awesome).
Tickets sent to the Riley family. Pretty good feeling.
Like I said, I was confident, but I was scared. Before the end of the night though, there was over $5K pledged to get Mike and family to Game 5. Holy shit, Part III.
By midday Monday, pledges were well over $7K. I'll refer you back to Mike's post for more details. Shorter: jerseys for the family and a limo to the game.
Here's the breakdown: 35 people pledged, and paid, $8,080. Holy shit, Part IV. Average donation was $230.86. Median was $200. Low was $30 and high was $1000. Six people gave $500 or more. Nineteen people gave $200 or more. The list is a veritable Who's Who in the Oracle community.
Tickets + Jerseys + Limo = $6027.76
Riley family memory = Priceless.
So, what happened to the rest? Well, they have bills. Lots of bills. With the remainder, $2052.24, we paid off some hospital bills of $1220.63. There is currently $831.61 that will be sent shortly. It doesn't stop there though. Cancer treatment is effing expensive. Mike has surgery in December. He'll be on bed rest for some time. His bed is 17 years old. He needs a new one. After that, more chemo and more bills.
"Hey Chet, I'd love to help the Riley family out, can I give you my money for them?"
Yes, absolutely. Help me help them. I started a GoFundMe campaign. Goal is $10K. Any and all donations are welcome. Gifts include a thank you card from the Riley family and the knowledge that you helped out a fellow Oracle (nerd, definitely a nerd) in need. You can find the campaign here.
If you can't donate money, I've also created a hashtag so that we can show support for Mike and his family. It's #fmcuta (I'll let you figure out what it means). Words of encouragement are welcome and appreciated.
Thank you to the 35 who have already so generously given. Thank you to the rest of you who will donate or send out (rude) tweets.
Tuesday, November 12, 2013
Wednesday, November 6, 2013
Fun with SQL - My Birthday
This year is kind of fun, my birthday is on November 12th (next Tuesday, if you want to send gifts). That means it will fall on 11/12/13. Even better perhaps, katezilla's birthday is December 13th. 12/13/14. What does this have to do with SQL?
Someone mentioned to me last night that this wouldn't happen again for 990 years. I was thinking, "wow, I'm super special now (along with the other 1/365 * 6 billion people)!" Or am I? I had to do the math. Since date math is hard, and math is hard, and I'm good at neither, SQL to the rescue.
That query gives me this:
Someone mentioned to me last night that this wouldn't happen again for 990 years. I was thinking, "wow, I'm super special now (along with the other 1/365 * 6 billion people)!" Or am I? I had to do the math. Since date math is hard, and math is hard, and I'm good at neither, SQL to the rescue.
select to_number( to_char( sysdate + ( rownum - 1 ), 'mm' ) ) month_of, to_number( to_char( sysdate + ( rownum - 1 ), 'dd' ) ) day_of, to_number( to_char( sysdate + ( rownum - 1 ), 'yy' ) ) year_of, sysdate + ( rownum - 1 ) actual from dual connect by level <= 100000(In case you were wondering, 100,000 days is just shy of 274 years. 273.972602739726027397260273972602739726 to be more precise.)
That query gives me this:
MONTH_OF DAY_OF YEAR_OF ACTUAL -------- ------ ------- ---------- 11 06 13 2013/11/06 11 07 13 2013/11/07 11 08 13 2013/11/08 11 09 13 2013/11/09 11 10 13 2013/11/10 11 11 13 2013/11/11 ...So how can I figure out where DAY_OF is equal to MONTH_OF + 1 and YEAR_OF is equal to DAY_OF + 1? In my head, I thought it would be far more complicated, but it's not.
select *
from
(
select
to_number( to_char( sysdate + ( rownum - 1 ), 'mm' ) ) month_of,
to_number( to_char( sysdate + ( rownum - 1 ), 'dd' ) ) day_of,
to_number( to_char( sysdate + ( rownum - 1 ), 'yy' ) ) year_of,
sysdate + ( rownum - 1 ) actual
from dual
connect by level <= 100000
)
where month_of + 1 = day_of
and day_of + 1 = year_of
order by actual asc
Which gives me:
MONTH_OF DAY_OF YEAR_OF ACTUAL -------- ------ ------- ---------- 11 12 13 2013/11/12 12 13 14 2014/12/13 01 02 03 2103/01/02 02 03 04 2104/02/03 03 04 05 2105/03/04 04 05 06 2106/04/05 05 06 07 2107/05/06 ...OK, so it looks closer to 100 years, not 990. Let's subtract. LAG to the rescue.
select
actual,
lag( actual, 1 ) over ( partition by 1 order by 2 ) previous_actual,
actual - ( lag( actual, 1 ) over ( partition by 1 order by 2 ) ) time_between
from
(
select
to_number( to_char( sysdate + ( rownum - 1 ), 'mm' ) ) month_of,
to_number( to_char( sysdate + ( rownum - 1 ), 'dd' ) ) day_of,
to_number( to_char( sysdate + ( rownum - 1 ), 'yy' ) ) year_of,
sysdate + ( rownum - 1 ) actual
from dual
connect by level <= 100000
)
where month_of + 1 = day_of
and day_of + 1 = year_of
order by actual asc
Which gives me:
ACTUAL PREVIOUS_ACTUAL TIME_BETWEEN ---------- --------------- ------------ 2013/11/12 2014/12/13 396 2103/01/02 32161 2104/02/03 397 2105/03/04 395 2106/04/05 397 2107/05/06 396 2108/06/07 398 2109/07/08 396 2110/08/09 397 2111/09/10 397 2112/10/11 397 2113/11/12 397 2114/12/13 396 2203/01/02 32161So, it looks like every 88 years it occurs and is followed by 11 consecutive years of matching numbers. The next time 11/12/13 and 12/13/14 will appear is in 2113 and 2114. Yay for SQL!
Tuesday, October 29, 2013
Why I'm voting for Danny Bryant and You Should Too
I'm talking about the ODTUG Board of Directors.
This.
That's really all you need isn't it?
Fine.
Today wraps up the voting period for the ODTUG Board of Directors. If you're asking me what ODTUG is, stop reading now. If you are a member of ODTUG, then please give me a few minutes to pontificate (that's a word I heard Jeff Smith use once, hopefully it makes sense here).
Your favorite Oracle conference, KScope, is largely successful based on the efforts of the Board, along with the expert advice of the YCC group. In addition, if you think ODTUG should "do more with Essbase" or "charge more for memberships" these decisions are made and carried out by the board.
So if you like being in ODTUG, and you want to help it get better and grow, and be as awesome as possible, you only need to do one thing today. Go vote. Midnight tonight (10/29) is the deadline. Do it.
You get to vote for several people. I suggest you read their bios. I'll save you the time for at least one vote, and that's for Danny Bryant.
Besides that awesome photo (#kscope12 in San Antonio) up above, here are several more reasons.
1. He's into everything. OBIEE. EBS. Essbase. SQL Developer. Database. Not very many people have their hands in everything, he does. He will be able to represent the entire spectrum of ODTUG members.
2. He's a fantastic human being. It's not just because he takes pictures of himself wearing ORACLENERD gear everywhere (doesn't hurt though), he's just, awesome.
3. This (Part II)
4. He also always answers the phone, tweets, and emails I send him. He might be sick, or he might just be that responsive. The ODTUG Board member responsibilities will fit nicely on his shoulders I believe.
So go vote. Now.
This.
That's really all you need isn't it?
Fine.
Today wraps up the voting period for the ODTUG Board of Directors. If you're asking me what ODTUG is, stop reading now. If you are a member of ODTUG, then please give me a few minutes to pontificate (that's a word I heard Jeff Smith use once, hopefully it makes sense here).
Your favorite Oracle conference, KScope, is largely successful based on the efforts of the Board, along with the expert advice of the YCC group. In addition, if you think ODTUG should "do more with Essbase" or "charge more for memberships" these decisions are made and carried out by the board.
So if you like being in ODTUG, and you want to help it get better and grow, and be as awesome as possible, you only need to do one thing today. Go vote. Midnight tonight (10/29) is the deadline. Do it.
You get to vote for several people. I suggest you read their bios. I'll save you the time for at least one vote, and that's for Danny Bryant.
Besides that awesome photo (#kscope12 in San Antonio) up above, here are several more reasons.
1. He's into everything. OBIEE. EBS. Essbase. SQL Developer. Database. Not very many people have their hands in everything, he does. He will be able to represent the entire spectrum of ODTUG members.
2. He's a fantastic human being. It's not just because he takes pictures of himself wearing ORACLENERD gear everywhere (doesn't hurt though), he's just, awesome.
3. This (Part II)
4. He also always answers the phone, tweets, and emails I send him. He might be sick, or he might just be that responsive. The ODTUG Board member responsibilities will fit nicely on his shoulders I believe.
So go vote. Now.
Monday, October 21, 2013
Excel VS BI in Financial Analysis: why the fight was over before it started
by Victor Fagundo
The argument over why Businesses should abandon Excel in favor of more structured tools has been raging for as long as I have had more than a casual exposure to Oracle products. From the standpoint of an IT user Excel appears to be a simplistic, flat-file-based, error-prone tool that careless people use, despite its obvious flaws. Petabytes of duplicative Excel spreadsheets clog network drives across the globe; we as IT users know it, and it drives us crazy. Why, oh why, can’t these analysts, project managers, and accountants not grasp the elegant beauty of a centralized database solution that ensures data integrity, security, and has the chops to handle gobs of data, and abandon their silly Excel sheets?
I’ll tell you why: Excel is better. Excel the most flexible and feature-rich tool for organizing and analyzing data. Ever. Period.
For the past few years I have lived in a hybrid Finance/IT role, and in coming from IT, I was shocked at how much Excel was used, for everything. But after working with Excel on a daily basis for several years, I am a convert. An adept Excel user can out-develop any tool ( BI, Apex, Hyperion, Crystal Reports ) handily. (when dataset size is not an issue). Microsoft has done too much work on Excel, made it too extendable, too intuitive, built in so much, that no structured tool like BI, APEX, SAP, Hyperion will EVER catch up to its usability/flexibility.
Take this real-world example that came across my desk a few months ago: for a retail chain define a by-week, by-unit sales target, and create a report that compares actual sales to this target. Oh, and the weekly sales targets get adjusted each quarter based on current financial outlook.
How quickly could you turn around a DW/BI solution to this problem? What would it involve?
• Create table to house targets
• Create ETL process to load new targets
• Define BMM/Presentation Layers to expose targets
• Develop / test / publish report.
A day? Maybe? If one person handled all steps (unlikely, since the DB layers and RPD layers are probably handled by different people.)
I can tell you how long it took me in Excel: 3 hours (OBIEE driven data-dump, married with target sheet supplied to me). I love OBIEE, but Excel was still miles faster/more efficient for this task. And I could regurgitate 6 other examples like this one off at a moment’s notice.
Case in point: 95% of the data that C-level executives use to make strategic decisions is Excel based.
If you’ve ever sat in on a presentation to a CEO or other C-level executive at any medium to large sized company, you know that people are not bringing up dashboards, or any other applications. They are presenting PowerPoints with a few (less than 7) carefully massaged facts on them. If you trace the source of these numbers back down the rabbit hole, your first stop is always Excel. Within these Excel workbooks you will find “guesses” and “plugs” that fill gaps in solid data, to arrive at an actionable bit of information. It’s these “guesses” and “plugs” that are very hard to code for in an environment like OBIEE (or any other application). Can it be done? Yes, of course, with gobs of time and money. And during the fitful and tense development, the creditably of the application is going to take major hits.
Given the above, the usefulness of OBIEE might seem bleak. But I strongly feel that applications such as OBIEE do have a proper place in the upper organizational layers of modern business: Facilitating the Tactical business layers, and providing data-dumps to the Strategic Business layers.
Since this post is mainly about Excel, I will focus on how OBIEE can support the analyses that are inevitably going to be done in Excel.
Data Formatting, Data Formatting, FORMATTING!! I can’t stress this enough. For an analyst, having to re-format numbers that come out of an export so that you can properly display them or drive calcs off them in Excel is infuriating, and wasteful. My favorite examples: in a BI environment I worked with percentages were exported as TEXT, so while they looked fine in the application, as soon as you exported them to Excel and built calcs off them, your answer was overstated by a factor of 100 (Excel understood “75%” to be the number 75 with a text character appended, not the number 0.75).
Ask your users how they would like to SEE a fact in Excel: with decimals or not? With commas or not? Ensure that when exported to Excel, facts and attributes function correctly.
“Pull” refreshes of information sources in Excel. In the finance world, most Excel workbooks are low to medium complexity financial models, based off a data-dump from a reporting system. When the user wants to refresh the model, they refresh the data-dump, and the Excel calculations do the rest. OBIEE currently forces a user to “push” a new data-dump by manually running/exporting from OBIEE and then pasting the data into the data-dump tab in the workbook. What an Excel user really desires is to have a data dump that can be refreshed automatedly, using values that exist on other parts of the workbook to define filters of data-dump. Then all the user needs to do is trigger a “pull” and everything else is automated. Currently OBIEE has no solution to this problem that is elegant enough for the common Excel user. (Smartview must have its filters defined explicitly in the Smartview UI each time an analysis is pulled.)
The important part to take away from these 2 suggestions, and this entire post, is that to maximize the audience of OBIEE, we must acknowledge that Excel is the preferred tool of the Finance department, due to its flexibility, and support friendly exports to Excel as a best practice. We must also understand that accounting for this flexibility in OBIEE is daunting, and probably not the best use of the tool. If your users are asking for a highly complex attribute or fact, that is fraught with exceptions and estimations, chances are they are going to be much happier if what you give them is reliable information in a data-dump form, and allow them to handle the exceptions and estimations in Excel.
The argument over why Businesses should abandon Excel in favor of more structured tools has been raging for as long as I have had more than a casual exposure to Oracle products. From the standpoint of an IT user Excel appears to be a simplistic, flat-file-based, error-prone tool that careless people use, despite its obvious flaws. Petabytes of duplicative Excel spreadsheets clog network drives across the globe; we as IT users know it, and it drives us crazy. Why, oh why, can’t these analysts, project managers, and accountants not grasp the elegant beauty of a centralized database solution that ensures data integrity, security, and has the chops to handle gobs of data, and abandon their silly Excel sheets?
I’ll tell you why: Excel is better. Excel the most flexible and feature-rich tool for organizing and analyzing data. Ever. Period.
For the past few years I have lived in a hybrid Finance/IT role, and in coming from IT, I was shocked at how much Excel was used, for everything. But after working with Excel on a daily basis for several years, I am a convert. An adept Excel user can out-develop any tool ( BI, Apex, Hyperion, Crystal Reports ) handily. (when dataset size is not an issue). Microsoft has done too much work on Excel, made it too extendable, too intuitive, built in so much, that no structured tool like BI, APEX, SAP, Hyperion will EVER catch up to its usability/flexibility.
Take this real-world example that came across my desk a few months ago: for a retail chain define a by-week, by-unit sales target, and create a report that compares actual sales to this target. Oh, and the weekly sales targets get adjusted each quarter based on current financial outlook.
How quickly could you turn around a DW/BI solution to this problem? What would it involve?
• Create table to house targets
• Create ETL process to load new targets
• Define BMM/Presentation Layers to expose targets
• Develop / test / publish report.
A day? Maybe? If one person handled all steps (unlikely, since the DB layers and RPD layers are probably handled by different people.)
I can tell you how long it took me in Excel: 3 hours (OBIEE driven data-dump, married with target sheet supplied to me). I love OBIEE, but Excel was still miles faster/more efficient for this task. And I could regurgitate 6 other examples like this one off at a moment’s notice.
Case in point: 95% of the data that C-level executives use to make strategic decisions is Excel based.
If you’ve ever sat in on a presentation to a CEO or other C-level executive at any medium to large sized company, you know that people are not bringing up dashboards, or any other applications. They are presenting PowerPoints with a few (less than 7) carefully massaged facts on them. If you trace the source of these numbers back down the rabbit hole, your first stop is always Excel. Within these Excel workbooks you will find “guesses” and “plugs” that fill gaps in solid data, to arrive at an actionable bit of information. It’s these “guesses” and “plugs” that are very hard to code for in an environment like OBIEE (or any other application). Can it be done? Yes, of course, with gobs of time and money. And during the fitful and tense development, the creditably of the application is going to take major hits.
Given the above, the usefulness of OBIEE might seem bleak. But I strongly feel that applications such as OBIEE do have a proper place in the upper organizational layers of modern business: Facilitating the Tactical business layers, and providing data-dumps to the Strategic Business layers.
Since this post is mainly about Excel, I will focus on how OBIEE can support the analyses that are inevitably going to be done in Excel.
Data Formatting, Data Formatting, FORMATTING!! I can’t stress this enough. For an analyst, having to re-format numbers that come out of an export so that you can properly display them or drive calcs off them in Excel is infuriating, and wasteful. My favorite examples: in a BI environment I worked with percentages were exported as TEXT, so while they looked fine in the application, as soon as you exported them to Excel and built calcs off them, your answer was overstated by a factor of 100 (Excel understood “75%” to be the number 75 with a text character appended, not the number 0.75).
Ask your users how they would like to SEE a fact in Excel: with decimals or not? With commas or not? Ensure that when exported to Excel, facts and attributes function correctly.
“Pull” refreshes of information sources in Excel. In the finance world, most Excel workbooks are low to medium complexity financial models, based off a data-dump from a reporting system. When the user wants to refresh the model, they refresh the data-dump, and the Excel calculations do the rest. OBIEE currently forces a user to “push” a new data-dump by manually running/exporting from OBIEE and then pasting the data into the data-dump tab in the workbook. What an Excel user really desires is to have a data dump that can be refreshed automatedly, using values that exist on other parts of the workbook to define filters of data-dump. Then all the user needs to do is trigger a “pull” and everything else is automated. Currently OBIEE has no solution to this problem that is elegant enough for the common Excel user. (Smartview must have its filters defined explicitly in the Smartview UI each time an analysis is pulled.)
The important part to take away from these 2 suggestions, and this entire post, is that to maximize the audience of OBIEE, we must acknowledge that Excel is the preferred tool of the Finance department, due to its flexibility, and support friendly exports to Excel as a best practice. We must also understand that accounting for this flexibility in OBIEE is daunting, and probably not the best use of the tool. If your users are asking for a highly complex attribute or fact, that is fraught with exceptions and estimations, chances are they are going to be much happier if what you give them is reliable information in a data-dump form, and allow them to handle the exceptions and estimations in Excel.
Subscribe to:
Posts (Atom)