Friday, July 25, 2014

SQL Server RIGHT, LEFT, SUBSTRING vs. Oracle SUBSTR

I've been doing a lot of data analysis in both SQL Server and Oracle lately. Thus, it seemed like a good time for another SQL Server vs. Oracle differences post.

If you've first worked with SQL Server and then Oracle, you've probably looked for either the RIGHT or LEFT function in Oracle and been unable to find it. Instead Oracle has a powerful SUBSTR function that covers RIGHT, LEFT, and SUBSTRING from SQL Server. Below are some examples.

1) Get right four characters from a string.

SQL Server
Query: SELECT RIGHT('My favorite year is 1998', 4);
Result: 1998

Oracle
Query: SELECT SUBSTR('My favorite year is 1998', -4) FROM DUAL;

Note: Oracle allows a negative starting position with SUBSTR, thus allowing it to mimic the SQL Server RIGHT function.

2) Get left 18 characters from a string.

SQL Server
Query: SELECT LEFT('Bricktown is super and fun', 18);
Result: Bricktown is super

Oracle
Query: SELECT SUBSTR('Bricktown is super and fun', 1, 18) FROM DUAL;
Result: Bricktown is super

3) Get 14 characters starting at character 11

SQL Server
Query: SELECT SUBSTRING('The state Hawaii is warm and sunny', 11, 14);
Result: Hawaii is warm

Oracle: SELECT SUBSTR('The state Hawaii is warm and sunny', 11, 14) FROM DUAL;
Result: Hawaii is warm

Note: SQL Server's SUBSTRING function requires 3 arguments.

There you have it. Both the SQL Server and Oracle functions are pretty easy to use, as long as you can remember the syntax for the database you are using.

Tuesday, December 17, 2013

My First Pass Summit - Part 2

Part 1 of this post is available here.

Wednesday - Sessions and Exhibitor Reception


By the time Wednesday came, it was hard for me to comprehend that we were just officially starting the educational content of the core Summit. I'd already had an amazing two days on Monday and Tuesday.

Sessions

My first session was Erin Welker's BI Best Practices: The Good, the Bad, and the Ugly. This session ended up being the most valuable to me with respect to my current job. I'm currently a data designer for a major module of new data coming into our data warehouse. My job includes not only designing the data warehouse structures related to this new project but also includes interactions with the business. Erin covered some of both in her session.

 Erin Welker presenting on BI Best Practices: The Good, the Bad, and the Ugly

Packed house for my first ever Summit Session to attend!

Exhibitor Reception

The highlight of this evening event was a 30 minute Hadoop session that Hortonworks presented. They called it a demo, but it really was an excellent 30-minute Hadoop session that gave a great high level explanation of Hadoop. It's the best high level overview of Hadoop that I've personally seen and a few others mentioned that to me, as well. I was impressed enough by the session that I spoke to one of the Hortonworks representatives about possibly giving this presentation to OKCSQL. Bonus: Attendees of the "demo" got a free stuffed Hortonworks elephant. It was my favorite swag from any of the vendors.

Special thanks to Dell, Idera, and FatCloud who gave my husband swag even though his PASS Summit Guest Badge was not scannable. I.e. they weren't getting any contact information from him. Note: none of the guest badges were scannable.

Thursday - Sessions, WIT Panel, and Community Appreciation Party


Sessions

My favorite session of the day was Power BI: Modern Device and Data Search Experiences by Adam Wilson and Amy Forstrom from Microsoft. This mostly demystified the magic of Power BI Q&A. I talk more about their session in my blog post Power BI - The Latest from Summit.

Women in Technology Panel 

This panel on Beyond Stereotypes: Equality, Gender, Neutrality, and Valuing Diversity was probably the most valuable session I attended during the entire event. There was some amazing discussion during this one hour panel. The entire panel is available here. This panel went beyond gender stereotypes to talk about diversity in general. It included discussions about people being excluded or shunned by being "different" from the social norm of their employer. For example, you're into "geeky" things but work in a "non-geeky" field. Discussion included how to handle certain kinds of scenarios when diversity is not being accepted.




With WIT Panel member Gail Shaw. 
Next year I'm bringing one of my Star Wars shirts!



Community Appreciation Party

We had access to the NASCAR Hall of Fame from 7:00 p.m. - 10:00 p.m. for what turned out to be one of my favorite nights.

First, I got an awesome, free caricature done while my husband was in the food line. The caricature finished right before he got to the buffet.

 Look I'm a race car driver!

Second, I met some amazing people and had some great conversations! Once we got our food, we stood around one of the small tables and ate. Various people would come and go from the table. This led to great discussion with a lot of different people. One of the longer conversations was with Yaxing Liu from North Carolina who is interested in possibly starting a PASS Chapter. She's been running a virtual SQL group via Meetup for awhile now with consistent membership. Thus, we spoke for awhile about chapter leadership. We've already been in contact post-Summit, as I got her in contact with Karla Landrum.

Third, I had even more great conversations. One of my favorite conversations was with fellow science fiction fan Matt Slocum. I think his shirt caught my attention, but I really don't remember exactly why we started talking. We talked about a variety of science fiction shows that we both like, and he even recommended The 4400 to my husband and I. My husband and I actually started watching it the day after we got back from Summit. Since then (by end of November), we've watched the entire series, all 45 episodes.

Hanging with my new science fiction buddy Matt Slocum

With my wonderful husband!!!

Friday - Sessions and PASS Board Q&A

Sessions
I was really tired by Friday morning, and there was still another full day of sessions left. I actually made most of Rob Farley's 8 a.m. session, which meant I was on track to be able to attend several sessions. I did notice that the final two sessions of the day had lower than normal attendance, as many people were already heading home.

PASS Board Q&A

Going into Summit, I didn't even know there would be a PASS Board Q&A, but Andy Warren saw me and encouraged me to attend. The first thing I learned is that there are 14 directors, including two each from founding partners Computer Associates and Microsoft. I knew Microsoft was involved, but I had no idea that CA was a founding partner of PASS. There were some great questions asked by the audience, and I liked seeing the board do their best to answer them.



By the time the final session regular session ended on Friday, I was completely exhausted, so I headed back to the hotel room and stayed there until time to fly out the following morning.

I met so many great people that I haven't had a chance to follow-up with everyone I want to.

Advice I'm glad I took for this year


Attend the first-timers orientation - I considered skipping this event. I'd read tons of Summit advice blog posts and had been to SQL Rally. Other people had given me in-person advice, as well. This event was very much worth it. I received some excellent advice like getting to your sessions early, since they sometimes fill up.

Bring an empty suitcase for swag -  This was definitely worth it!

 This bag was empty when we brought it and full of free swag when we took it home!!! I could have gotten even more swag, but I chose to restrain myself as the bag continued to fill throughout the week. This bag includes 1 Hortonworks Green Elephant, 1 Azure Cat, 3 Idera ducks, multiple t-shirts, and more! Obtaining all 3 different Idera ducks was by far my hardest swag accomplishment of the week. You could only get duck per day, and Idera was quickly running out of certain colors.

What I would do differently next year


I would bring business cards - This was something I didn't get around to this year. I figured most people would be accessible later via Twitter (Twitter handles were on our name badges) or would have their own business cards. This was often the case but often not. Even though I felt like I was a late comer to Twitter, a lot of attendees were not on Twitter. In some cases if someone wasn't on Twitter and didn't have a card, I'd enter their information directly into my phone, but there were some cases where there wasn't time for this.

Fly in on Monday instead of attending a pre-con - I learned a lot of great information about SSRS from Stacia Misner during her From Reporting Services Rookie to Rockstar pre-con. As a data designer, though, it's unlikely that I'll be doing a lot of SSRS development in the near future. If there isn't a pre-con closely related to my current job, I'll probably skip out on a pre-con next year. I am glad I attended one this year, though.

Pick a Night to Turn in Extra Early - In this case, turn in means being back in the hotel room versus actually going to bed. I was out socializing Monday night - Thursday night. Even though I was in bed by around 12:30 a.m. each night, I was absolutely exhausted by Friday evening. Everyone has different limits, and in my case, I needed to take a night off and didn't. If the same schedule were to occur next year, I'd probably take Wednesday night off, once the Exhibitor's event was over. It ended at 8 p.m. this year.

Tuesday, November 12, 2013

My First PASS Summit - Part 1 (Monday and Tuesday)

A couple of weeks ago, I finally got to attend my first PASS Summit! A lot of great conversations, connecting, and learning occurred. So much so that it took about a week for me to process the experience enough to considering blogging about it as a whole. In the week immediately following Summit my blog posts were Power BI - The Latest from Summit and Region Shading and Custom Coloring in Power Map. Both posts were inspired by content I heard about at Summit.

It's now now been exactly four weeks since Summit officially started, and I've finally had the processing time and blogging time to post about the first 2 days.

Highlights 

Monday

SSRS Pre-con

It was my first Summit, so I wanted to tryout a day of jam-packed learning on a single topic. I attended Stacia (pronounced Stay-sha) Misner's From Reporting Services Rookie to Rockstar pre-con. I wasn't very familiar with SSRS, so this was an excellent session for me to get a high level overview of SSRS and its major capabilities.


With OKCSQL VP Matt Brimer (@SQLCenturion)

Socials

Hanging with Tim Mitchell, my husband Jeff, and Matt Brimer



It was great to catch up with Julie Koesmarno (@mssqlgirl). We're outside my hotel's bar where about 30 of us converged shortly beforehand. The pub we, previously, tried to go to was already too full from other Summit attendees.
 

Tuesday Day - SQL Saturday Round Table and Chapter Leader's Meeting

Tuesday started with Rob Farley leading a lot of SQL Saturday organizers in discussions about changes to SQL Saturday rules and what kind of guidance organizers are looking for. Four of us from Oklahoma City that already knew each other ended up sitting together at a table. Although this led to some great internal discussion, it didn't lead to great networking. I did make some great contacts right after the meeting, though.

Tuesday afternoon was the chapter leaders meeting. I made a specific point to sit at a table where I didn't know anyone. That decision alone allowed me to meet Tim Radney (RM for South East - USA), Pinal Dave (RM for India), JK Wood (Chapter Officer from Missouri), and a couple of other chapter representives. Our table and then the overall meeting attendees had some great discussion about the benefits of PASS and where improvements could be made.  Afterwards we broke into our PASS regions and met with our RM's and other chapter leaders from our region.

Follow-up note: Last night JK drove four hours from Springfield, Missouri to provide an in-person presentation to OKCSQL.


Five of us are wearing "First Timer" ribbons
Hanging with fellow SC Chapter Representatives: Jeff Johnston (MSBIC), Ganesh Gopalakrishnan (NTSSUG), Jim Murphy (CACTUSS Central), Matt Brimer (Tulsa SQL), me (OKCSQL), Ryan Adams (RM), Curt Wolfe (Northwest Arkansas SQL Server Users Group) and Sri Sridharan (Board Member)


Tuesday Night - First Timers Orientation, Welcome Reception, Linchpin Party 

First Timers Orientation

Tuesday night was one of my favorite nights, and I almost skipped this first amazing part! I'd read tons of Summit advice blog posts and had been to SQL Rally. Other people had given me in-person advice, as well. I'm SO glad that I attended this event. I received some excellent additional advice related to Summit and got some amazing glow jewelry. My husband, a guest pass participant, felt a bit distracted at this particular event, so he walked around picking up more glowsticks and making me more jewelry. As for Summit proper advice, one key point was to arrive to sessions early, as they can completely fill up. Note: I attended mostly BI sessions and got there early enough to not miss any sessions. I typically arrived 15 minutes early. I always got into my sessions. I know several people who tried arriving at DBA sessions 10 minutes early and got turned away.

Showing off my glow jewelry at the First Timers Orientation!
The jewelry led to some great conversations throughout the evening.

Welcome Reception

We have two amazing Regional Mentors in the South Central Region. They are Ryan Adams and John Sterrett. John spent much of the welcome reception introducing myself and a few others around.

Linch Pin Private Party

I apparently know several people connected to Linch Pin, so I was lucky enough to get invited to their private vendor party that evening at Strike City! There was free bowling, appetizers, and beverages. I met a lot of great people that night and also caught up with existing SQLFamily. One of my favorite conversations was with Rob Farley whom I'd met at SQL Rally the year before.

I had a hard time comprehending that Summit officially started on Tuesday evening, since by the end of the night my Summit experience was 40% over. I'll cover the remaining days in later post(s).

Saturday, October 26, 2013

Region Shading and Custom Coloring in Power Map


Region Shading

At PASS Summit, I was excited to hear that Power Map added a Region Shading feature in the September Release. Out of SSRS, Power View, and Power Map, the only one to previously have region shading was SSRS. Now this feature is available in Power Map by country, province/state, county, and zip code.

I finally had a chance to play around with this new feature today, and thought I'd try it out against some data that I had previously Power Mapped. In a previous blog post, I discussed how you could use a city layer with column bars and a state/province layer with bubble charts to display data by both city and province.

Now you can use the new Region option shown below to implement region shading.


Below are the results with Region Shading

SQL Saturday OKC Registrations for 2012 by City and State


 September Release
Used new Region Shading and Custom Color Features
In the above example, the shading intensity represents the province's registration count relative to the other provinces.

Here's the April release results, without region shading.

April Release
There was no region shading, so the bubbles represented registration by province.


Custom Coloring

Post-Summit I heard about Power Map adding custom coloring in the September release. When initially trying out Power Map this is something I quickly wanted and expected end users would want as well. I was happy to see how quickly this got added. Below is how to use this feature.

In Layer Options, you may use the color option below to select the color to use per category value. The color selection option is available for region shading, column bars, and bubbles.


When trying this option out, I was glad to see so many colors available and that assignments were per category value. In the below example I had event year categories of 2007 - 2013. I was able to select a different color for each value.

SQL Saturdays in North America 
by Province by First Event Year
Used new Region Shading and Custom Color Features


Video: SQL Saturdays in North America 
by Province by First Event Year

Used the new Create Video feature in Power Map

Monday, October 21, 2013

Power BI - The Latest from Summit

One of my goals at PASS Summit 2013 was to learn more about Power BI. When Power BI was announced in July, I already had familiarity with Power Pivot, Power View, Power Query, and Power Map. Since then I've seen and experienced confusion related to what's part of Power BI. The following is some what I learned at Summit and what my new impressions are.

Initial Causes for Power BI Confusion
When Power BI was announced in July, Power Pivot, Power View, Power Query, and Power Map were all announced to be part of Power BI. Q&A was mentioned as a feature, as well.

Power Pivot, though, was already officially available for Office 2010/2013, and Power View was officially available for Office 2013. Both Power Map and Power Query had Excel 2013 add-ins in public preview. Thus, what did it mean for the first four items with the announcement of Power BI?

Demystification of Power BI
My current impression is that Power Pivot, Power View, Power Query, and Power Map may all stay available for on-premise self-service BI. Power BI for Office 365 is coming across as an upcoming subscription for Office 365 in the cloud that would eventually include Power Pivot, Power View, Power Query, Power Map, and Q&A. Q&A seems like a cloud only feature, and it seems like some non-Q&A functionality may come in the cloud first and then possibly go on-premise. Note: Last I heard Power Map was not scheduled to be in the GA for Power BI in the cloud.

The following are additional Power BI notes from Summit 2013:

Power BI Q&A Feature
When I first saw the Q&A feature of Power BI in the Power BI Office 365 July demo video, the Q&A feature mostly seemed like magic. After attending a full Summit session dedicated just to Power BI Q&A, I became much more excited about the feature. Here's some of what I learned.
  • Power BI Q&A is not magic
  • Power BI Q&A relies on Power Pivot models in the cloud
  • In the current Power BI Preview, you can try using the Q&A feature against some sample workbooks
  • If you want to run the Q&A feature against your own workbooks, you'll need to wait until the next Power BI Preview
  • You'll be able to "train" Q&A to tie search words to your Power Pivot models by changing the primary synonym and/or adding phrases. The primary synonym defaults based upon the column names in your Power Pivot model. I got the impression that it could automatically turn underscores into spaces.
  • When the user is typing in their search words, beneath the search the user sees the exact search that would be run.
  • If Q&A cannot understand a word typed into the search, the word is grayed out.
Like I mentioned earlier, after seeing Adam Wilson and Amy Forstrom's Power BI: Modern Device and Data Search Experience session, I'm much more excited about Power BI Q&A. If you have access to the Summit 2013 recordings, I highly recommend this session.

In addition, the Day 1 Keynote, had a Power BI Q&A demo at the 1 hour 15 minutes and 15 seconds mark. It is available for public viewing.

Power Query
  • There is a decent chance that the Power Query add-in for Excel on the desktop will remain free.
  • In order to publish a Power Query workbook for sharing, a Power BI subscription for Office 365 would be required. 
Day 1 Keynote (one hour and 5 minutes mark) shows using Power Query to import data from Windows Azure HDInsight

Power Map
Major additions from the September release:
  • Create Video (big yay!)
  • Region Shading - For countries, states/provinces, and counties
  • Flat Map
  • Update: Ari Schorr let me know about even more additional features from the September release. He  has a blogpost about them here. I can't wait to try them out!
Other Power Map notes:
  • Not expected to be in the GA of Power BI. This gives me hope that the Excel add-in for Power Map might remain free for Excel on the desktop while publishing to Office 365 might require a Power BI Subscription.
  • Requires DirectX10
  • It's not recommended to use Power Map on a VM. The visualization may not work properly or may not work at all. Update: This recommendation was apparently pre-September release, although, it was provided to me at Summit. It can happen. With the September release, Power Map supports the main VM's.
Power View
  • Microsoft is working on the capability for Power View in the cloud to work on native iPad. This was mentioned by a Microsoft employee at a Summit session on mobile BI. The employee spoke up after the speaker clearly was dancing around this point, as he did not think he was allowed to disclose it.
Office 2013 Click-to-Run vs. ISO Installation
It matters whether or not your Office 2013 installation was done using Click-to-Run or using an ISO. I believe the Click-to-Run version is available using the Power BI Preview, while the ISO version seems be available via MSDN. The Click-to-Run version updates automatically, thus, it would get the critical Power BI Preview 2 updates automatically like those needed to be able to train your Power Pivot models for Power BI Q&A. Last I heard Microsoft is aware of a possible need to get the Click-to-Run version out onto MSDN.

Thanks to everyone who provided some great Power BI information at Summit. Microsoft provided some great information via keynote and sessions. I had some great discussions with Microsoft employees at the SQL Server Clinic and in other environments. Thanks to Julie Koesmarno and others in the community for some great Power BI discussion, as well.

Tuesday, August 27, 2013

SQL Saturday Oklahoma City - Power Map Presentation

Thanks to everyone that attended my 3D Geospatial Visualization Using Power Map session at SQL Saturday Oklahoma City 2013. I actually ended up giving the session on Friday evening and on Saturday. The Power Pivot pre-con ended an hour early on Friday, so I did a full run-through of the session to a few people from the pre-con who weren't going to be able to attend my Saturday session for various reasons. I then gave the official presentation on Saturday.

One of my favorite demos involved analyzing visitor count data of the various D.C. Smithsonian museums. I had audience members announce their favorite Smithsonian museums, and then we analyzed 2010 - 2012 visitor data. On Friday, everyone said Air and Space Museum. On Saturday I heard, Hirshorn Museum then Museum of Natural History and then finally Air and Space. We then went on to create a Power Map tour that showed another museum overtaking Air and Space in 2012.

My slides are available here.

A video of how to do the U.S. Tourist Attraction demo is available here.

A video of the Power Map tour from the Smithsonian demo is available here.

Monday, August 19, 2013

Speaking on Power Map at SQL Saturday Oklahoma City

I'm excited to see #SQLFamily and learn a ton while at SQL Saturday Oklahoma City this Saturday, August 24th. I'm also quite looking forward to a full day of Power Pivot immersion on Friday from Bill Pearson's Practical Self-Service BI with PowerPivot for Excel pre-con. You can register for the pre-con here. You can register for SQL Saturday Oklahoma City here.

I'll also be giving an updated version of my 3D Geospatial Visualization Using Power Map presentation. This new version includes a quick overview of datums, plus a high level overview of geospatial reporting in SSRS and Power View. Then we'll deep dive into Power Map. I hope to see you guys there!