Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Save more on your purchases! discount-offer-chevron-icon
Savings automatically calculated. No voucher code required.
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Events
Videos
Audiobooks
Packt Hub
Free Learning
Arrow right icon
timer SALE ENDS IN
0 Days
:
00 Hours
:
00 Minutes
:
00 Seconds

How-To Tutorials - Data

1229 Articles
article-image-securing-data-rest-oracle-11g
Packt
23 Oct 2012
11 min read
Save for later

Securing Data at Rest in Oracle 11g

Packt
23 Oct 2012
11 min read
Introduction The Oracle physical database files are primarily protected by filesystem privileges. An attacker who has read permissions on these files will be able to steal the entire database or critical information such as datafiles containing credit card numbers, social security numbers, or other types of private information. Other threats are related to data theft from storage mediums where the physical database resides. The same applies for unprotected backups or dumps that can be easily restored or imported. The data in the database is stored in proprietary format that is quite easy to decipher. There are several sites and specialized tools available to extract data from datafiles, backups, and dumps, known generically as Data Unloading ( DUL). These tools are usually the last solution when the database is corrupted and there is no backup available for restore and recovery. As you probably have already guessed, they can be used by an attacker for data extraction from stolen databases or dumps (summary descriptions and links to several DUL tools can be found at http://www.oracle-internals.com/?p=17 Blvd). The technology behind DUL utilities is based on understanding how Oracle keeps the data in datafiles behind the scenes (a very good article about Oracle datafile internals, written by Rodrigo Righetti, can be found at http://docs.google.com/Doc?id=df2mxgvb_1dgb9fv). Once you decipher the mechanism you will be able to build your tool with little effort. One of the best methods for protecting data at rest is encryption. We can enumerate the following as data encryption methods, described in this chapter for using with Oracle database: Operating system proprietary filesystem or block-based encryption Cryptographic API, especially DBMS_CRYPTO used for column encryption Transparent Data Encryption for encrypting columns, tablespaces, dumps, and RMAN backups Using block device encryption By using block device encryption the data is encrypted and decrypted at block-device level. The block device can be formatted with a filesystem. The decryption is performed once the filesystem is mounted by the operating system, transparently for users. This type of encryption protects best against media theft and can be used for datafile placement. In this recipe we will add a new disk and implement block-level encryption with Linux Unified Key Setup-on-disk-format (LUKS). Getting ready All steps will be performed with nodeorcl1 as root. How to do it... Shut down nodeorcl1, then add a new disk to the nodeorcl1 system and boot it. Our new device will be seen by the operating system as /dev/sdb . Next, create a new partition /dev/sdb1 using fdisk as follows: [root@nodeorcl1 ~]# fdisk /dev/sdb WARNING: DOS-compatible mode is deprecated. It's strongly recommended to switch off the mode (command 'c') and change display units to sectors (command 'u'). Command (m for help): n Command action e extended p primary partition (1-4) p Partition number (1-4): 1 First cylinder (1-5577, default 1): Using default value 1 Last cylinder, +cylinders or +size{K,M,G} (1-5577, default 5577): Using default value 5577 Command (m for help): w The partition table has been altered! Calling ioctl() to re-read partition table. Syncing disks. Format and add a passphrase for encryption on /dev/sdb1 device with cryptsetup utility as follows: [root@nodeorcl1 dev]# cryptsetup luksFormat /dev/sdb1 WARNING! ======== This will overwrite data on /dev/sdb1 irrevocably. Are you sure? (Type uppercase yes): YES Enter LUKS passphrase: P5;@o[]klopY&P] Verify passphrase: P5;@o[]klopY&P] [root@nodeorcl1 dev]# The access on the encrypted device is not performed directly; all operations are performed through a device-mapper. Open the device-mapper for /dev/sdb1 as follows: [root@nodeorcl1 mapper]# cryptsetup luksOpen /dev/sdb1 storage Enter passphrase for /dev/sdb1: P5;@o[]klopY&P] [root@nodeorcl1 mapper]# [root@nodeorcl1 mapper]# ls -al /dev/mapper/storage lrwxrwxrwx. 1 root root 7 Sep 23 20:03 /dev/mapper/storage -> ../ dm-4 The formatting with a filesystem must also be performed on the device-mapper. Format the device-mapper with the ext4 filesystem as follows: [root@nodeorcl1 mapper]# mkfs.ext4 /dev/mapper/storage mke2fs 1.41.12 (17-May-2010) Filesystem label= OS type: Linux Block size=4096 (log=2) Fragment size=4096 (log=2) ………………………………………………………………………………………………………… This filesystem will be automatically checked every 38 mounts or 180 days, whichever comes first. Use tune2fs -c or -i to override. [root@nodeorcl1 mapper]# Next we will configure the device-mapper /dev/mapper/storage for automatic mount during boot. Create a directory called storage that will be used as the mount point: [root@nodeorcl1 storage]# mkdir /storage The mapper-device /dev/mapper/storage can be mounted as a normal device: [root@nodeorcl1 storage]# mount /dev/mapper/storage /storage To make the mount persistent across reboots add /storage as the mount point for /dev/mapper/storage. First add the mapper-device name into /etc/crypttab: [root@nodeorcl1 storage]# echo "storage /dev/sdb1" > /etc/crypttab Add the complete mapper-device path, mount point, and filesystem type in /etc/fstab as follows: /dev/mapper/storage /storage ext4 defaults 1 2 Reboot the system: [root@nodeorcl1 storage]# shutdown –r now At boot sequence, the passphrase for /storage will be requested. If no passphrase is typed then the mapper device will be not mounted. How it works... Block device encryption is implemented to work below the filesystem level. Once the device is offline, the data appears like a large blob of random data. There is no way to determine what kind of filesystem and data it contains. There's more... To dump information about the encrypted device you should execute the following command: [root@nodeorcl1 dev]# cryptsetup luksDump /dev/sdb1 LUKS header information for /dev/sdb1 Version: 1 Cipher name: aes Cipher mode: cbc-essiv:sha256 Hash spec: sha1 Payload offset: 4096 MK bits: 256 MK digest: 2c 7a 4c 96 9d db 63 1c f0 15 0b 2c f0 1a d9 9b 8c 0c 92 4b MK salt: 59 ce 2d 5b ad 8f 22 ea 51 64 c5 06 7b 94 ca 38 65 94 ce 79 ac 2e d5 56 42 13 88 ba 3e 92 44 fc MK iterations: 51750 UUID: 21d5a994-3ac3-4edc-bcdc-e8bfbf5f66f1 Key Slot 0: ENABLED Iterations: 207151 Salt: 89 97 13 91 1c f4 c8 74 e9 ff 39 bc d3 28 5e 90 bf 6b 9a c0 6d b3 a0 21 13 2b 33 43 a7 0c f1 85 Key material offset: 8 AF stripes: 4000 Key Slot 1: DISABLED Key Slot 2: DISABLED Key Slot 3: DISABLED Key Slot 4: DISABLED Key Slot 5: DISABLED Key Slot 6: DISABLED Key Slot 7: DISABLED [root@nodeorcl1 ~]# Using filesystem encryption with eCryptfs The eCryptfs filesytem is implemented as an encryption/decryption layer interposed between a mounted filesystem and the kernel. The data is encrypted and decrypted automatically at filesystem access. It can be used for backup or sensitive files placement for transportable or fixed storage mediums. In this recipe we will install and demonstrate some of eCryptfs, capabilities. Getting ready All steps will be performed on nodeorcl1. How to do it... eCryptfs is shipped and bundled with the Red Hat installation kit. The eCryptfs package is dependent on the trouser package. As root user, first install the trouser package followed by installation of the ecryptfs-util package: [root@nodeorcl1 Packages]# rpm -Uhv trousers-0.3.4-4.el6.x86_64. rpm warning: trousers-0.3.4-4.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID fd431d51: NOKEY Preparing... ###################################### ##### [100%] 1:trousers ###################################### ##### [100%] [root@nodeorcl1 Packages]# rpm -Uhv ecryptfs-utils-82-6.el6. x86_64.rpm warning: ecryptfs-utils-82-6.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID fd431d51: NOKEY Preparing... ###################################### ##### [100%] 1:ecryptfs-utils ###################################### ##### [100%] Create a directory that will be mounted with the eCryptfs filesystem and set the oracle user as the owner: [root@nodeorcl1 ~]# mkdir /ecryptedfiles [root@nodeorcl1 ~]# chown -R oracle:oinstall /ecryptedfiles Mount /ecryptedfiles to itself using the eCryptfs filesystem. Use the default values for all options and use a strong passphrase as follows: [root@nodeorcl1 hashkeys]# mount -t ecryptfs /ecryptedfiles / ecryptedfiles Select key type to use for newly created files: 1) openssl 2) tspi 3) passphrase Selection: 3 Passphrase: lR%5_+KO}Pi_$2E Select cipher: 1) aes: blocksize = 16; min keysize = 16; max keysize = 32 (not loaded) 2) blowfish: blocksize = 16; min keysize = 16; max keysize = 56 (not loaded) 3) des3_ede: blocksize = 8; min keysize = 24; max keysize = 24 (not loaded) 4) cast6: blocksize = 16; min keysize = 16; max keysize = 32 (not loaded) 5) cast5: blocksize = 8; min keysize = 5; max keysize = 16 (not loaded) Selection [aes]: Select key bytes: 1) 16 2) 32 3) 24 Selection [16]: Enable plaintext passthrough (y/n) [n]: Enable filename encryption (y/n) [n]: y Filename Encryption Key (FNEK) Signature [d395309aaad4de06]: Attempting to mount with the following options: ecryptfs_unlink_sigs ecryptfs_fnek_sig=d395309aaad4de06 ecryptfs_key_bytes=16 ecryptfs_cipher=aes ecryptfs_sig=d395309aaad4de06 Mounted eCryptfs [root@nodeorcl1 hashkeys]# Switch to the oracle user and export the HR schema to /ecryptedfiles directory as follows: [oracle@nodeorcl1 ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 [oracle@nodeorcl1 ~]$ exp system file=/ecryptedfiles/hr.dmp owner=HR statistics=none Export: Release 11.2.0.3.0 - Production on Sun Sep 23 20:49:30 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set About to export specified users ... …………………………………………………………………………………………………………….. . . exporting table LOCATIONS 23 rows exported . . exporting table REGIONS 4 rows exported . …………………………………………………………………………………………………….. . exporting post-schema procedural objects and actions . exporting statistics Export terminated successfully without warnings. [oracle@nodeorcl1 ~]$ If you open the hr.dmp file with the strings command, you will be able to see the content of the dump file: [root@nodeorcl1 ecryptedfiles]# strings hr.dmp | more ……………………………………………………………………………………………………………………………………….. CREATE TABLE "COUNTRIES" ("COUNTRY_ID" CHAR(2) CONSTRAINT "COUNTRY_ID_NN" NOT NULL ENABLE, "COUNTRY_NAME" VARCHAR2(40), "REGION_ID" NUMBER, CONSTRAINT "COUNTRY_C_ID_PK" PRIMARY KEY ("COUNTRY_ID") ENABLE ) ORGANIZATION INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "EXAMPLE" NOLOGGING NOCOMPRESS PCTTHRESHOLD 50 INSERT INTO "COUNTRIES" ("COUNTRY_ID", "COUNTRY_NAME", "REGION_ ID") VALUES (:1, :2, :3) Argentina Australia Belgium Brazil Canada Next as root unmount /ecryptedfiles as follows: [root@nodeorcl1 /]# unmount /ecryptedfiles/ If we list the content of the /ecryptedfile directory now, we should see that the file name and content is encrypted: [root@nodeorcl1 /]# cd /ecryptedfiles/ [root@nodeorcl1 ecryptedfiles]# ls ECRYPTFS_FNEK_ENCRYPTED.FWbHZH0OehHS.URqPdiytgZHLV5txs- bH4KKM4Sx2qGR2by6i00KoaCBwE-- [root@nodeorcl1 ecryptedfiles]# [root@nodeorcl1 ecryptedfiles]# more ECRYPTFS_FNEK_ENCRYPTED. FWbHZH0OehHS.URqPdiytgZHLV5txs-bH4KKM4Sx2qGR2by6i00KoaCBwE-- ………………………………………………………………………………………………………………………………… 9$Eî□□KdgQNK□□v□□ S□□J□□□ h□□□ PIi'ʼn□□R□□□□□siP □b □`)3 □W □W( □□□□c!□□8□E.1'□R□7bmhIN□□--(15%) …………………………………………………………………………………………………………………………………. To make the file accessible again, mount the /ecryptedfiles filesystem by passing the same parameters and passphrase as performed in step 3. How it works... eCryptfs is mapped in the kernel Virtual File System ( VFS ), similarly with other filesystems such as ext3, ext4, and ReiserFS. All calls on a filesystem will go first through the eCryptfs mount point and then to the current filesystem found on the mount point (ext4, ext4, jfs, ReiserFS). The key used for encryption is retrieved from the user session key ring, and the kernel cryptographic API is used for encryption and decryption of file content. The communication with kernel is performed by the eCryptfs daemon. The file data content is encrypted for each file with a distinct randomly generated File Encryption Key ( FEK ); FEK is encrypted with File Encryption Key Encryption Key ( FEKEK ) resulting in an Encrypted File Encryption Key ( EFEK) that is stored in the header of file. There's more... On Oracle Solaris you can implement filesystem encryption using the ZFS built-in filesystem encryption capabilities. On IBM AIX you can use EFS.
Read more
  • 0
  • 0
  • 3919

article-image-piwik-tracking-user-interactions
Packt
11 Oct 2012
15 min read
Save for later

Piwik: Tracking User Interactions

Packt
11 Oct 2012
15 min read
Tracking events with Piwik Many of you may be familiar with event tracking with Google Analytics; and many of you may not. In Google Analytics, event tracking is pretty structured. When you track an event with Google, you get five parameters: Category: The name for the group of objects you want to track Action: A string that is used to define the user in action for the category of object Label: This is optional and is for additional data Value: This is optional and is used for providing addition numerical data Non-interaction: This is optional and will not add the event hit into bounce rate calculation if set to true We are going over a few details on event tracking with Google Analytics because the custom variable feature we will be using for event tracking in Piwik is a little less structured. And a little structure will help you drill down the details of your data more effectively from the start. You won't have to restructure and change your naming conventions later on and lose all of your historical data in the process. We don't need to look over the code for Google Analytics. Just know that it may help to set up your event tracking with a similar structure. If you had videos on your site, enough to track, you would most likely make a category of events Videos. You can create as many as you need for the various objects you want to track on your site: Videos Maps Games Ads Blog posts (social media actions) Products As for the actions that can be performed on those Videos, I can think of a few: Play Pause Stop Tweet Like Download There are probably more than you can think of, but now that we have these actions we can connect them with elements of the site. As for the label parameter, you would probably want to use that to store the title of the movie the visitor is interacting with or the page it is located on. We will skip the value parameter which is for numeric data because with Piwik, you won't have a similar value. But non-interaction is interesting; it means that by default an action on a page counts to lower the bounce rate from that page since the user is doing something. Unfortunately, this is not a feature that we have using Piwik currently, although that could change in the future. Okay, now that we have learned one of the ways to structure our events, let's look at the way we can track events in Piwik. There is really nothing called event tracking in Piwik, but Piwik does have custom variables which will do the same job. But, since it is not really event tracking in the truest sense of the word, the bounce rate will be unaffected by any of the custom variables collected. In other words, unlike Google Analytics, you don't get the non-interaction parameter you can set. But let's see what you can do with Piwik. Custom variables are name-value pairs that you can set in Piwik. You can assign up to five custom variables for each visitor and/or each page view. The function for setting a custom variable is setCustomVariable . You must call it for each custom variable you set up to the limit of five. piwikTracker.setCustomVariable (index, name, value, scope); And here are what you set the parameters to: index: This is a number from 1 to 5 where your custom variables are stored. It should stay the same as the name of custom variable. Changing the index of a name later will reset all old data. name: This is the custom variable name or key. value: This is the value for name. scope: This parameter sets the scope of the custom variable, whether it is being tracked per visit or per page. And what scope we set depends upon what we are tracking and how complex our site is. So how do these custom variables fit our model of event tracking? Well we have to do things a little bit differently. For most of our event tracking, we will have to set our variable scope per page. There is not enough room to store much data at the visit level. That is good for other custom tracking you may need but for event tracking you will need more space for data. So with page level custom variables, you get five name-value sets per page. So, we would set up our variables similar to something like this for a video on the page: Index = 1 Name = "Video" Value = "Play","Pause","Stop","Tweet","Like", and so on Scope = "page" And this set of variables in using Piwik's custom variable function would look like one of the following: piwikTracker.setCustomVariable(1,"Video","Play","page"); piwikTracker.setCustomVariable(1,"Video","Pause","page"); piwikTracker.setCustomVariable(1,"Video","Tweet","page"); Which one you would use would depend on what action you are tracking. You would use JavaScript in the page to trigger these variables to be set, most likely by using an onClick event on the button. We will go into the details of various event tracking scenarios later in this chapter. You will notice in the previous snippets of code that the index value of each call is 1. We have set the index of the "Video" name to 1 and must stick to this now on the page or data could be overwritten. This also leaves us the two to five indexes still available for use on the same page. That means if we have banner ads on the page, we could use one of the spare indexes to track the ads. piwikTracker.setCustomVariable(2,"SidebarBanner","Click","page"); You will notice that Google event tracking has the label variable. As we are using page leveling custom variables with Piwik and the variables will be attached to the page itself, there is no need to have this extra variable in most cases. If we do need to add extra data other than an action value, we will have to concatenate our data to the action and use the combined value in Piwik's custom tracking's value variable. Most likely, if we have one banner on our video page, we will have more and to track those click events per banner, we may have to get a little creative using the following: piwikTracker.setCustomVariable(2,"SidebarBanner", "AddSlot1Click","page"); piwikTracker.setCustomVariable(2,"SidebarBanner", "AddSlot2Click","page"); piwikTracker.setCustomVariable(2,"SidebarBanner", "AddSlot3Click","page"); Of course, it is up to you whether you join your data together by using CamelCase, which means joining each piece of data together after capitalizing each. This is what I did previously. You can also use spaces or underscores as long as it is understandable to you and you stick to it. Since the name and value are in quotation marks, you can use any suitable string. And again, since these are custom variables, if you come up with a better system of setting up your event tracking that works better with your website and business model, then by all means try it. Whatever works best for you and your site is better in the long run. So now that we have a general idea of how we will be tracking events with Piwik, let's look at some specific examples and more in depth at what events are, compared to goals or page views. Tracking social engagement You know that you have a Facebook "Like" button on your page, a Twitter "tweet" button, and possibly lots more buttons that do various things at other sites that you yourself have no control over and can add no tracking code to. But you can track clicks on the button itself. You use event tracking for what you could call micro-conversions. But there is really nothing micro about them. That Facebook "Like" could end up in many more sales or conversions than a standard conversion. They could be the route on the way to one or multiple conversions. There may be a blurry line between engagement goals and micro-conversions. And really, it is up to you what weight you give to visitor actions on your site, but use events for something smaller than you would consider a goal. If your goal is sales on your website, that Facebook "Like" should cause a spike in your sales and you will be able to correlate that to your event tracking, but the "Like" is not the end of the road, or the goal. It is a stop on the way. If your website is a blog and your goal is to promote advertising or your services with your content, then tracking social engagement can tell you which topics have the highest social interest so that you can create similar content in the future. So what are some other events we can track? Of course, you would want to track anything having to do with liking, tweeting, bookmarking, or somehow spreading your site on a social network. That includes Facebook , Twitter , Digg , StumbleUpon , Pinterest , and any other social network whose button you put on your site. If you spent enough time to put the buttons on your pages, you can at least track these events. And if you don't have buttons, you have to remember that each generation is using the Internet more often; smartphones make it available everywhere, and everyone is on a social network. Get with it. And don't forget to add event tracking to any sort of Follow Me or Subscribe button. That too is an event worth tracking. We will also look at blog comments since we can consider them to be in the social realm of our tracking. Tracking content sharing So let's look at a set of social sharing buttons on our website. We aren't going to blow things out of proportion by using buttons for every social network out there, just two: Twitter and Facebook. Your site may have less and should have more, but the same methods we will explore next can be used for any amount of content sharing buttons. We are event tracking, so let's begin by defining what our custom variable data will be. We need to figure out how we are going to set up our categories of events and the actions. In this example, we will be using buttons on our Holy Hand Grenade site: You will see our Twitter button and our Facebook button right underneath the image of our Holy Hand Grenade. We are going to act as if our site has many more pages and events to track on it and use a naming convention that will leave room for growth. So we are going to use the category of product shares. That way we have room for video shares when we finally get that cinematographer and film our Holy Hand Grenade in action. Now we need to define our actions. We will be adding more buttons later after we test the effectiveness of our Facebook and Twitter buttons. This means we need a separate action to distinguish each social channel. Share on Facebook Share on Twitter And then we add more buttons: Share on Google+ Share on Digg Share on Reddit Share on StumbleUpon So let's look at the buttons in the source of the page for a minute to see what we are working with: <li class="span1"> <script type="text/javascript" src="http://platform.twitter.com/widgets.js"></script> <a href="https://twitter.com/intent/tweet?url=http%3A%2F%2Fdir23.com&text=Holy%20Hand%20Grenades"class="twitter-share-button">Tweet</a> </li> <li class="span1"></li> <li class="span1"> <script src="http://connect.facebook.net/en_US/all.js#xfbml=1"></script> <fb:like href="http://dir23.com/" show_faces="false"width="50" font=""></fb:like> </li> </ul> <p><a class="btn btn-primary btn-large">Buy Now >></a></p> You see that the buttons are not really buttons yet, they are only HTML anchors in the code and JavaScript includes. Before we start looking at the code to track clicks on these buttons, we need to go over some details about the way Piwik's JavaScript works. Setting a custom variable in Piwik using an onclick event is a very tricky procedure. To start with, you must call more than just setCustomVariable because that will not work after the Piwik tracking JavaScript has loaded and trackPageView has been called. But there is a way around this. First, you call setCustomVariable and then, in that same onclick event , you call trackLink , as in the next example: <p><a href="buynow.html" class="btn btn-primary btn-large" onclick="javascript:piwikTracker.setCustomVariable(2,'Product Pricing','ViewProduct Price','page');piwikTracker.trackLink();">Buy Now >></a></p> If you forget to add the piwikTracker.trackLink() call, nothing will happen and no custom variables will be set. Now with the sharing buttons, we have another issue when it comes to tracking clicks. Most of these buttons, including Facebook, Twitter, and Google+ use JavaScript to create an iframe that has the button. This is a problem, because the iframe is on another domain and there is not an easy way to track clicks. For this reason, I suggest using your social network's API functionality to create the button so that you can create a callback that will fire when someone likes or tweets your page. Another advantage to this method is that you will be sure that each tracked tweet or like will be logged accurately. Using an on_click event will cause a custom variable to be created with every click. If the person is not logged in to their social account at the time, the actual tweet or like will not happen until after they log in, even if they decide to do so. Facebook, Twitter, and Google+ all have APIs with this functionality. But if you decide to try to track the click on the iframe, you can take a look at the code at http://www.bennadel.com/blog/1752-Tracking-Google-AdSense- Clicks-With-jQuery-And-ColdFusion.htm to see how complicated it can get. The click is not really tracked. The blur on the page is tracked, because blur usually happens if a link in the iframe is clicked and a new page is about to load. We already have our standard Piwik tracking code on the page. This does not have to be modified in any way for event tracking. Instead we will be latching into Twitters and Facebook's APIs which we loaded in the page by including their JavaScript. <script> twttr.events.bind('tweet', function(event) { piwikTracker.setCustomVariable(1,'Product Shares','Share on Twitter','page'); piwikTracker.trackLink(); }); </script> <script type="text/javascript"> FB.Event.subscribe('edge.create', function(response) { piwikTracker.setCustomVariable(1,'Product Shares','Share onFacebook','page'); piwikTracker.trackLink(); }); </script> We add these two simple scripts to the bottom of the page. I put them right before the Piwik tracking code. The first script binds to the tweet event in Twitter's API and once that event fires, our Piwik code executes and sets our custom variable. Notice that here too we have to call trackLink right afterwards. The second script does the same thing when someone likes the page on Facebook. It is beyond the scope of this book to go into more details about social APIs, but this code will get you started and you can do more research on your chosen social network's API on your own to see what type of event tracking will be possible. For example, with the Twitter API you can bind a function to each one of these actions: click, tweet, retweet, favorite, or follow. There are definitely more possibilities with this than there is with a simple onclick event. Using event tracking on your social sharing buttons will let you know where people share your line of Holy Hand Grenades. This will help you figure out just which social networks you should have a presence on. If people on Twitter like the grenades, then you should make sure to keep your Twitter account active, and if you don't have a Twitter account and your product is going viral there, you need to get one quick and participate in the conversation about your product. Or you may want to invest in the development of a Facebook app and you are not quite sure that it is worth the investment. Well, a little bit of event tracking will tell you if you have enough people interested in your website or products to fork over the money for an app. Or maybe a person goes down deep into the pages of your site, digs out a gem, and it gets passed around StumbleUpon like crazy. This might indicate a page that you should feature on the home page of your website. And if it's a product page that's been hidden from light for years, maybe throw some advertising its way too.
Read more
  • 0
  • 0
  • 3271

article-image-overview-fim-2010-r2
Packt
03 Sep 2012
18 min read
Save for later

Overview of FIM 2010 R2

Packt
03 Sep 2012
18 min read
The following picture shows a high-level overview of the FIM family and the components relevant to an FIM 2010 R2 implementation:     Within the FIM family, there are some parts that can live by themselves and others that depend on other parts. But, in order to fully utilize the power of FIM 2010 R2, you should have all parts in place. At the center, we have FIM Service and FIM Synchronization Service (FIM Sync). The key to a successful implementation of FIM 2010 R2 is to understand how these two components work—by themselves as well as together.   The history of FIM 2010 R2 Let us go through a short summary of the versions preceding FIM 2010 R2. In 1999, Microsoft bought a company called Zoomit. They had a product called VIA —a directory synchronization product. Microsoft incorporated Zoomit VIA into Microsoft Metadirectory Services (MMS). MMS was only available as a Microsoft Consulting Services solution. In 2003, Microsoft released Microsoft Identity Integration Server (MIIS), and this was the first publicly available version of the synchronization engine today known as FIM 2010 R2 Synchronization Service. In 2005, Microsoft bought a company called Alacris. They had a product called IdNexus, which was used to manage certificates and smart cards. Microsoft renamed it Certificate Lifecycle Manager (CLM). In 2007, Microsoft took MIIS (now with Service Pack 2) and CLM and slammed them together into a new product called Identity Lifecycle Manager 2007 (ILM 2007). Despite the name, ILM 2007 was basically a directory synchronization tool with a certificate management side-kicker. Finally, in 2010, Microsoft released Forefront Identity Manager 2010 (FIM 2010). FIM 2010 was a whole new thing, but as we will see, the old parts from MIIS and CLM are still there. The most fundamental change in FIM 2010 was the addition of the FIM Service component. The most important news was that FIM Service added workflow capability to the synchronization engine. Many identity management operations that used to require a lot of coding were suddenly available without a single line of code. In FIM 2010 R2, Microsoft added the FIM Reporting component and also made significant improvements to the other components.   FIM Synchronization Service (FIM Sync) FIM Synchronization Service is the oldest member of the FIM family. Anyone who has worked with MIIS back in 2003 will feel quite at home with it. Visually, the management tools look the same. FIM Synchronization Service can actually work by itself, without any other component of FIM 2010 R2 being present. We will then basically get the same functionality as MIIS had, back in 2003. FIM Synchronization Service is the heart of FIM, which pumps the data around, causing information about identities to flow from one system to another. Let's look at the pieces that make up the FIM Synchronization Service:     As we can see, there are lots of acronyms and concepts that need a little explaining. On the right-hand side of FIM Synchronization Service, we have Metaverse (MV). Metaverse is used to collect all the information about all the identities managed by FIM. On the other side, we have Connected Data Source (CDS). Connected Data Source is the database, directory, and file, among others, that the synchronization service imports information regarding the managed identities from, and/or exports this information to. To talk to different kinds of Connected Data Sources, FIM Synchronization Service uses adapters that are called Management Agents (MA). In FIM 2010 R2, we will start to use the term Connectors, instead. But, as the user interface in FIM Synchronization Manager still uses the term Management Agent The Management Agent stores a representation of the objects in the CDS, in its Connector Space (CS). When stored in the Connector Space, we refer to the objects as holograms. If we were to look into this a little deeper, we would find that the holograms (objects) are actually stored in multiple instances so that the Management Agent can keep a track of the changes to the objects in the Connector Space. In order to synchronize information from/to different Connected Data Sources, we connect the objects in the Connector Space with the corresponding object in the Metaverse. By collecting information from all Connected Data Sources, the synchronization engine aggregates the information about the object from all the Connected Data Sources into the Metaverse object. This way, the Metaverse will only contain one representation of the object (for example, a user). To describe the data flow within the synchronization service, let's look at the previous diagram and follow a typical scenario. The scenario is this—we want information in our Human Resource (HR) system to govern how users appear in Active Directory (AD) and in our e-mail system. Import users from HR: The bottom CDS could be our HR system. We configure a Management Agent to import users from HR to the corresponding CS. Projection to Metaverse: As there is no corresponding user in the MV that we can connect to, we tell the MA to create a new object in the MV. The process of creating new objects in the MV is called Projection. To transfer information from the HR CS to the MV, we configure Inbound Synchronization Rules. Import and join users from AD: The middle CDS could be Active Directory (AD). We configure a Management Agent to import users from AD. Because there are objects in the MV, we can now tell the Management Agent to try to match the user objects from AD to the objects in the MV. Connecting existing objects in a Connector Space, to an existing object in the Metaverse, is called Joining. In order for the synchronization service to know which objects to connect, some kind of unique information must be present, to get a one-to-one mapping between the object in the CS and the object in the Metaverse. Synchronize information from HR to AD: Once the Metaverse object has a connector to both the HR CS and the AD CS, we can move information from the HR CS to the AD CS. We can, for example, use the employee status information in the HR system to modify the userAccountControl attribute of the AD account. In order to modify the AD CS object, we configure an Outbound Synchronization rule that will tell the synchronization service how to update the CS object based on the information in the MV object. Synchronizing, however, does not modify the user object in AD; it only modifies the hologram representation of the user in the AD Connector Space. Export information to AD: In order to actually change any information in a Connected Data Source, we need to tell the MA to export the changes. During export, the MA updates the objects in the CDS with the changes it has made to the hologram in the Connector Space. Provision users to the e-mail system: The top CDS could be our e-mail system. As users are not present in this system, we would like the synchronization service to create new objects in the CS for the e-mail system. The process of creating new objects in a Connector Space is called Provisioning. Projection, Joining, and Provisioning all create a connector between the Metaverse object and the Connector Space object, making it possible to synchronize identity information between different Connected Data Sources. A key concept to understand here, is that we do not configure synchronization between Connected Data Sources or between Connector Spaces. We synchronize between each Connector Space and Metaverse. Looking at the previous example, we can see that when information flows from HR to AD, we configure the following: HR MA to Import data to the HR CS Inbound synchronization from the HR CS to the MV Outbound synchronization from the MV to the AD CS AD MA to Export the data to AD   Management Agents Management Agents, or Connectors as some people call them, are the entities that enable FIM to talk to different kinds of data sources. Basically, we can say that FIM can talk to any type of data source, but it only has built-in Management Agents for some. If the data source is really old, we might even have to use the extensibility platform and write our own Management Agent or buy a Management Agent from a third-party supplier. At http://aka.ms/FIMPartnerMA, we can find a list of Management Agents supplied by Microsoft Partners. For a complete list of Management Agents built in and available from Microsoft, please look at http://aka.ms/FIMMA. With R2, a new Management Agent for Extensible Connectivity 2.0 (ECMA 2.0) is released, introducing new ways of making custom Management Agents. We will see updated versions of most third party Management Agents as soon as they are migrated to the new ECMA 2.0 platform. Microsoft will also ship new Management Agents using the new ECMA 2.0 platform. Writing our own MA is one way of solving problems communicating with odd data sources. But there might be other solutions to the problem that will require less coding.   Non-declarative vs. declarative synchronization If you are using FIM Synchronization Service the old way, like we did in MIIS or ILM 2007, it is called non-declarative synchronization. We usually call that classic synchronization and will also use that term in this article. If we use the FIM Service logic to control it all, it is called declarative synchronization. As classic synchronization usually involves writing code, and declarative does not; we will also find references calling declarative synchronization codeless. In fact, it was quite possible, in some scenarios, to have codeless synchronization— even in the old MIIS or ILM 2007—using classic synchronization. The fact also remains that there are very few FIM 2010 R2 implementations that are indeed code free. In some cases you might even mix the two. This could be due either to migration from MIIS/ILM 2007 to FIM 2010 R2 or to the decision that it is cheaper/ quicker/easier to solve a particular problem using classic synchronization.   Password synchronization This should be the last resort to achieve some kind of Single Sign On (SSO). Instead of implementing password synchronization, we try to make our customers look at other ways, such as Kerberos or Federation, to get SSO. There are, however, many cases where password synchronization is the best option to maintain passwords in different systems. Not all environments can utilize Kerberos or Federation and therefore need the FIM password synchronization feature to maintain passwords in different Connected Data Sources. The use of this feature is to have Active Directory by either installing and configuring Password Change Notification Service (PCNS) on Domain Controllers or using FIM Service as a source for the password change. FIM Synchronization Service then updates the password on the connected object in Connected Data Sources, which are configured as password synchronization targets. In order for FIM to set the password in a target system, the Management Agent used to connect to that specific CDS needs to support this. Most Management Agents available today support password management or can be configured to do so.   FIM Service Management Agent A very special Management Agent is the one connecting FIM Synchronization Service to FIM Service. Many of the rules we apply to other types of Management Agents do not apply to this one. If you have experience working with classic synchronization in MIIS or ILM 2007, you will find that this Management Agent does not work as the others.   FIM Service If FIM Synchronization Service is the heart pumping information, FIM Service is the brain (sorry FIM CM, but your brain is not as impressive) FIM Service plays many roles in FIM, and during the design phase the capabilities of FIM Service is often on focus. FIM Service allows you to enforce the Identity Management policy within your organization and also make sure you are compliant at all times. FIM Service has its own database, where it stores the information about the identities it manages.   Request pipeline In order to make any changes to objects in the FIM Service database, we need to work our way through the FIM Service request pipeline. So, let's look at the following diagram and walk through the request pipeline:     Every request is made to the web service interface, and follows the ensuing flow: The Request Processor workflow receives the request and evaluates the token (who?) and the request type (what?). Permission is checked to see if the request is allowed. Management Policy Rules are evaluated. If Authenticate workflow is required, serialize and run interactive workflow. If Authorize workflow is required, parallelize and run asynchronous workflow. Modify the object in FIM Service Database according to the request. If Action workflow is required, run follow-up workflows. As we can see, a request to FIM Service may trigger three types of workflows. With the installation of FIM 2001 R2, we will get a few workflows that will cover many basic requirements, but this is one of the situations where custom coding or thirdparty workflows might be required in order to fulfill the identity management policy within the organization. Authentication workflow (AuthN) is used when the request requires additional authentication. An example of this is when a user tries to reset his password—the AuthN workflow will ask the anonymous user to authenticate using the QA gateway. Authorization workflow (AuthZ) is used when the request requires authorization from someone else. An example of this is when a user is added to a group, but the policy states that the owner of the group needs to approve the request. Action workflow is used for many types of follow-up actions—it could be sending a notification email or modifying attributes, among many other things.   FIM Service Management Agent FIM Service Management Agent , as we discussed earlier, is responsible for synchronizing data between FIM Service and FIM Synchronization Service. We said then that this MA is a bit special, and even from the FIM Service perspective it works a little differently. A couple of examples of the special relationship between the FIM Service MA and FIM Service are as follows: Any request made by the FIM Service MA will bypass any AuthN and AuthZ workflows As a performance enhancer, the FIM Service MA is allowed to make changes directly to the FIM Service DB in FIM 2010 R2, without using the request pipeline described earlier   Management Policy Rules (MPRs) The way we control what can be done, or what should happen, is by defining Management Policy Rules (MPRs) within FIM Service. MPR is our tool to enforce the Identity Management policies within our organization. There are two types of MPRs—Request and Set Transition. A Request MPR is used to define how the request pipeline should behave on a particular request. If a request comes in and there is no Request MPR matching the request, it will fail. A Set Transition MPR is used to detect changes in objects and react upon that change. For example, if my EmployeeStatus is changed to Fired, my Active Directory (AD) account should be disabled. A Set is used within FIM Service to group objects. We define rules that govern the criteria for an object to be part of a Set. For example, we can create a Set, which contains all users with Fired as EmployeeStatus. As objects satisfy this criteria and transition in to the Set, we can define a Set Transition MPR to make things such as disabling the AD account happen. We can also define an MPR that applies to the transition out from a Set. The Sets are also used to configure permissions within FIM Service. Using Sets allows us to configure very granular permissions in scenarios where FIM Service is used for user self service.   FIM Portal FIM Portal is usually the starting point for administrators who will configure FIM Service. The configuration of FIM Service is usually done using FIM Portal, but it may also be configured using Power Shell or even your own custom interface. FIM Portal can also be used for self-service scenarios, allowing users to manage some aspect of the Identity Management process. FIM Portal is actually an ASP.NET application using Microsoft Sharepoint as a foundation, and can be modified in many ways.   Self Service Password Reset (SSPR) The Self Service Password Reset (SSPR) feature of FIM is a special case, where most components used to implement it are built-in. The default method is using what is called a QA Gate. FIM 2010 R2 also has built-in methods for using a One Time Password (OTP) that can be sent using either SMS, or e-mail services. In short, the QA Gate works in the following way: The administrator defines a number of questions. Users register for SSPR and provide answers to the questions. Users are presented with the same questions, when a password reset is needed. Giving the correct answers identifies the user and allows them to reset their password.     Once the FIM administrator has used FIM Portal to configure the password reset feature, the end user can register his answers to QA Gate. If the organization has deployed FIM Password Reset Extension to the end user's Windows client, the process of registration and reset can be made directly from the Windows client. If not, the user can register and reset his password using the password registration and reset portals.   FIM Reporting The Reporting component is brand new in FIM 2010 R2. In earlier versions of FIM, as well as the older MIIS and ILM, reporting was typically achieved by either buying third-party add-ons or developing their own solutions based on SQL Reporting Services. The purpose of Reporting is to give you a chance to view historical data. There are a few reports built in to FIM 2010 R2, but many organizations will develop their own reports that comply with their Identity Management policies. The implementation of FIM 2010 R2 will however be a little more complex, if you want the Reporting component. This is because the engine used to generate the reports is the Data Warehouse component of Microsoft System Center Service Manager (SCSM). There are a number of reasons for using the existing reporting capabilities in SCSM; the main one is that it is easy to extend.   FIM Certificate Management (FIM CM) Certificate Management is the outcast member of the FIM family. FIM CM can be, and often is, used by itself, without any other parts of FIM being present. It is also the component with the poorest integration with the other components. If we look at it, we will find that it hasn't changed much since its predecessor, Certificate Lifecycle Management (CLM), was released. FIM CM is mainly focused on managing smart cards, but it can also be used to manage and trace any type of certificate requests.     The basic concept of FIM CM is that a smart card is requested using the FIM CM portal. Information regarding all requests is stored in the FIM CM database. The Certification authority, which handles the issuing of the certificates, is configured to report the status back to the FIM CM database. FIM CM portal also contains a workflow engine, so that the FIM CM admin can configure features such as e-mail notifications as a part of the policies.   Certificate Management portal FIM Certificate Management uses a portal to interact with users and administrators. The FIM CM portal is an ASP.Net 2.0 website where, for example: Administrators can configure the policies that govern the processes around certificate management End users can manage their smart cards for purposes such as renewing and changing PIN codes Help desks can use the portal to, for example, request temporary smart cards or reset PINs:     Licensing We put this part in here, not to tell you how FIM 2010 R2 is licensed, but rather to tell you that it is complex. Since Microsoft has a habit of changing the way they license their products, we will not put any license details into writing. Depending on what parts you are using and, in some cases, how you are using them, you need to buy different licenses. FIM 2010 R2 (at the time of my writing) uses both Server licenses as well as Client Access Licenses (CALs). In almost every FIM project the licensing cost is negligible compared to the gain retrieved by implementing it. But even so, please make sure to contact your Microsoft licensing partner, or your Microsoft contact, to clear any questions you might have around licensing. If you do not have Microsoft System Center Service Manager (SCSM), it is stated (at the time of my writing) that you can install and use SCSM for FIM Reporting usage without having to buying SCSM licenses. Read more about FIM Licensing at o http://aka.ms/FIMLicense.   Summary As it can be seen, Microsoft Forefront Identity Manager 2010 R2 is not just one product, but a family of products. In this article, we have given you a short overview of the different components, and we saw how together they can mitigate the challenges that The Company has identified about their identity management.
Read more
  • 0
  • 0
  • 3966

article-image-infinispan-data-grid-infinispan-and-jboss-7
Packt
23 Aug 2012
2 min read
Save for later

Infinispan Data Grid: Infinispan and JBoss AS 7

Packt
23 Aug 2012
2 min read
The new modular application server JBoss AS has changed a lot with the latest distribution. The new application server has improved in many areas, including lower memory footprint, lightning fast startup, true classloading isolation (between built-in modules and modules delivered by developers), and excellent management of resources with the addition of domain controllers. How Infinispan platform fits into this new picture will be illustrated shortly. Should you need to know all the core details of the AS 7 architecture, you might consider looking for a copy of JBoss AS 7 Configuration, Deployment and Administration, which has been authored by Francesco Marchioni and was published in December, 2011. In a nutshell, JBoss AS 7 is composed of a set of modules that provide the basic server functionalities. The configuration of modules is not spread in a set of single XML files anymore, but it is centralized into a single file. Thus, every configuration file holds a single server configuration. A server configuration can be in turn based on a set of standalone servers or domain servers. The main difference between standalone servers and domain servers encompasses the management area; as a matter of fact, domain-based servers can be managed from a centralized point (the domain controller) while, on the other hand, standalone servers are independent server units, each one managing its own configuration.
Read more
  • 0
  • 0
  • 2233

article-image-hbase-administration-performance-tuning
Packt
21 Aug 2012
8 min read
Save for later

HBase Administration, Performance Tuning

Packt
21 Aug 2012
8 min read
Setting up Hadoop to spread disk I/O Modern servers usually have multiple disk devices to provide large storage capacities. These disks are usually configured as RAID arrays, as their factory settings. This is good for many cases but not for Hadoop. The Hadoop slave node stores HDFS data blocks and MapReduce temporary files on its local disks. These local disk operations benefit from using multiple independent disks to spread disk I/O. In this recipe, we will describe how to set up Hadoop to use multiple disks to spread its disk I/O. Getting ready We assume you have multiple disks for each DataNode node. These disks are in a JBOD (Just a Bunch Of Disks) or RAID0 configuration. Assume that the disks are mounted at /mnt/d0, /mnt/d1, …, /mnt/dn, and the user who starts HDFS has write permission on each mount point. How to do it... In order to set up Hadoop to spread disk I/O, follow these instructions: On each DataNode node, create directories on each disk for HDFS to store its data blocks: hadoop$ mkdir -p /mnt/d0/dfs/datahadoop$ mkdir -p /mnt/d1/dfs/data…hadoop$ mkdir -p /mnt/dn/dfs/data Add the following code to the HDFS configuration file (hdfs-site.xml): hadoop@master1$ vi $HADOOP_HOME/conf/hdfs-site.xml <property> <name>dfs.data.dir</name> <value>/mnt/d0/dfs/data,/mnt/d1/dfs/data,...,/mnt/dn/dfs/data</value> </property> Sync the modified hdfs-site.xml file across the cluster: hadoop@master1$ for slave in `cat $HADOOP_HOME/conf/slaves`do rsync -avz $HADOOP_HOME/conf/ $slave:$HADOOP_HOME/conf/done Restart HDFS: hadoop@master1$ $HADOOP_HOME/bin/stop-dfs.shhadoop@master1$ $HADOOP_HOME/bin/start-dfs.sh How it works... We recommend JBOD or RAID0 for the DataNode disks, because you don't need the redundancy of RAID, as HDFS ensures its data redundancy using replication between nodes. So, there is no data loss when a single disk fails. Which one to choose, J BOD or RAID0? You will theoretically get better performance from a JBOD configuration than from a RAID configuration. This is because, in a RAID configuration, you have to wait for the slowest disk in the array to complete before the entire write operation can complete, which makes the average I/O time equivalent to the slowest disk's I/O time. In a JBOD configuration, operations on a faster disk will complete independently of the slower ones, which makes the average I/O time faster than the slowest one. However, enterprise-class RAID cards might make big differences. You might want to benchmark your JBOD and RAID0 configurations before deciding which one to go with. For both JBOD and RAID0 configurations, you will have the disks mounted at different paths. The key point here is to set the dfs.data.dirproperty to all the directories created on each disk. The dfs.data.dirproperty specifies where the DataNode should store its local blocks. By setting it to comma-separated multiple directories, DataNode stores its blocks across all the disks in round robin fashion. This causes Hadoop to efficiently spread disk I/O to all the disks. Warning Do not leave blanks between the directory paths in the dfs.data.dir property value, or it won't work as expected. You will need to sync the changes across the cluster and restart HDFS to apply them. There's more... If you run MapReduce, as MapReduce stores its temporary files on TaskTracker's local file system, you might also like to set up MapReduce to spread its disk I/O: On each TaskTracker node, create directories on each disk for MapReduce to store its intermediate data files: hadoop$ mkdir -p /mnt/d0/mapred/localhadoop$ mkdir -p /mnt/d1/mapred/local…hadoop$ mkdir -p /mnt/dn/mapred/local Add the following to MapReduce's configuration file (mapred-site.xml): hadoop@master1$ vi $HADOOP_HOME/conf/mapred-site.xml <property> <name>mapred.local.dir</name> <value>/mnt/d0/mapred/local,/mnt/d1/mapred/local,...,/mnt/dn/mapred/local</value> </property> Sync the modified mapred-site.xml file across the cluster and restart MapReduce. MapReduce generates a lot of temporary files on TaskTrackers' local disks during its execution. Like HDFS, setting up multiple directories on different disks helps spread MapReduce disk I/O significantly. Using network topology script to make Hadoop rack-aware Hadoop has the concept of "Rack Awareness ". Administrators are able to define the rack of each DataNode in the cluster. Making Hadoop rack-aware is extremely important because: Rack awareness prevents data loss Rack awareness improves network performance In this recipe, we will describe how to make Hadoop rack-aware and why it is important. Getting ready You will need to know the rack to which each of your slave nodes belongs. Log in to the master node as the user who started Hadoop. How to do it... The following steps describe how to make Hadoop rack-aware: Create a topology.sh script and store it under the Hadoop configuration directory. Change the path for topology.data, in line 3, to fit your environment: hadoop@master1$ vi $HADOOP_HOME/conf/topology.sh while [ $# -gt 0 ] ; do nodeArg=$1 exec< /usr/local/hadoop/current/conf/topology.data result="" while read line ; do ar=( $line ) if [ "${ar[0]}" = "$nodeArg" ] ; then result="${ar[1]}" fi done shift if [ -z "$result" ] ; then echo -n "/default/rack " else echo -n "$result " fi done Don't forget to set the execute permission on the script file: hadoop@master1$ chmod +x $HADOOP_HOME/conf/topology.sh Create a topology.data file, as shown in the following snippet; change the IP addresses and racks to fit your environment: hadoop@master1$ vi $HADOOP_HOME/conf/topology.data10.161.30.108 /dc1/rack110.166.221.198 /dc1/rack210.160.19.149 /dc1/rack3 Add the following to the Hadoop core configuration file (core-site.xml): hadoop@master1$ vi $HADOOP_HOME/conf/core-site.xml <property> <name>topology.script.file.name</name> <value>/usr/local/hadoop/current/conf/topology.sh</value> </property> Sync the modified files across the cluster and restart HDFS and MapReduce. Make sure HDFS is now rack-aware. If everything works well, you should be able to find something like the following snippet in your NameNode log file: 2012-03-10 13:43:17,284 INFO org.apache.hadoop.net.NetworkTopology: Adding a new node: /dc1/rack3/10.160.19.149:50010 2012-03-10 13:43:17,297 INFO org.apache.hadoop.net.NetworkTopology: Adding a new node: /dc1/rack1/10.161.30.108:50010 2012-03-10 13:43:17,429 INFO org.apache.hadoop.net.NetworkTopology: Adding a new node: /dc1/rack2/10.166.221.198:50010 Make sure MapReduce is now rack-aware. If everything works well, you should be able to find something like the following snippet in your JobTracker log file: 2012-03-10 13:50:38,341 INFO org.apache.hadoop.net.NetworkTopology: Adding a new node: /dc1/rack3/ip-10-160-19-149.us-west-1.compute.internal 2012-03-10 13:50:38,485 INFO org.apache.hadoop.net.NetworkTopology: Adding a new node: /dc1/rack1/ip-10-161-30-108.us-west-1.compute.internal 2012-03-10 13:50:38,569 INFO org.apache.hadoop.net.NetworkTopology: Adding a new node: /dc1/rack2/ip-10-166-221-198.us-west-1.compute.internal How it works... The following diagram shows the concept of Hadoop rack awareness: Each block of the HDFS files will be replicated to multiple DataNodes, to prevent loss of all the data copies due to failure of one machine. However, if all copies of data happen to be replicated on DataNodes in the same rack, and that rack fails, all the data copies will be lost. So to avoid this, the NameNode needs to know the network topology in order to use that information to make intelligent data replication. As shown in the previous diagram, with the default replication factor of three, two data copies will be placed on the machines in the same rack, and another one will be put on a machine in a different rack. This ensures that a single rack failure won't result in the loss of all data copies. Normally, two machines in the same rack have more bandwidth and lower latency between them than two machines in different racks. With the network topology information, Hadoop is able to maximize network performance by reading data from proper DataNodes. If data is available on the local machine, Hadoop will read data from it. If not, Hadoop will try reading data from a machine in the same rack, and if it is available on neither, data will be read from machines in different racks. In step 1, we create a topology.sh script. The script takes DNS names as arguments and returns network topology (rack) names as the output. The mapping of DNS names to network topology is provided by the topology.data file, which was created in step 2. If an entry is not found in the topology.data file, the script returns /default/rack as a default rack name. Note that we use IP addresses, and not hostnames in the topology. data file. There is a known bug that Hadoop does not correctly process hostnames that start with letters "a" to "f". Check HADOOP-6682 for more details. In step 3, we set the topology.script.file.name property in core-site.xml, telling Hadoop to invoke topology.sh to resolve DNS names to network topology names. After restarting Hadoop, as shown in the logs of steps 5 and 6, HDFS and MapReduce add the correct rack name as a prefix to the DNS name of slave nodes. This indicates that the HDFS and MapReduce rack awareness work well with the aforementioned settings.
Read more
  • 0
  • 0
  • 4569

article-image-article-hbase-administration-performance-tuning-hadoop-0
Packt
21 Aug 2012
7 min read
Save for later

HBase Administration, Performance Tuning, Hadoop

Packt
21 Aug 2012
7 min read
Setting up Hadoop to spread disk I/O Modern servers usually have multiple disk devices to provide large storage capacities. These disks are usually configured as RAID arrays, as their factory settings. This is good for many cases but not for Hadoop. The Hadoop slave node stores HDFS data blocks and MapReduce temporary files on its local disks. These local disk operations benefit from using multiple independent disks to spread disk I/O. In this recipe, we will describe how to set up Hadoop to use multiple disks to spread its disk I/O. Getting ready We assume you have multiple disks for each DataNode node. These disks are in a JBOD (Just a Bunch Of Disks) or RAID0 configuration. Assume that the disks are mounted at /mnt/d0, /mnt/d1, …, /mnt/dn, and the user who starts HDFS has write permission on each mount point. How to do it... In order to set up Hadoop to spread disk I/O, follow these instructions: On each DataNode node, create directories on each disk for HDFS to store its data blocks: code 1 Add the following code to the HDFS configuration file (hdfs-site.xml): code 2 Sync the modified hdfs-site.xml file across the cluster: code 3 Restart HDFS: code 4 How it works... We recommend JBOD or RAID0 for the DataNode disks, because you don't need the redundancy of RAID, as HDFS ensures its data redundancy using replication between nodes. So, there is no data loss when a single disk fails. Which one to choose, J BOD or RAID0? You will theoretically get better performance from a JBOD configuration than from a RAID configuration. This is because, in a RAID configuration, you have to wait for the slowest disk in the array to complete before the entire write operation can complete, which makes the average I/O time equivalent to the slowest disk's I/O time. In a JBOD configuration, operations on a faster disk will complete independently of the slower ones, which makes the average I/O time faster than the slowest one. However, enterprise-class RAID cards might make big differences. You might want to benchmark your JBOD and RAID0 configurations before deciding which one to go with. For both JBOD and RAID0 configurations, you will have the disks mounted at different paths. The key point here is to set the dfs.data.dirproperty to all the directories created on each disk. The dfs.data.dirproperty specifies where the DataNode should store its local blocks. By setting it to comma-separated multiple directories, DataNode stores its blocks across all the disks in round robin fashion. This causes Hadoop to efficiently spread disk I/O to all the disks. Warning Do not leave blanks between the directory paths in the dfs.data.dir property value, or it won't work as expected. You will need to sync the changes across the cluster and restart HDFS to apply them. There's more... If you run MapReduce, as MapReduce stores its temporary files on TaskTracker's local file system, you might also like to set up MapReduce to spread its disk I/O: On each TaskTracker node, create directories on each disk for MapReduce to store its intermediate data files: code 5 Add the following to MapReduce's configuration file (mapred-site.xml): code 6 Sync the modified mapred-site.xml file across the cluster and restart MapReduce. MapReduce generates a lot of temporary files on TaskTrackers' local disks during its execution. Like HDFS, setting up multiple directories on different disks helps spread MapReduce disk I/O significantly. Using network topology script to make Hadoop rack-aware Hadoop has the concept of "Rack Awareness ". Administrators are able to define the rack of each DataNode in the cluster. Making Hadoop rack-aware is extremely important because: Rack awareness prevents data loss Rack awareness improves network performance In this recipe, we will describe how to make Hadoop rack-aware and why it is important. Getting ready You will need to know the rack to which each of your slave nodes belongs. Log in to the master node as the user who started Hadoop. How to do it... The following steps describe how to make Hadoop rack-aware: Create a topology.sh script and store it under the Hadoop configuration directory. Change the path for topology.data, in line 3, to fit your environment: code 7 Don't forget to set the execute permission on the script file: code 8 Create a topology.data file, as shown in the following snippet; change the IP addresses and racks to fit your environment: code 9 Add the following to the Hadoop core configuration file (core-site.xml): code 10 Sync the modified files across the cluster and restart HDFS and MapReduce. Make sure HDFS is now rack-aware. If everything works well, you should be able to find something like the following snippet in your NameNode log file: 2012-03-10 13:43:17,284 INFO org.apache.hadoop.net.NetworkTopology: Adding a new node: /dc1/rack3/10.160.19.149:50010 2012-03-10 13:43:17,297 INFO org.apache.hadoop.net.NetworkTopology: Adding a new node: /dc1/rack1/10.161.30.108:50010 2012-03-10 13:43:17,429 INFO org.apache.hadoop.net.NetworkTopology: Adding a new node: /dc1/rack2/10.166.221.198:50010 Make sure MapReduce is now rack-aware. If everything works well, you should be able to find something like the following snippet in your JobTracker log file: 2012-03-10 13:50:38,341 INFO org.apache.hadoop.net.NetworkTopology: Adding a new node: /dc1/rack3/ip-10-160-19-149.us-west-1.compute.internal 2012-03-10 13:50:38,485 INFO org.apache.hadoop.net.NetworkTopology: Adding a new node: /dc1/rack1/ip-10-161-30-108.us-west-1.compute.internal 2012-03-10 13:50:38,569 INFO org.apache.hadoop.net.NetworkTopology: Adding a new node: /dc1/rack2/ip-10-166-221-198.us-west-1.compute.internal How it works... The following diagram shows the concept of Hadoop rack awareness: Each block of the HDFS files will be replicated to multiple DataNodes, to prevent loss of all the data copies due to failure of one machine. However, if all copies of data happen to be replicated on DataNodes in the same rack, and that rack fails, all the data copies will be lost. So to avoid this, the NameNode needs to know the network topology in order to use that information to make intelligent data replication. As shown in the previous diagram, with the default replication factor of three, two data copies will be placed on the machines in the same rack, and another one will be put on a machine in a different rack. This ensures that a single rack failure won't result in the loss of all data copies. Normally, two machines in the same rack have more bandwidth and lower latency between them than two machines in different racks. With the network topology information, Hadoop is able to maximize network performance by reading data from proper DataNodes. If data is available on the local machine, Hadoop will read data from it. If not, Hadoop will try reading data from a machine in the same rack, and if it is available on neither, data will be read from machines in different racks. In step 1, we create a topology.sh script. The script takes DNS names as arguments and returns network topology (rack) names as the output. The mapping of DNS names to network topology is provided by the topology.data file, which was created in step 2. If an entry is not found in the topology.data file, the script returns /default/rack as a default rack name. Note that we use IP addresses, and not hostnames in the topology. data file. There is a known bug that Hadoop does not correctly process hostnames that start with letters "a" to "f". Check HADOOP-6682 for more details. In step 3, we set the topology.script.file.name property in core-site.xml, telling Hadoop to invoke topology.sh to resolve DNS names to network topology names. After restarting Hadoop, as shown in the logs of steps 5 and 6, HDFS and MapReduce add the correct rack name as a prefix to the DNS name of slave nodes. This indicates that the HDFS and MapReduce rack awareness work well with the aforementioned settings.
Read more
  • 0
  • 0
  • 1066
Unlock access to the largest independent learning library in Tech for FREE!
Get unlimited access to 7500+ expert-authored eBooks and video courses covering every tech area you can think of.
Renews at $19.99/month. Cancel anytime
article-image-article-ibm-cognos-10-bi-business-insight-dashboard
Packt
16 Jul 2012
7 min read
Save for later

IBM Cognos 10 BI dashboarding components

Packt
16 Jul 2012
7 min read
Introducing IBM Cognos 10 BI Cognos Connection In this recipe we will be exploring Cognos Connection, which is the user interface presented to the user when he/she logs in to IBM Cognos 10 BI for the first time. IBM Cognos 10 BI, once installed and configured, can be accessed through the Web using supported web browsers. For a list of supported web browsers, refer to the Installation and Configuration Guide shipped with the product. Getting ready As stated earlier, make sure that IBM Cognos 10 BI is installed and configured. Install and configure the GO Sales and GO Data Warehouse samples. Use the gateway URI to log on to the web interface called Cognos Connection. How to do it... To explore Cognos Connection, perform the following steps: Log on to Cognos Connection using the gateway URI that may be similar to http://<HostName>:<PortNumber>/ibmcognos/cgi-bin/cognos.cgi. Take note of the Cognos Connection interface. It has the GO Sales and GO Data Warehouse samples visible. Note the blue-colored folder icon, shown as in the preceding screenshot. It represents metadata model packages that are published to Cognos Connection using the Cognos Framework Manager tool. These packages have objects that represent business data objects, relationships, and calculations, which can be used to author reports and dashboards. Refer to the book, IBM Cognos TM1 Cookbook by Packt Publishing to learn how to create metadata models packages. From the toolbar, click on Launch. This will open a menu, showing different studios, each having different functionality, as shown in the following screenshot: We will use Business Insight and Business Insight Advanced, which are the first two choices in the preceding menu. These are the two components used to create and view dashboards. For other options, refer to the corresponding books by the same publisher. For instance, refer to the book, IBM Cognos 8 Report Studio Cookbook to know more about creating and distributing complex reports. Query Studio and Analysis Studio are meant to provide business users with the facility to slice and dice business data themselves. Event Studio is meant to define business situations and corresponding actions. Coming back to Cognos Connection, note that a yellow-colored folder icon, which is shown as represents a user-defined folder, which may or may not contain other published metadata model packages, reports, dashboards, and other content. In our case, we have a user-defined folder called Samples. This was created when we installed and configured samples shipped with the product. Click on the New Folder icon, which is represented by , on the toolbar to create a user-defined folder. Other options are also visible here, for instance to create a new dashboard.   Click on the user-defined folder—Samples to view its contents, as shown in the following screenshot: As shown in the preceding screenshot, it has more such folders, each having its own content. The top part of the pane shows the navigation path. Let's navigate deeper into Models | Business Insight Samples to show some sample dashboards, created using IBM Cognos Business Insight, as shown in the following screenshot: Click on one of these links to view the corresponding dashboard. For instance, click on Sales Dashboard (Interactive) to view the dashboard, as shown in the following screenshot: The dashboard can also be opened in the authoring tool, which is IBM Cognos Business Insight, in this case by clicking on the icon shown as on extreme right, on Cognos Connection. It will show the same result as shown in the preceding screenshot. We will see the Business Insight interface in detail later in this article. How it works... Cognos Connection is the primary user interface that user sees when he/she logs in for the first time. Business data has to be first identified and imported from the metadata model using the Cognos Framework Manager tool. Relationships (inner/outer joins) and calculations are then created, and the resultant metadata model package is published to the IBM Cognos 10 BI Server. This becomes available on Cognos Connection. Users are given access to appropriate studios on Cognos Connection, according to their needs. Analysis, reports, and dashboards are then created and distributed using one of these studios. The preceding sample has used Business Insight, for instance. Later sections in this article will look more into Business Insight and Business Insight Advanced. The next section focuses on the Business Insight interface details from the navigation perspective. Exploring IBM Cognos Business Insight User Interface In this recipe we will explore IBM Cognos Business Insight User Interface in more detail. We will explore various areas of the UI, each dedicated to perform different actions. Getting ready As stated earlier, we will be exploring different sections of Cognos Business Insight. Hence, make sure that IBM Cognos 10 BI installation is open and samples are set up properly. We will start the recipe assuming that the IBM Cognos Connection window is already open on the screen. How to do it... To explore IBM Cognos Business Insight User Interface, perform the following steps: In the IBM Cognos Connection window, navigate to Business Insight Samples, as shown in the following screenshot: Click on one of the dashboards, for instance Marketing Dashboard to open the dashboard in Business Insight. Different areas are labeled, as shown in the following figure: The overall layout is termed as Dashboard. The topmost toolbar is called Application bar . The Application bar contains different icons to manage the dashboard as a whole. For instance, we can create, open, e-mail, share, or save the dashboard using one of the icons on the Application bar. The user can explore different icons on the Application bar by hovering the mouse pointer over them. Hovering displays the tooltip, which has a brief but self-explanatory help text. Similarly, it has a Widget toolbar for every widget, which gets activated when the user clicks on the corresponding widget. When the mouse is focused away from the widget, the Widget toolbar disappears. It has various options, for instance to refresh the widget data, print as PDF, resize to ? t content, and so on. It also provides the user with the capability to change the chart type as well as to change the color palette. However, all these options have help text associated with them, which is activated on mouse hover. Content tab and Content pane show the list of objects available on the Cognos Connection. Directory structure on Cognos Connection can be navigated using Content pane and Content tab, and hence, available objects can be added to or removed from the dashboard. The drag-and-drop functionality has been provided as a result of which creating and editing a dashboard has become as simple as moving objects between the Dashboard area and Cognos Connection. The Toolbox tab displays additional widgets. The Slider Filter and Select Value Filter widgets allow the user to filter report content. The other toolbox widgets allow user to add more report content to the dashboard, such as HTML content, images, RSS feeds, and rich text. How it works... In the preceding section, we have seen basic areas of Business Insight. More than one user can log on to the IBM Cognos 10 BI server, and create various objects on Cognos Connection. These objects include packages, reports, cubes, templates, and statistics to name a few. These objects can be created using one or more tools available to users. For instance, reports can be created using one of the studios available. Cubes can be created using IBM Cognos TM1 or IBM Cognos Transformer and published on Cognos Connection. Metadata model packages can be created using IBM Cognos Framework Manager and published on Cognos Connection. These objects can then be dragged, dropped, and formatted as standalone objects in Cognos Business Insight, and hence, dashboards can be created.
Read more
  • 0
  • 0
  • 5159

article-image-article-ibm-cognos-10-bi-dashboard-business-insight
Packt
16 Jul 2012
7 min read
Save for later

IBM Cognos 10 Business Intelligencea

Packt
16 Jul 2012
7 min read
Introducing IBM Cognos 10 BI Cognos Connection In this recipe we will be exploring Cognos Connection, which is the user interface presented to the user when he/she logs in to IBM Cognos 10 BI for the first time. IBM Cognos 10 BI, once installed and configured, can be accessed through the Web using supported web browsers. For a list of supported web browsers, refer to the Installation and Configuration Guide shipped with the product. Getting ready As stated earlier, make sure that IBM Cognos 10 BI is installed and configured. Install and configure the GO Sales and GO Data Warehouse samples. Use the gateway URI to log on to the web interface called Cognos Connection. How to do it... To explore Cognos Connection, perform the following steps: Log on to Cognos Connection using the gateway URI that may be similar to http://<HostName>:<PortNumber>/ibmcognos/cgi-bin/cognos.cgi. Take note of the Cognos Connection interface. It has the GO Sales and GO Data Warehouse samples visible. Note the blue-colored folder icon, shown as in the preceding screenshot. It represents metadata model packages that are published to Cognos Connection using the Cognos Framework Manager tool. These packages have objects that represent business data objects, relationships, and calculations, which can be used to author reports and dashboards. Refer to the book, IBM Cognos TM1 Cookbook by Packt Publishing to learn how to create metadata models packages. From the toolbar, click on Launch. This will open a menu, showing different studios, each having different functionality, as shown in the following screenshot: We will use Business Insight and Business Insight Advanced, which are the first two choices in the preceding menu. These are the two components used to create and view dashboards. For other options, refer to the corresponding books by the same publisher. For instance, refer to the book, IBM Cognos 8 Report Studio Cookbook to know more about creating and distributing complex reports. Query Studio and Analysis Studio are meant to provide business users with the facility to slice and dice business data themselves. Event Studio is meant to define business situations and corresponding actions. Coming back to Cognos Connection, note that a yellow-colored folder icon, which is shown as represents a user-defined folder, which may or may not contain other published metadata model packages, reports, dashboards, and other content. In our case, we have a user-defined folder called Samples. This was created when we installed and configured samples shipped with the product. Click on the New Folder icon, which is represented by , on the toolbar to create a user-defined folder. Other options are also visible here, for instance to create a new dashboard.   Click on the user-defined folder—Samples to view its contents, as shown in the following screenshot: As shown in the preceding screenshot, it has more such folders, each having its own content. The top part of the pane shows the navigation path. Let's navigate deeper into Models | Business Insight Samples to show some sample dashboards, created using IBM Cognos Business Insight, as shown in the following screenshot: Click on one of these links to view the corresponding dashboard. For instance, click on Sales Dashboard (Interactive) to view the dashboard, as shown in the following screenshot: The dashboard can also be opened in the authoring tool, which is IBM Cognos Business Insight, in this case by clicking on the icon shown as on extreme right, on Cognos Connection. It will show the same result as shown in the preceding screenshot. We will see the Business Insight interface in detail later in this article. How it works... Cognos Connection is the primary user interface that user sees when he/she logs in for the first time. Business data has to be first identified and imported from the metadata model using the Cognos Framework Manager tool. Relationships (inner/outer joins) and calculations are then created, and the resultant metadata model package is published to the IBM Cognos 10 BI Server. This becomes available on Cognos Connection. Users are given access to appropriate studios on Cognos Connection, according to their needs. Analysis, reports, and dashboards are then created and distributed using one of these studios. The preceding sample has used Business Insight, for instance. Later sections in this article will look more into Business Insight and Business Insight Advanced. The next section focuses on the Business Insight interface details from the navigation perspective. Exploring IBM Cognos Business Insight User Interface In this recipe we will explore IBM Cognos Business Insight User Interface in more detail. We will explore various areas of the UI, each dedicated to perform different actions. Getting ready As stated earlier, we will be exploring different sections of Cognos Business Insight. Hence, make sure that IBM Cognos 10 BI installation is open and samples are set up properly. We will start the recipe assuming that the IBM Cognos Connection window is already open on the screen. How to do it... To explore IBM Cognos Business Insight User Interface, perform the following steps: In the IBM Cognos Connection window, navigate to Business Insight Samples, as shown in the following screenshot: Click on one of the dashboards, for instance Marketing Dashboard to open the dashboard in Business Insight. Different areas are labeled, as shown in the following figure: The overall layout is termed as Dashboard. The topmost toolbar is called Application bar . The Application bar contains different icons to manage the dashboard as a whole. For instance, we can create, open, e-mail, share, or save the dashboard using one of the icons on the Application bar. The user can explore different icons on the Application bar by hovering the mouse pointer over them. Hovering displays the tooltip, which has a brief but self-explanatory help text. Similarly, it has a Widget toolbar for every widget, which gets activated when the user clicks on the corresponding widget. When the mouse is focused away from the widget, the Widget toolbar disappears. It has various options, for instance to refresh the widget data, print as PDF, resize to ? t content, and so on. It also provides the user with the capability to change the chart type as well as to change the color palette. However, all these options have help text associated with them, which is activated on mouse hover. Content tab and Content pane show the list of objects available on the Cognos Connection. Directory structure on Cognos Connection can be navigated using Content pane and Content tab, and hence, available objects can be added to or removed from the dashboard. The drag-and-drop functionality has been provided as a result of which creating and editing a dashboard has become as simple as moving objects between the Dashboard area and Cognos Connection. The Toolbox tab displays additional widgets. The Slider Filter and Select Value Filter widgets allow the user to filter report content. The other toolbox widgets allow user to add more report content to the dashboard, such as HTML content, images, RSS feeds, and rich text. How it works... In the preceding section, we have seen basic areas of Business Insight. More than one user can log on to the IBM Cognos 10 BI server, and create various objects on Cognos Connection. These objects include packages, reports, cubes, templates, and statistics to name a few. These objects can be created using one or more tools available to users. For instance, reports can be created using one of the studios available. Cubes can be created using IBM Cognos TM1 or IBM Cognos Transformer and published on Cognos Connection. Metadata model packages can be created using IBM Cognos Framework Manager and published on Cognos Connection. These objects can then be dragged, dropped, and formatted as standalone objects in Cognos Business Insight, and hence, dashboards can be created.
Read more
  • 0
  • 0
  • 1068

article-image-article-ibm-cognos-business-intelligence-dashboard-business-insight-advanced
Packt
16 Jul 2012
4 min read
Save for later

IBM Cognos 10 Business Intelligence

Packt
16 Jul 2012
4 min read
Introducing IBM Cognos 10 BI Cognos Connection In this recipe we will be exploring Cognos Connection, which is the user interface presented to the user when he/she logs in to IBM Cognos 10 BI for the first time. IBM Cognos 10 BI, once installed and configured, can be accessed through the Web using supported web browsers. For a list of supported web browsers, refer to the Installation and Configuration Guide shipped with the product. Getting ready As stated earlier, make sure that IBM Cognos 10 BI is installed and configured. Install and configure the GO Sales and GO Data Warehouse samples. Use the gateway URI to log on to the web interface called Cognos Connection. How to do it... To explore Cognos Connection, perform the following steps: Log on to Cognos Connection using the gateway URI that may be similar to http://<HostName>:<PortNumber>/ibmcognos/cgi-bin/cognos.cgi. Take note of the Cognos Connection interface. It has the GO Sales and GO Data Warehouse samples visible. Note the blue-colored folder icon, shown as in the preceding screenshot. It represents metadata model packages that are published to Cognos Connection using the Cognos Framework Manager tool. These packages have objects that represent business data objects, relationships, and calculations, which can be used to author reports and dashboards. Refer to the book, IBM Cognos TM1 Cookbook by Packt Publishing to learn how to create metadata models packages. From the toolbar, click on Launch. This will open a menu, showing different studios, each having different functionality, as shown in the following screenshot: We will use Business Insight and Business Insight Advanced, which are the first two choices in the preceding menu. These are the two components used to create and view dashboards. For other options, refer to the corresponding books by the same publisher. For instance, refer to the book, IBM Cognos 8 Report Studio Cookbook to know more about creating and distributing complex reports. Query Studio and Analysis Studio are meant to provide business users with the facility to slice and dice business data themselves. Event Studio is meant to define business situations and corresponding actions. Coming back to Cognos Connection, note that a yellow-colored folder icon, which is shown as represents a user-defined folder, which may or may not contain other published metadata model packages, reports, dashboards, and other content. In our case, we have a user-defined folder called Samples. This was created when we installed and configured samples shipped with the product. Click on the New Folder icon, which is represented by , on the toolbar to create a user-defined folder. Other options are also visible here, for instance to create a new dashboard.   Click on the user-defined folder—Samples to view its contents, as shown in the following screenshot: As shown in the preceding screenshot, it has more such folders, each having its own content. The top part of the pane shows the navigation path. Let's navigate deeper into Models | Business Insight Samples to show some sample dashboards, created using IBM Cognos Business Insight, as shown in the following screenshot: Click on one of these links to view the corresponding dashboard. For instance, click on Sales Dashboard (Interactive) to view the dashboard, as shown in the following screenshot: The dashboard can also be opened in the authoring tool, which is IBM Cognos Business Insight, in this case by clicking on the icon shown as on extreme right, on Cognos Connection. It will show the same result as shown in the preceding screenshot. We will see the Business Insight interface in detail later in this article. How it works... Cognos Connection is the primary user interface that user sees when he/she logs in for the first time. Business data has to be first identified and imported from the metadata model using the Cognos Framework Manager tool. Relationships (inner/outer joins) and calculations are then created, and the resultant metadata model package is published to the IBM Cognos 10 BI Server. This becomes available on Cognos Connection. Users are given access to appropriate studios on Cognos Connection, according to their needs. Analysis, reports, and dashboards are then created and distributed using one of these studios. The preceding sample has used Business Insight, for instance. Later sections in this article will look more into Business Insight and Business Insight Advanced. The next section focuses on the Business Insight interface details from the navigation perspective.
Read more
  • 0
  • 0
  • 1461

article-image-android-database-programming-binding-ui
Packt
07 Jun 2012
6 min read
Save for later

Android Database Programming: Binding to the UI

Packt
07 Jun 2012
6 min read
(For more resources on Android, see here.) SimpleCursorAdapters and ListViews There are two major ways of retrieving data on Android, and each has its own class of ListAdapters, which will then know how to handle and bind the passed-in data. The first way of retrieving data is one that we're very familiar with already – through making queries and obtaining Cursor objects. The subclass of ListAdapters that wrap around Cursors is called CursorAdapter, and in the next section we'll focus on the SimpleCursorAdapter, which is the most straightforward instance of CursorAdapter. The Cursor points to a subtable of rows containing the results of our query. By iterating through this cursor, we are able to examine the fields of each row. Now we would like to convert each row of the subtable into a corresponding row in our list. The first step in doing this is to set up a ListActivity (a variant of the more common Activity class). As its name suggests, a ListActivity is simply a subclass of the Activity class which comes with methods that allow you to attach ListAdapters. The ListActivity class also allows you to inflate XML layouts, which contain list tags. In our example, we will use a very bare-bones XML layout (named list.xml) that only contains a ListView tag as follows: <?xml version="1.0" encoding="utf-8"?><LinearLayout android_orientation="vertical" android_layout_width="fill_parent" android_layout_height="wrap_content" > <ListView android_id="@android:id/android:list" android_layout_width="fill_parent" android_layout_height="wrap_content" /></LinearLayout> This is the first step in setting up what's called a ListView in Android. Similar to how defining a TextView allows you to see a block of text in your Activity, defining a ListView will allow you to interact with a scrollable list of row objects in your Activity. Intuitively, the next question in your mind should be: Where do I define how each row actually looks? Not only do you need to define the actual list object somewhere, but each row should have its own layout as well. So, to do this we create a separate list_entry.xml file in our layouts directory. The example I'm about to use is the one that queries the Contacts content provider and returns a list containing each contact's name, phone number, and phone number type. Thus, each row of my list should contain three TextViews, one for each data field. Subsequently, my list_entry.xml file looks like the following: <?xml version="1.0" encoding="utf-8"?><LinearLayout android_orientation="vertical" android_layout_width="fill_parent" android_layout_height="wrap_content" android_padding="10dip" > <TextView android_id="@+id/name_entry" android_layout_width="wrap_content" android_layout_height="wrap_content" android_textSize="28dip" /> <TextView android_id="@+id/number_entry" android_layout_width="wrap_content" android_layout_height="wrap_content" android_textSize="16dip" /> <TextView android_id="@+id/number_type_entry" android_layout_width="wrap_content" android_layout_height="wrap_content" android_textColor="#DDD" android_textSize="14dip" /></LinearLayout> So we have a vertical LinearLayout which contains three TextViews, each with its own properly defined ID as well as its own aesthetic properties (that is, text size and text color). In terms of set up – this is all we need! Now we just need to create the ListActivity itself, inflate the list.xml layout, and specify the adapter. To see how all this is done, let's take a look at the code before breaking it apart piece by piece: public class SimpleContactsActivity extends ListActivity { @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.list); // MAKE QUERY TO CONTACT CONTENTPROVIDER String[] projections = new String[] { Phone._ID, Phone.DISPLAY_NAME, Phone.NUMBER, Phone.TYPE }; Cursor c = getContentResolver().query(Phone.CONTENT_URI, projections, null, null, null); startManagingCursor(c); // THE DESIRED COLUMNS TO BE BOUND String[] columns = new String[] { Phone.DISPLAY_NAME, Phone.NUMBER, Phone.TYPE }; // THE XML DEFINED VIEWS FOR EACH FIELD TO BE BOUND TO int[] to = new int[] { R.id.name_entry, R.id.number_entry, R.id.number_type_entry }; // CREATE ADAPTER WITH CURSOR POINTING TO DESIRED DATA SimpleCursorAdapter cAdapter = new SimpleCursorAdapter(this, R.layout.list_entry, c, columns, to); // SET THIS ADAPTER AS YOUR LIST ACTIVITY'S ADAPTER this.setListAdapter(cAdapter); }} So what's going on here? Well, the first part of the code you should recognize by now – we're simply making a query over the phone's contact list (specifically over the Contact content provider's Phone table) and asking for the contact's name, number, and number type. Next, the SimpleCursorAdapter takes as two of its parameters, a string array and an integer array which represent a mapping between Cursor columns and XML layout views. In our case, this is as follows: // THE DESIRED COLUMNS TO BE BOUNDString[] columns = new String[] { Phone.DISPLAY_NAME, Phone.NUMBER, Phone.TYPE };// THE XML DEFINED VIEWS FOR EACH FIELD TO BE BOUND TOint[] to = new int[] { R.id.name_entry, R.id.number_entry, R.id.number_type_entry }; This is so that the data in the DISPLAY_NAME column will get bound to the TextView with ID name_entry, and so on. Once we have these mappings defined, the next part is to just instantiate the SimpleCursorAdapter, which can be seen in this line: // CREATE ADAPTER WITH CURSOR POINTING TO DESIRED DATASimpleCursorAdapter cAdapter = new SimpleCursorAdapter(this, R.layout.list_entry, c, columns, to); Now, the SimpleCursorAdapter takes five parameters – the first is the Context, which essentially tells the CursorAdapter which parent Activity it needs to inflate and bind the rows to. The next parameter is the ID of the R layout that you defined earlier, which will tell the CursorAdapter what each row should look like and, furthermore, where it can inflate the corresponding Views. Next, we pass in the Cursor, which tells the adapter what the underlying data actually is, and lastly, we pass in the mappings. Hopefully, the previous code makes sense, and the parameters of SimpleCursorAdapter make sense as well. The result of this previous Activity can be seen in the following screenshot: Everything looks good, except for these random integers floating around under the phone number. Why are there a bunch of 1s, 2s, and 3s at the bottom of each row where the types should be? Well, the phone number types are not returned as Strings but are instead returned as integers. From there through a simple switch statement, we can easily convert these integers into more descriptive Strings. However, you'll quickly see that with our very simple, straightforward use of the built-in SimpleCursorAdapter class, there was nowhere for us to implement any "special" logic that would allow us to convert such returned integers to Strings. This is when overriding the SimpleCursorAdapter class becomes necessary, because only then can we have full control over how the Cursor's data is to be displayed in each row. And so, we move onwards to the next section where we see just that.
Read more
  • 0
  • 0
  • 2505
article-image-plsql-using-collections
Packt
17 May 2012
18 min read
Save for later

PL/SQL: Using Collections

Packt
17 May 2012
18 min read
Collections—an overview A collection is a homogeneous single dimensional structure, which constitutes an ordered set of elements of a similar type. Being a homogeneous structure, all elements are of the same data type. The structure of the element contains cells with a subscript. The elements reside in these cells to make the index as their location information. The subscript or cell index becomes identification of an element and is used for its access. Structure of a collection type, SPORT, is shown in the following diagram. Note the subscript and elements into it. A new element, GOLF, enters at the last empty location and is represented as SPORT [6]: A collection element can be of any valid SQL data type or a user-defined type. An element of the SQL primitive data type is a scalar value while an element of the user-defined type is an object type instance. A collection can be used within a PL/SQL program by declaring a PL/SQL variable of collection type. The local PL/SQL variable can hold the instances of its collection type. Besides, a database column in a table can also be of the schema collection type. The collections in Oracle are strictly one dimensional. They cannot be realized on two-dimensional coordinates. However, multidimensional arrays can be realized when the collection has an object type or collection type attribute. A collection can be bounded or unbounded. Bounded collections can accommodate a limited number of elements while unbounded collections have no upper limit for subscripts. Collections provide an efficient way to organize the data in an array or set format while making the use of object-oriented features. An instance of a nested table or varray collection type is accessed as an object while the data is still stored in database columns. Collections can be used to avail data caching in programs and boost up the performance of SQL operations. On dedicated server connections, a session always uses User Global Area (UGA), a component of PGA, for collection operations. On the other hand, for shared server mode, the collection operations are still carried out in UGA; but UGA is now a part of System Global Area (SGA), thus indirectly in SGA. This is because in shared server connections, multiple server processes can affect a session, thus UGA must be allocated out of the SGA. Categorization Collections are of two types—persistent and non-persistent. A collection is persistent if it stores the collection structure and elements physically in the database. Contrarily, a non-persistent collection is active for a program only that is, maximum up to a session. Apart from the preceding categories, a collection can be realized in three formats namely, associative array, nested table or varray. This categorization is purely based on their objective and behavioral properties in a PL/SQL program. The following diagram combines the abstract and physical classification of collections: We will take a quick tour of these collection types now and discuss them in detail in the coming sections: Associative array (index-by table): This is the simplest form of non- persistent unbounded collections. As a non-persistent collection, it cannot be stored in the database, but they are available within a PL/SQL block only. The collection structure and data of associative array cannot be retained once the program is completed. Initially, during the days of Oracle 7, it was known as PL/SQL tables. Later, Oracle 8 version released it as index-by tables as they used an index to identify an element. Nested table: This is a persistent form of unbounded collections which can be created in the database as well as in PL/SQL block. Varray (variable-size array): This is a persistent but bounded form of collection which can be created in the database as well as in PL/SQL. Similar to a nested table, a varray is also a unidimensional homogeneous collection. The collection size and storage scheme are the factors which differentiate varrays from nested tables. Unlike a nested table, a varray can accommodate only a defined (fixed) number of elements. Selecting an appropriate collection type Here are a few guidelines to decide upon the appropriate usage of collection types in programs: Use of associative arrays is required when: You have to temporarily cache the program data in an array format for lookup purpose. You need string subscripts for the collection elements. Note that it supports negative subscripts, too. Map hash tables from the client to the database. Use of nested tables is preferred when: You have to stores data as sets in the database. Database columns of nested table type can be declared to hold the data persistently. Perform major array operations such as insertion and deletion, on a large volume of data. Use of varrays is preferred when: You have to store calculated or predefined volume of data in the database. Varray offers limited and defined storage of rows in a collection. Order of the elements has to be preserved. Associative arrays Associative arrays are analogous to conventional arrays or lists which can be defined within a PL/SQL program only. Neither the array structure nor the data can be stored in the database. It can hold the elements of a similar type in a key-value structure without any upper bound to the array. Each cell of the array is distinguished by its subscript, index, or cell number. The index can be a number or a string. Associative arrays were first introduced in Oracle 7 release as PL/SQL tables to signify its usage within the scope of a PL/SQL block. Oracle 8 release identified the PL/SQL table as Index by table due to its structure as an index-value pair. Oracle 10g release recognized the behavior of index by tables as arrays so as to rename it as associative arrays due to association of an index with an array. The following diagram explains the physical lookup structure of an associative array: Associative arrays follow the following syntax for declaration in a PL/SQL declare block: TYPE [COLL NAME] IS TABLE OF [ELEMENT DATA TYPE] NOT NULL INDEX BY [INDEX DATA TYPE] In the preceding syntax, the index type signifies the data type of the array subscript. RAW, NUMBER, LONG-RAW, ROWID, and CHAR are the unsupported index data types. The suited index types are BINARY_INTEGER, PLS_INTEGER, POSITIVE, NATURAL, SIGNTYPE, or VARCHAR2. The element's data type can be one of the following: PL/SQL scalar data type: NUMBER (along with its subtypes), VARCHAR2 (and its subtypes), DATE, BLOB, CLOB, or BOOLEAN Inferred data: The data type inherited from a table column, cursor expression or predefined package variable User-defined type: A user defined object type or collection type For illustration, the following are the valid conditions of the associative array in a PL/SQL block: /*Array of CLOB data*/ TYPE clob_t IS TABLE OF CLOB INDEX BY PLS_INTEGER; /*Array of employee ids indexed by the employee names*/ TYPE empno_t IS TABLE OF employees.empno%TYPE NOT NULL INDEX BY employees.ename%type; The following PL/SQL program declares an associative array type in a PL/ SQL block. Note that the subscript of the array is of a string type and it stores the number of days in a quarter. This code demonstrates the declaration of an array and assignment of the element in each cell and printing them. Note that the program uses the FIRST and NEXT collection methods to display the array elements. The collection methods would be covered in detail in the PL/SQL collection methods section: /*Enable the SERVEROUTPUT on to display the output*/ SET SERVEROUTPUT ON /*Start the PL/SQL block*/ DECLARE /*Declare a collection type associative array and its variable*/ TYPE string_asc_arr_t IS TABLE OF NUMBER INDEX BY VARCHAR2(10); l_str string_asc_arr_t; l_idx VARCHAR2(50); BEGIN /*Assign the total count of days in each quarter against each cell*/ l_str ('JAN-MAR') := 90; l_str ('APR-JUN') := 91; l_str ('JUL-SEP') := 92; l_str ('OCT-DEC') := 93; l_idx := l_str.FIRST; WHILE (l_idx IS NOT NULL) LOOP DBMS_OUTPUT.PUT_LINE('Value at index '||l_idx||' is '||l_str(l_ idx)); l_idx := l_str.NEXT(l_idx); END LOOP; END; / Value at index APR-JUN is 91 Value at index JAN-MAR is 90 Value at index JUL-SEP is 92 Value at index OCT-DEC is 93 PL/SQL procedure successfully completed. In the preceding block, note the string indexed array. A string indexed array considerably improves the performance by using indexed organization of array values. In the last block, we noticed the explicit assignment of data. In the following program, we will try to populate the array automatically in the program. The following PL/SQL block declares an associative array to hold the ASCII values of number 1 to 100: /*Enable the SERVEROUTPUT on to display the output*/ SET SERVEROUTPUT ON /*Start the PL/SQL Block*/ DECLARE /*Declare an array of string indexed by numeric subscripts*/ TYPE ASCII_VALUE_T IS TABLE OF VARCHAR2(12) INDEX BY PLS_INTEGER; L_GET_ASCII ASCII_VALUE_T; BEGIN /*Insert the values through a FOR loop*/ FOR I IN 1..100 LOOP L_GET_ASCII(I) := ASCII(I); END LOOP; /*Display the values randomly*/ DBMS_OUTPUT.PUT_LINE(L_GET_ASCII(5)); DBMS_OUTPUT.PUT_LINE(L_GET_ASCII(15)); DBMS_OUTPUT.PUT_LINE(L_GET_ASCII(75)); END; / 53 49 55 PL/SQL procedure successfully completed. The salient features of associative arrays are as follows: An associative array can exist as a sparse or empty collection Being a non-persistent collection, it cannot participate in DML transactions It can be passed as arguments to other local subprograms within the same block Sorting of an associative array depends on the NLS_SORT parameter An associative array declared in package specification behaves as a session-persistent array Nested tables Nested tables are a persistent form of collections which can be created in the database as well as PL/SQL. It is an unbounded collection where the index or subscript is implicitly maintained by the Oracle server during data retrieval. Oracle automatically marks the minimum subscript as 1 and relatively handles others. As there is no upper limit defined for a nested table, its size can grow dynamically. Though not an index-value pair structure, a nested table can be accessed like an array in a PL/SQL block. A nested table is initially a dense collection but it might become sparse due to delete operations on the collection cells. Dense collection is the one which is tightly populated. That means, there exists no empty cells between the lower and upper indexes of the collection. Sparse collections can have empty cells between the first and the last cell of the collection. A dense collection may get sparse by performing the "delete" operations. When a nested table is declared in a PL/SQL program, they behave as a one-dimensional array without any index type or upper limit specification. A nested table defined in a database exists as a valid schema object type. It can be either used in a PL/SQL block to declare a PL/SQL variable for temporarily holding program data or a database column of particular nested table type can be included in a table, which can persistently store the data in the database. A nested table type column in a table resembles a table within a table, but Oracle draws an out- of-line storage table to hold the nested table data. This scenario is illustrated in the following diagram: Whenever a database column of nested table type is created in a table (referred to as parent table), Oracle creates a storage table with the same storage options as that of the parent table. The storage table created by Oracle in the same segment carries the name as specified in the NESTED TABLE STORE AS clause during creation of the parent table. Whenever a row is created in the parent table, the following actions are performed by the Oracle server: A unique identifier is generated to distinguish the nested table instances of different parent rows, for the parent row The instance of the nested table is created in the storage table alongside the unique identifier of the parent row The Oracle server takes care of these nested table operations. For the programmer or user, the whole process is hidden and appears as a normal "insert" operation. A nested table definition in PL/SQL follows the following syntax: DECLARE TYPE type_name IS TABLE OF element_type [NOT NULL]; In the preceding syntax, element_type is a primitive data type or a user-defined type, but not as a REF CURSOR type. In a database, a nested table can be defined using the following syntax: CREATE [OR REPLACE] TYPE type_name IS TABLE OF [element_type] [NOT NULL]; / In the preceding syntax, [element_type] can be a SQL supported scalar data type, a database object type, or a REF object type. Unsupported element types are BOOLEAN, LONG, LONG-RAW, NATURAL, NATURALN, POSITIVE, POSITIVEN, REF CURSOR, SIGNTYPE, STRING, PLS_INTEGER, SIMPLE_INTEGER, BINARY_INTEGER and all other non-SQL supported data types. If the size of the element type of a database collection type has to be increased, follow this syntax: ALTER TYPE [type name] MODIFY ELEMENT TYPE [modified element type] [CASCADE | INVALIDATE]; The keywords, CASCADE or INVALIDATE, decide whether the collection modification has to invalidate the dependents or the changes that have to be cascaded across the dependents. The nested table from the database can be dropped using the DROP command, as shown in the following syntax (note that the FORCE keyword drops the type irrespective of its dependents): DROP TYPE [collection name] [FORCE] Nested table collection type as the database object We will go through the following illustration to understand the behavior of a nested table, when created as a database collection type: /*Create the nested table in the database*/ SQL> CREATE TYPE NUM_NEST_T AS TABLE OF NUMBER; / Type created. The nested table type, NUM_NEST_T, is now created in the database. Its metadata information can be queried from the USER_TYPES and USER_COLL_TYPES dictionary views: SELECT type_name, typecode, type_oid FROM USER_TYPES WHERE type_name = 'NUM_NEST_T'; TYPE_NAME TYPECODE TYPE_OID --------------- --------------- -------------------------------- NUM_NEST_T COLLECTION 96DE421E47114638A9F5617CE735731A Note that the TYPECODE value shows the type of the object in the database and differentiates collection types from user-defined object types: SELECT type_name, coll_type, elem_type_name FROM user_coll_types WHERE type_name = 'NUM_NEST_T'; TYPE_NAME COLL_TYPE ELEM_TYPE_NAME --------------- ---------- -------------------- NUM_NEST_T TABLE NUMBER Once the collection type has been successfully created in the database, it can be used to specify the type for a database column in a table. The CREATE TABLE statement in the following code snippet declares a column of the NUM_NEST_T nested table type in the parent table, TAB_USE_NT_COL. The NESTED TABLE [Column] STORE AS [Storage table] clause specifies the storage table for the nested table type column. A separate table for the nested table column, NUM, ensures its out-of-line storage. SQL> CREATE TABLE TAB_USE_NT_COL (ID NUMBER, NUM NUM_NEST_T) NESTED TABLE NUM STORE AS NESTED_NUM_ID; Table created. A nested table collection type in PL/SQL n PL/SQL, a nested table can be declared and defined in the declaration section of the block as a local collection type. As a nested table follows object orientation, the PL/SQL variable of the nested table type has to be necessarily initialized. The Oracle server raises the exception ORA-06531: Reference to uninitialized collection if an uninitialized nested table type variable is encountered during block execution. As the nested table collection type has been declared within the PL/SQL block, its scope, visibility, and life is the execution of the PL/SQL block only. The following PL/SQL block declares a nested table. Observe the scope and visibility of the collection variable. Note that the COUNT method has been used to display the array elements. /*Enable the SERVEROUTPUT to display the results*/ SET SERVEROUTPUT ON /*Start the PL/SQL block*/ DECLARE /*Declare a local nested table collection type*/ TYPE LOC_NUM_NEST_T IS TABLE OF NUMBER; L_LOCAL_NT LOC_NUM_NEST_T := LOC_NUM_NEST_T (10,20,30); BEGIN /*Use FOR loop to parse the array and print the elements*/ FOR I IN 1..L_LOCAL_NT.COUNT LOOP DBMS_OUTPUT.PUT_LINE('Printing '||i||' element: '||L_LOCAL_ NT(I)); END LOOP; END; / Printing 1 element: 10 Printing 2 element: 20 Printing 3 element: 30 PL/SQL procedure successfully completed. Additional features of a nested table In the earlier sections, we saw the operational methodology of a nested table. We will now focus on the nested table's metadata. Furthermore, we will demonstrate a peculiar behavior of the nested table for the "delete" operations. Oracle's USER_NESTED_TABLES and USER_NESTED_TABLE_COLS data dictionary views maintain the relationship information of the parent and the nested tables. These dictionary views are populated only when a database of a nested table collection type is included in a table. The USER_NESTED_TABLES static view maintains the information about the mapping of a nested table collection type with its parent table. The structure of the dictionary view is as follows: SQL> desc USER_NESTED_TABLES Name Null? Type ----------------------- -------- --------------- TABLE_NAME VARCHAR2(30) TABLE_TYPE_OWNER VARCHAR2(30) TABLE_TYPE_NAME VARCHAR2(30) PARENT_TABLE_NAME VARCHAR2(30) PARENT_TABLE_COLUMN VARCHAR2(4000) STORAGE_SPEC VARCHAR2(30) RETURN_TYPE VARCHAR2(20) ELEMENT_SUBSTITUTABLE VARCHAR2(25) Let us query the nested table relationship properties for the TAB_USE_NT_COL table from the preceding view: SELECT parent_table_column, table_name, return_type, storage_spec FROM user_nested_tables WHERE parent_table_name='TAB_USE_NT_COL' / PARENT_TAB TABLE_NAME RETURN_TYPE STORAGE_SPEC ---------------------------------------------------------------------- NUM NESTED_NUM_ID VALUE DEFAULT In the preceding view query, RETURN_TYPE specifies the return type of the collection. It can be VALUE (in this case) or LOCATOR. Another column, STORAGE_SPEC, signifies the storage scheme used for the storage of a nested table which can be either USER_SPECIFIED or DEFAULT (in this case). The USER_NESTED_TABLE_COLS view maintains the information about the collection attributes contained in the nested tables: SQL> desc USER_NESTED_TABLE_COLS Name Null? Type ----------------------- -------- --------------- TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME NOT NULL VARCHAR2(30) DATA_TYPE VARCHAR2(106) DATA_TYPE_MOD VARCHAR2(3) DATA_TYPE_OWNER VARCHAR2(30) DATA_LENGTH NOT NULL NUMBER DATA_PRECISION NUMBER DATA_SCALE NUMBER NULLABLE VARCHAR2(1) COLUMN_ID NUMBER DEFAULT_LENGTH NUMBER DATA_DEFAULT LONG NUM_DISTINCT NUMBER LOW_VALUE RAW(32) HIGH_VALUE RAW(32) DENSITY NUMBER NUM_NULLS NUMBER NUM_BUCKETS NUMBER LAST_ANALYZED DATE SAMPLE_SIZE NUMBER CHARACTER_SET_NAME VARCHAR2(44) CHAR_COL_DECL_LENGTH NUMBER GLOBAL_STATS VARCHAR2(3) USER_STATS VARCHAR2(3) AVG_COL_LEN NUMBER CHAR_LENGTH NUMBER CHAR_USED VARCHAR2(1) V80_FMT_IMAGE VARCHAR2(3) DATA_UPGRADED VARCHAR2(3) HIDDEN_COLUMN VARCHAR2(3) VIRTUAL_COLUMN VARCHAR2(3) SEGMENT_COLUMN_ID NUMBER INTERNAL_COLUMN_ID NOT NULL NUMBER HISTOGRAM VARCHAR2(15) QUALIFIED_COL_NAME VARCHAR2(4000) We will now query the nested storage table in the preceding dictionary view to list all its attributes: SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, HIDDEN_COLUMN FROM user_nested_table_cols where table_name='NESTED_NUM_ID' / COLUMN_NAME DATA_TYP DATA_LENGTH HID ------------------------------ ---------- ----------- ----- NESTED_TABLE_ID RAW 16 YES COLUMN_VALUE NUMBER 22 NO We observe that though the nested table had only number elements, there is two- columned information in the view. The COLUMN_VALUE attribute is the default pseudo column of the nested table as there are no "named" attributes in the collection structure. The other attribute, NESTED_TABLE_ID, is a hidden unique 16-byte system generated raw hash code which latently stores the parent row identifier alongside the nested table instance to distinguish the parent row association. If an element is deleted from the nested table, it is rendered as parse. This implies that once an index is deleted from the collection structure, the collection doesn't restructure itself by shifting the cells in a forward direction. Let us check out the sparse behavior in the following example. The following PL/SQL block declares a local nested table and initializes it with a constructor. We will delete the first element and print it again. The system raises the NO_DATA_FOUND exception when we query the element at the index 1 in the collection:   /*Enable the SERVEROUTPUT to display the block messages*/ SQL> SET SERVEROUTPUT ON /*Start the PL/SQL block*/ SQL> DECLARE /*Declare the local nested table collection*/ TYPE coll_method_demo_t IS TABLE OF NUMBER; /*Declare a collection variable and initialize it*/ L_ARRAY coll_method_demo_t := coll_method_demo_t (10,20,30,40,50); BEGIN /*Display element at index 1*/ DBMS_OUTPUT.PUT_LINE('Element at index 1 before deletion:'||l_ array(1)); /*Delete the 1st element from the collection*/ L_ARRAY.DELETE(1); /*Display element at index 1*/ DBMS_OUTPUT.PUT_LINE('Element at index 1 after deletion:'||l_ array(1)); END; / Element at index 1 before deletion:10 DECLARE * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 15
Read more
  • 0
  • 0
  • 8214

article-image-troubleshooting-intuit-quickbooks
Packt
08 May 2012
12 min read
Save for later

Troubleshooting with Intuit Quickbooks

Packt
08 May 2012
12 min read
The following table is the Recipe Reference Card for the keyboard shortcuts included in this article: Find Ctrl+F Delete the line Ctrl+Del Save and close Alt+A Advance to the next field Tab Regress to the previous field Shift+Tab Customize the report Alt+M   Clearing stale undeposited funds When the Undeposited Funds window includes customer payments, which you know have already been deposited, recorded, and reconciled, the Income or Unearned Income and Undeposited Funds accounts are overstated. You can use this recipe to efficiently combine the cleared deposit with the undeposited funds. Getting ready Verify that the appropriate bank account is reconciled for the period containing the stale undeposited funds. If not, this can be resolved simply by deleting the recorded deposit and recording the deposit of the undeposited funds. How to do it... With the Find tool (Edit | Find or Ctrl+F), use the Amount filter, along with the Date filter, if necessary, to bring up both the deposit and the customer payment already recorded. Open up the deposit, and click on the Payments button. Check off the appropriate transaction, and click on OK to add this to the deposit. Click on the line item for the deposit originally on the screen, that is, the duplicate of the payment that you just added to this screen. Click on Edit | Delete Line (Ctrl+Del), and then Save and Close (Alt+A). How it works… The only way to directly delete an item added to Undeposited Funds is to delete the underlying customer payment or sales receipt. However, this is not advisable, because these transactions are typically accurate representations of a real-world activity. Additionally, when the deposit was recorded, the related account duplicated the income or customer deposit from the original invoice or sales receipt. Therefore, the deposit itself needs to be modified to simultaneously remove the duplicate offset account, and resolve the outstanding Undeposited Funds item. There's more… For a printable and memorizable list of all outstanding items in the Undeposited Funds account, open the Undeposited Funds ledger. Click on Customize Report | Filters | Choose Filter | Cleared | No. On the Header/Footer tab, in the Report Title field, enter Undeposited Funds, and click on OK. Adjusting cash basis receivables or payables balances Does your cash basis balance sheet show balance in your receivable or payables accounts? This recipe will take you through the two-step process of resolving these items: Locate them Adjust them Getting ready To find out which customers and vendors are responsible for your cash basis accounts receivable or accounts payable balances, respectively, run the following report: Go to Reports | Custom Reports | Summary. Set Dates to All. If you desire a cut-off date, leave the From field blank, and enter your cut-off date in the To field. Set Report Basis to Cash. Set Display rows by to Customer or Vendor. Go to Advanced, and set Display Rows to Non-zero. Go to the Filters tab, and set Account to Accounts Receivable (or Accounts Payable). Go to the Header/Footer tab, and set Report Title to Cash Basis A/R by Customer or Cash Basis A/P by Vendor. The report total matches your balance sheet account total for the same cut-off date. How to do it... Double-click one of the account balances to reveal the detail, and remove the columns irrelevant to this effort. Scan the activity for patterns, unusual items, or clues about the cash basis balance. Gain an understanding of the transaction, and resolve the items by changing the accounts, changing a date, making a journal entry, noting that no adjustment is needed, or other action. Refresh the report, and confirm either a zero balance or an appropriate cash basis balance. How it works… Some of the most likely patterns to scan for include the following: A Balance column, which keeps returning to 0.00, and then stops returning to 0.00: An unusual transaction Type: A recurring figure in the Balance column. This lets you know that at least one culprit occurred before the recurrence began: A zero balance right before a transaction, which is also the aggregate balance sheet account balance: There's more… The most common reasons for a cash basis receivables or payable balance are: Payment date precedes bill or invoice date, and the report cut-off is between both the dates Offset account is a balance sheet account, and the bill or the invoice is unpaid Writing off stale receivables Making a journal entry to write off stale A/R in bulk is easy, but this makes it difficult to trace through the accounting records. The possible uses for more precise information include producing a trail for taxing authorities, internal or independent auditors, or banks. A separate spreadsheet may suffice, but it may be difficult to coordinate. This recipe focuses on straightforward ways to write off these balances in a detailed, but effi cient fashion. Getting ready Have your criteria ready for which invoices are to be written off. The A/R Aging Summary report may help (Reports | Customers & Receivables | A/R Aging Summary). To further analyze your oldest receivables: Go to Customize Report | Age through how many days?, and type 360. Go to Filters | Choose Filter | Aging | >=, and type 90. Go to Header/Footer, add the text: Older Than 90 days to the Report Title, and click on OK. How to do it... To write off a stale receivable: Go to the Customers page or Home Page | Receive Payments. From the Received From drop-down box, select the appropriate customer. If applicable, select the particular job instead. In the Date field, enter the effective date of the writeoff. Click on the Discount & Credits button. In the Discount and Credits pop-up window, fill in the amount to be written off, the writeoff account (generally Bad Debt), and the same class from the original invoice, if class tracking is used in the file: The completed screen should have 0.00 in the Amount and Payment fields. Include the amount written off in the Discount field: If an allowance for doubtful accounts is used against bad debt for writeoffs, then set up the Allowance account as an Accounts Receivable account type , and select the Allowance account from the drop-down box at the top of the Customer Payments screen. A set of journal entries can be used later, to remove the amounts from both Accounts Receivable and the Allowance account. There's more… This is the same procedure that can be used to record discounts, but the key is that an income or expense account must always be selected. This procedure is not appropriate for a balance sheet account to be selected, such as debiting a liability account while crediting A/R, or debiting the Allowance account while crediting A/R. This will cause a cash basis balance sheet report to be out of balance. If that combination of debits and credits is essential, then use a journal entry instead. Then, apply the journal entry to the original invoice, by opening the invoice, and clicking the Apply Credits button. When this recipe is used to write off receivables, Act. Revenue is reduced in the Job Profitability Summary report , and there is no effect on the Item Profitability Summary report. The same reporting results are attained if a journal entry is used to debit Bad Debt Expense and credit Accounts Receivable. If a Credit Memo is used instead, Act. Revenue is reduced in the Job Profitability Summary report as well as the Item Profitability Summary report. In order to increase the Act. Cost column in the Job Profitability Summary report instead, use the Write Checks screen in an unusual fashion: on the Items tab, use an Other Charge item called Bad Debt or Writeoffs. When you create this item, link it to the Bad Debt Expense account. On the Write Checks screen, be sure to enter the Customer:Job name as well as the writeoff amount. On the Expenses tab, select Accounts Receivable, and enter the writeoff amount as a negative number, so that the total amount of the check equals 0. Be sure that the check bears no check number, and clear it in the next bank reconciliation. This technique causes both the Job Profitability and Item Profitability reports to show the transaction as an expense, rather than as a reduction of revenue. It works because QuickBooks includes the Write Check transactions in the Act. Cost column of these reports. Writing off stale payables Making a journal entry to write off stale A/P in bulk is easy, but makes it difficult to trace through the accounting records. Possible uses for more precise information include producing a trail for taxing authorities, internal or independent auditors, or banks. A separate spreadsheet may suffice, but may be difficult to coordinate. This recipe focuses on straightforward ways to write off these balances in a detailed but efficient fashion. Getting ready Have your criteria ready for which bills are to be written off. The A/P Aging Summary report may help (Reports | Vendors & Payables | A/P Aging Summary). To further analyze your oldest payables: Go to Customize Report | Age through how many days?, and type 360. Go to Filters | Choose Filter | Aging | >=, and type 90. Go to Header/Footer, add the text: Older Than 90 days to the Report Title, and click on OK. How to do it... Go to Vendors or Home Page | Pay Bills. Consider using the Filter by drop-down list to only show bills from a particular vendor, and consider using the Sort by drop-down list to organize the payables list by Due Date. For one single vendor, check off the fi rst bill to be written off. Click on the Set Discount button . In the Discount and Credits pop-up window, fi ll in the amount to be written off, the writeoff account (generally the same expense account as the original bill), and the same class from the original bill, if class tracking is used in the file: Click on Done, and proceed to the next bill for the same vendor. Make sure the Payment Date field is the effective date of the write off. The completed screen should have 0.00 in the Amt. to Pay field. Include the amount written off in the Disc. Used field: When the writeoffs for that vendor are complete, click on Pay Selected Bills, followed by Pay More Bills for additional writeoffs. There's more… The advantage of this recipe is that the transaction is created and applied to the bill in a single step. However, the drawback is that it does not appear in the Job Profitability Summary or the Item Profitability Summary reports. For that to occur, create a vendor credit instead, by using the Enter Bills screen, and clicking on the Credit button. Then, use the Items tab to record the credit, using the same item that was used in the original bill. Additionally, use the Customer:Job field to apply the credit to a particular job. For a partial writeoff, after the Discount and Credits window is closed, be sure to manually input 0.00 into the Amt. to Pay field. The default is to include the remaining balance in that field, and this recipe assumes that the current action is only to record writeoffs, not payments to vendors. Balancing the balance sheet How can a balance sheet get out of balance in a software program? If you're reading this recipe, you may have already seen for yourself that the impossible can happen. The following is a procedure to root out the transaction which is causing this phenomenon. Getting ready A balance sheet prepared on the cash basis can be out of balance if certain transactions were saved, for example if the Discount feature was used with a balance sheet account. How to do it... Open a Balance Sheet Summary report. Click on the Customize Report button and in the Dates drop-down box, select All. If the report is on the accrual basis, change the Report Basis to Cash. In the Display columns by drop-down box, change the selection to Year, and click on OK. Look at the balance sheet, and identify the earliest year in which the balance sheet is out of balance. Click on the Customize Report button . In the From and To fields, enter the beginning and ending dates of the year identified in the previous step. In the Display columns by drop-down box, change the selection to Month, and click on OK. Look at the balance sheet, and identify the earliest month in which the balance sheet is out of balance. Click on the Customize Report button . In the From and To fields, enter the beginning and ending dates of the month identified in the previous step. In the Display columns by drop-down box , change the selection to Week, and click on OK. Look at the balance sheet, and identify the earliest week in which the balance sheet is out of balance. Click on the Customize Report button . In the From and To fields, enter the beginning and ending dates of the week identified in the previous step. In the Display columns by drop-down box, change the selection to Day, and click on OK. Look at the balance sheet, and identify the earliest day in which the balance sheet is out of balance: Run a transaction journal (Reports | Accountant & Taxes | Journal), limit the transactions to that day, and scan the report for the transaction responsible. Delete the transaction which caused the imbalance, which is usually a Customer Payment or other A/R or A/P data entry screen, and make a journal entry instead, to cover the appropriate debit and credit. There's more… If the Discount feature was used to reclassify an Accounts Receivable balance to Retainage Receivable, make a journal entry to achieve the same General Ledger effect instead, and apply the transaction to the original invoice, by opening the invoice and using the Apply Credits button.
Read more
  • 0
  • 0
  • 2142

article-image-visualizations-made-easy-gnuplot
Packt
01 Mar 2012
12 min read
Save for later

Visualizations made easy with gnuplot

Packt
01 Mar 2012
12 min read
  This article, written by Lee Philips the author of gnuplot Cookbook contains the following :   Making a surface plot Using coordinate mappings Coloring the surface Making a contour plot Making a vector plot Making an image plot or heat map Combining contours and images Combining surfaces with images Plotting a path in 3D Drawing parametric surfaces Making a surface plot A surface plot represents the dependent quantity z, which depends on the two independent variables x and y, as a surface whose height indicates the value of z.   The previous figure is a perspective drawing of a surface representing the Bessel function J0(r), where r is the distance from (x=0, y=0). The height of the surface shows the value of J0, given on the vertical axis (unlabeled in this figure, but usually called z). The other two (unlabeled) axes defning the plane above which the surface is drawn are the x and y axes.     How to do it… The following code listing is the script that coaxed gnuplot into making the previous figure:   set isosamples 40unset keyset title "J_0(r^2)"set xrange [-4:4]set yrange [-4:4]set ztics 1splot besj0(x**2+y**2)set view 29,53 #Done implicitly by mousing.set term pngcairo mono enhancedset out 'bessel.png'replot   How it works… There are several new commands in this recipe. The set isosamples command sets the isoline density. This is analogous to the set samples command when making 2D plots, but it sets the number of lines used in forming the surface. The number of isosamples can be set independently in each direction; if one number is specifed, it is used for both directions. The default of 10 usually creates surfaces that are far too coarse to be useful.   Turning to the second of the highlighted commands, the splot command is the 3D version of our old friend the plot command (it probably initially stood for "surface plot", but now can do several things besides plot surfaces, as we shall see in the rest of this article). It expects a function of x and y rather than x alone. Although we are interested in plotting something that has the type of symmetry that would be most conveniently expressed in polar (spherical or cylindrical) coordinates, these geometries are not available for function plots in 3D in gnuplot. (They are available through the set mapping command for data plots, as we shall see later in this article.) Therefore in such cases, we are required to convert our expressions to the rectangular coordinate system. Instead of what we would call r in a cylindrical coordinate system, here we use the equivalent x**2 + y**2.   In this recipe, we would like to illustrate, as far as possible, the interactive approach to creating a fnal 3D plot. The next highlighted line, beginning with set view, can be entered on the command line or included in a script. The view is the orientation in degrees of the perspective drawing of the 3D plot. Naturally, it does arise in 2D. It is diffcult to determine what is the most useful view for a particular plot without looking at it and experimenting with it; therefore, even if our fnal product is intended to be a fle, a common workfow is to frst create the plot using an interactive terminal (x11 or wxt). Then we rotate the plot with the mouse, and possibly scale and zoom it using the middle mouse button, until we arrive at the desired appearance. This is what we mean by the comment in the set view command. Now we can reset the terminal to the fnal output device that we need, specify the output fle, and simply say replot. The view and scaling at which we left the interactive plot is retained as a set of global settings and will be refected in our fnal output fle. These settings are also displayed at the bottom of the interactive plot window, so we can record them if we are going to make similar plots in the future, or want a set of plots to be drawn with the same settings. Note that we also redefned the ztics value. This is because when the plot is tilted to the fnal view angle that we chose, the perspective causes the tic labels on the vertical axis to be crowded together; this is a common problem with 3D plots, and taking manual control of the tics on the z-axis is the solution.   There's more… Following is the same plot with one setting changed (aside from a slight adjustment in the view angle):   While the frst plot was essentially a wireframe that we could see through, this version has the appearance of a solid, opaque surface. All we need to do is to say set hidden3d. This, which only works when lines or linespoints are being used, makes the surface appear opaque by removing from the plot any part of the surface, other surfaces, and other plot elements such as the axes and tic labels, that are behind the surface from our point of view. The underside of the surface is shown in a contrasting color with a color output device, but the two sides of the surface are not distinguished in monochrome. The name of the setting refers to the technique of hidden line removal; gnuplot is justly famed for the quality of its hidden line removal algorithm, and is one reason this program is so well regarded for its 3D plotting ability.   Using coordinate mappings It is possible, when making 3D plots from data fles, for the data to be interpreted in spherical or cylindrical coordinates rather than the default Cartesian system. For details, type help set mapping. We will give an example of using the cylindrical mapping to conveniently draw a shape with cylindrical symmetry.   The previous figure is a perspective view of a surface that somewhat resembles a Christmas tree ornament. The relevant feature of this surface is that it has rotational symmetry around the z (vertical) axis, which means that it is most naturally expressed in cylindrical coordinates.   How to do it… Try the following script:   set mapping cylindricalunset ticsunset borderset hiddenset xrange [-pi : pi]set yrange [-pi : pi]set zrange [0 : pi]set iso 60unset keysplot '++' using 1:2:(sin($2)) with lines   How it works… There are several new ideas used in this recipe. Breaking it down, these are:   The set mapping command The frst, highlighted line contains the new command that is the subject of this recipe. When the default Cartesian (x-y-z) coordinate system is changed to cylindrical then the columns of data read in during a data plot are interpreted as θ-z-r, where θ is the angular coordinate, z is the vertical coordinate, and r is the radius. A spherical mapping is also available and explained in the gnuplot online help (help set mapping). If the data fle only has two columns, then the plot is drawn with r = 1.   In our example we don't want to plot from a data fle, however. We want to plot a function given directly in the script. This presents us with a problem, as gnuplot does not support cylindrical or spherical plots of functions in 3D. The solution is to use one of gnuplot's pseudofles.   The ++ pseudofle The "++" pseudofle creates rows of imaginary data with three columns x-y-z unless we change the coordinate mapping, which of course in this example we have. Setting the mapping to cylindrical means that the fctitious data columns will be interpreted as θ-z-r.   Now to plot a function, we use the using notation applied to the imaginary columns of data. We've done this in the fnal line of the script, where we plot the sine of the second column (z).   To clarify the use of "++" when plotting surfaces, note that, in Cartesian coordinates, the two commands "splot sin(x)+cos(y)" and "splot '++' using 1:2:(sin($1)+cos($2)) with lines" produce exactly the same plot.   Coordinate ranges We have also established ranges for all variables in the set xrange and two other commands following it. The ranges for the polar coordinates are taken from the corresponding Cartesian coordinates, that is, when we set the xrange, we are setting both the range of the x-axis displayed on the plot and the range of the variable θ in the cylindrical coordinate system. It is mandatory to set xrange and yrange when using the "++" flename.   This mixing of the coordinate system in which the function is calculated and the Cartesian system in which it is displayed can be confusing, but the example shows a strategy, which should make it possible to get predictable results. Setting the xrange and yrange as we've done puts the r = 0 point in the middle of the graph and prevents part of the plot from being cut off. It also sets up a full rotation of the angular coordinate over a range of 2 p.   If we wanted to plot, say, our shape with half of it sliced off by a vertical plane, the easiest way to do this is not to fddle with the coordinate ranges, but to apply a transformation to one of the fctitious data columns: splot '++' using ($1/2):2:(sin($2)) with lines, will do the trick without any surprising side effects. In this example the underlying angular coordinate (column 1) still passes through a full rotation, but we've divided it in half without changing the way the figure is projected onto the Cartesian display. Note that the 60 isolines will still be used in our reduced angular range, so we might want to set iso to a smaller value.   Completing the picture We've eliminated all of the graph adornments (unset tics, unset border, unset key) so we will be left with only the surface. The isosamples are set to give a suffciently smooth surface drawing that is nevertheless not too crowded with isosurface lines (see the previous recipe). set hidden ensures that we shall see only the outer surface of the shape.   Coloring the surface The wireframe splot with hidden line removal that we covered in the frst recipe of this article, Making a surface plot, gives the visual impression of a solid surface. The numerical value encoded into the surface's height can be visually estimated, roughly, by the perspective provided by the isolines in conjunction with the tics on the vertical axis. But gnuplot also has a way to draw real solid surfaces whose height is indicated by color or shade.   The previous figure shows the same mathematical function plotted in the frst recipe in this article (Making a surface plot). Now the numerical value of the function at any point is indicated by both the height of the surface and its shade; the surface is now drawn as an opaque membrane rather than as a network of curves.   How to do it…   To produce the previous figure, run the following in gnuplot:   set isosamples 100set samples 100unset keyset title "J_0(r^2)"set xrange [-4:4]set yrange [-4:4]set ztics 1unset surfaceset pm3dsplot besj0(x**2+y**2) The surface will be drawn with a palette of colors when a color output device is being used and with a grayscale palette when using a monochrome terminal.     How it works… If you compare the previous script with the one in the Making a surface plot recipe at the beginning of this article, you will see that the only signifcant difference is the highlighted line. The pm3d mode colors the imaginary surface being plotted according to its height or z-value at every point, with the mapping between the height and the color or shade determined by the palette, which we shall discuss in some more detail shortly.   The other modifcations are to increase the number of isolines, in order to get a smoother surface, and to turn off the drawing of the individual isolines themselves with the command unset surface. We also need to set the sample frequency; generally we want this to be equal to the isosample frequency. In pm3d mode, the two orthogonal sets of isolines are drawn with two different spacings given by the two parameters. Although the gnuplot manual claims that the global hidden3d setting does not affect pm3d surface plots, it in fact seems to, and should not be turned on, as it appears to slightly degrade the drawing quality.   There's more… Sometimes we want both a colored surface and a set of isolines; in fact, this can often be the clearest type of quantitative 3D plot. The way to achieve the highest quality in this type of graph is to use the hidden3d linestyle option to pm3d, as we do in the following script:   set iso 30set samp 30unset keyset title "J_0(r^2)"set xrange [-4:4]set yrange [-4:4]set ztics 1unset surfset style line 1 lt 4 lw .5set pm3d at s hidden3d 1splot besj0(x**2+y**2) This requires us to defne a user linestyle. Then the linestyle is referred to in an option to the set pm3d command. This will cause the isolines to be drawn using lines in this style, which allows us to have them in any color, thickness, or pattern supported by our terminal. Further, the isolines will be drawn with hidden line removal, so they will appear to be embedded in the opaque surface. As before, the global hidden3d option should not be turned on.   Note that we've also reduced the sample and isoline frequency, to keep our plot from being too crowded with isolines. (The at s component of the set pm3d command means at surface.)  
Read more
  • 0
  • 0
  • 13522
article-image-administrating-mysql-server
Packt
09 Feb 2012
10 min read
Save for later

Administrating the MySQL Server

Packt
09 Feb 2012
10 min read
(For more resources on MySQL, see here.) Managing users and their privileges The Privileges sub-page (visible only if we are logged in as a privileged user) contains dialogs to manage MySQL user accounts. It also contains dialogs to manage privileges on global, database, and table levels. This sub-page is hierarchical. When editing a user's privileges, we can see the global privileges as well as the database-specific privileges. Then, when viewing database-specific privileges for a user, we can view and edit this user's privileges for any table within this database. The user overview The first page displayed when we enter the Privileges sub-page is called User overview. This shows all user accounts and a summary of their global privileges, as shown in the following screenshot: (Move the mouse over the image to enlarge.) From this page, we can: Edit a user's privileges, via the Edit Privileges link for this user Export a user's privileges definition, via the Export link for this user Use the checkboxes to remove users, via the Remove selected users dialog Access the page where the Add a new User dialog is available The displayed users' list has columns with the following characteristics: Column Characteristic User The user account we are defining. Host The machine name or IP address, from which this user account will be connecting to the MySQL server. A % value here indicates all hosts. Password Contains Yes if a password is defined and No if it isn't. The password itself cannot be seen from phpMyAdmin's interface or by directly looking at the mysql.user table, as it is encrypted with a one-way hashing algorithm. Global privileges A list of the user's global privileges. Grant Contains Yes if the user can grant his/her privileges to others. Action Contains a link to edit this user's privileges or export them. Exporting privileges This feature can be useful when we need to create a user with the same password and privileges on another MySQL server. Clicking on Export for user marc produces the following panel: Then it's only a matter of selecting these GRANT statements and pasting them in the SQL box of another phpMyAdmin window, where we have logged in on another MySQL server. Privileges reload At the bottom of User overview page, this message is displayed: Note: phpMyAdmin gets the users' privileges directly from MySQL's privilege tables. The content of these tables may differ from the privileges the server uses, if they have been changed manually. In this case, you should reload the privileges before you continue. Here, the text reload the privileges is clickable. The effective privileges (the ones against which the server bases its access decisions) are the privileges that are located in the server's memory. Privilege modifications that are made from the User overview page are made both in memory and on disk in the mysql database. Modifications made directly to the mysql database do not have immediate effect. The reload the privileges operation reads the privileges from the database and makes them effective in memory. Adding a user The Add a new User link opens a dialog for user account creation. First, we see the panel where we will describe the account itself, as shown in the following screenshot: The second part of the Add a new User dialog is where we will specify the user's global privileges, which apply to the server as a whole (see the Assigning global privileges section of this article), as shown in the following screenshot: Entering the username The User name menu offers two choices. We can choose Use text field: and enter a username in the box, or we can choose Any user to create an anonymous user (the blank user). More details about the anonymous user are available at http://dev.mysql.com/doc/refman/5.5/en/connection-access.html. Let us choose Use text field: and enter bill. Assigning a host value By default, this menu is set to Any host, with % as the host value. The Local choice means localhost. The Use host table choice (which creates a blank value in the host field) means to look in the mysql.host table for database-specific privileges. Choosing Use text field: allows us to enter the exact host value we want. Let us choose Local. Setting passwords Even though it's possible to create a user without a password (by selecting the No password option), it's best to have a password. We have to enter it twice (as we cannot see what is entered) to confirm the intended password. A secure password should have more than eight characters, and should contain a mixture of uppercase and lowercase characters, digits, and special characters. Therefore, it's recommended to have phpMyAdmin generate a password—this is possible in JavaScript-enabled browsers. In the Generate password dialog, clicking on Generate button enters a random password (in clear text) on the screen and fills the Password and Re-type input fields with the generated password. At this point, we should note the password so that we can pass it on to the user. Understanding rights for database creation A frequent convention is to assign a user the rights to a database having the same name as this user. To accomplish this, the Database for user section offers the Create database with same name and grant all privileges radio button. Selecting this checkbox automates the process by creating both the database (if it does not already exist) and assigning the corresponding rights. Please note that, with this method, each user would be limited to one database (user bill, database bill). Another possibility is to allow users to create databases that have the same prefix as their usernames. Therefore, the other choice Grant all privileges on wildcard name (username_%) performs this function by assigning a wildcard privilege. With this in place, user bill could create the databases bill_test, bill_2, bill_payroll, and so on; phpMyAdmin does not pre-create the databases in this case. Assigning global privileges Global privileges determine the user's access to all databases. Hence, these are sometimes known as superuser privileges. A normal user should not have any of these privileges unless there is a good reason for this. Moreover, should a user account that has global privileges become compromised, the damage could be far greater. If we are really creating a superuser, we will select every global privilege that he or she needs. These privileges are further divided into Data, Structure, and Administration groups. In our example, bill will not have any global privileges. Limiting the resources used We can limit the resources used by this user on this server (for example, the maximum queries per hour). Zero means no limit. We will not impose any resources limits on bill. The following screenshot shows the status of the screen just before hitting Create user to create this user's definition (with the remaining fields being set to default): Editing a user profile The page used to edit a user's profile appears whenever we click on Edit Privileges for a user in the User overview page. Let us try it for our newly created user bill. There are four sections on this page, each with its own Go button. Hence, each section is operated independently and has a distinct purpose. Editing global privileges The section for editing the user's privileges has the same look as the Add a new User dialog, and is used to view and to change global privileges. Assigning database-specific privileges In this section, we define the databases to which our user has access, and his or her exact privileges on these databases. As shown in the previous screenshot, we see None because we haven't defined any privileges yet. There are two ways of defining database privileges. First, we can choose one of the existing databases from the drop-down menu as shown in the following screenshot: This assigns privileges only for the chosen database. Secondly, we can also choose Use text field: and enter a database name. We could enter a non-existent database name, so that the user can create it later (provided we give him/her the CREATE privilege in the next panel). We can also use special characters, such as the underscore and the percent sign, for wildcards. For example, entering bill here would enable him to create a bill database, and entering bill% would enable him to create a database with any name that starts with bill. For our example, we will enter bill and click on Go. The next screen is used to set bill's privileges on the bill database, and create table-specific privileges. To learn more about the meaning of a specific privilege, we can hover the mouse over a privilege name (which is always in English), and an explanation about this privilege appears in the current language. We give SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, and DROP privileges to bill on this database. We then click on Go. After the privileges have been assigned, the interface stays at the same place, so that we can refine these privileges further. We cannot assign table-specific privileges for the moment, as the database does not yet exist. To go back to the general privileges page of bill, click on the 'bill'@'localhost' title. This brings us back to the following, familiar page except for a change in one section: We see the existing privileges (we could click on Edit Privileges link to edit or on Revoke link to revoke them) on the bill database for user bill, and we can add privileges for bill on another database. We can also see that bill has no table-specific privilege on the bill database. Changing the password The Change password dialog is part of the Edit user page, and we can use it either to change bill's password or to remove it. Removing the password will enable bill to log in without a password. The dialog offers a choice of password hashing options, and it's recommended to keep the default of MySQL 4.1+ hashing. For more details about hashing, please visit http://dev.mysql.com/doc/refman/5.1/en/password-hashing.html. Changing login information or copying a user This dialog can be used to change the user's login information, or to copy his or her login information to a new user. For example, suppose that Bill calls and tells us that he prefers the login name billy instead of bill. We just have to add a y to the username, and then select delete the old one from the user tables radio button, as shown in the following screenshot: After clicking on Go, bill no longer exists in the mysql database. Also, all of his privileges, including the privileges on the bill database, will have been transferred to the new user—billy. However, the user definition of bill will still exist in memory, and hence it's still effective. If we had chosen the delete the old one from the user tables and reload the privileges afterwards option instead, the user definition of bill would immediately have ceased to be valid. Alternatively, we could have created another user based on bill, by making use of the keep the old one choice. We can transfer the password to the new user by choosing Do not change the password option, or change it by entering a new password twice. The revoke all active privileges… option immediately terminates the effective current privileges for this user, even if he or she is currently logged in. Removing a user Removing a user is done from the User overview section of the Privileges page. We select the user to be removed. Then (in Remove selected users) we can select the Drop the databases that have the same names as the users option to remove any databases that are named after the users we are deleting. A click on Go effectively removes the selected users.
Read more
  • 0
  • 0
  • 2430

article-image-integrating-ibm-cognos-tm1-ibm-cognos-8-bi
Packt
16 Dec 2011
4 min read
Save for later

Integrating IBM Cognos TM1 with IBM Cognos 8 BI

Packt
16 Dec 2011
4 min read
(For more resources on IBM, see here.) Before proceeding with the actual steps of the recipe, we will take a note of the following integration considerations: The measured Dimension in the TM1 Cube needs to be explicitly identified. The Data Source needs to be created in IBM Cognos Connection which points to the TM1 Cube. New Data Source can also be created from IBM Cognos Framework Manager, but for the sake of simplicity we will be creating that from IBM Cognos Connection itself. The created Data Source is used in IBM Cognos Framework Manager Model to create a Metadata Package and publish to IBM Cognos Connection. Metadata Package can be used to create reports, generate queries, slice and dice, or event management using one of the designer studios available in IBM Cognos BI. We will focus on each of the above steps in this recipe, where we will be using one of the Cubes created as part of demodata TM1 Server application and we will be using the Cube as a Data Source in the IBM Cognos BI layer. Getting ready Ensure that the TM1 Admin Server service is started and demodata TM1 Server is running. We should have IBM Cognos 8 BI Server running and IBM Cognos 8 Framework Manager installed. How to do it... Open the TM1 Architect and right-click on the Sales_Plan Cube. Click on Properties. In the Measures Dimension box, click on Sales_Plan_Measures and then for Time Dimension click on Months. Note that the preceding step is compulsory if we want to use the Cube as a Data Source for the BI layer. We need to explicitly define a measures dimension and a time dimension. Click on OK and minimize the TM1 Architect, keep the server running. Now from the Start menu, open IBM Cognos Framework Manager, which is desktop-based tool used to create metadata models. Create a new project from IBM Cognos 8 Framework Manager. Enter the Project name as Demodata and provide the Location where the model file will be located. Note that each project generates a .cpf file which can be opened in the IBM Cognos Framework Manager. Provide valid user credentials so that IBM Cognos Framework Manager can link to a running IBM Cognos BI Server setup. Users and roles are defined by IBM Cognos BI admin user. Choose English as the authoring language when the Select Language list comes up. This will open the Metadata Wizard - Select Metadata Source. We use the Metadata Wizard to create a new Data Source or point to an existing Data Source. In the Metadata Wizard make sure that Data Sources is selected and click on the Next button. In the next screen, click on the New button to create a new Data Source by the name of TM1_Demodata_Sales_Plan. This will open a New data source wizard, where we need to specify the name of the Data Source. On next screen, it will ask for the Data Source Type for which we will specify TM1 from the drop-down, as we want to create a new Data Source based on the TM1 Cube Sales_Data. On the next screen specify the connection parameters. For Administration Host we can specify a name or localhost, depending on the name of the server. In our case, we have specified name of the server as ankitgar, hence we are using an actual name instead of a localhost. In the case of TM1 sitting on another server within the network, we will provide the IP address or name of the host in UNC format. Test the connection to test whether the connection to the TM1 Cube is successful. Click on Close and proceed. Click on the Finish button to complete the creation of the Data Source. The new Data Source is created on the Cognos 8 Server and now can be used by anyone with valid privileges given by the admin user. It's just a connection to the Sales_Plan TM1 Cube which now can be used to create metadata models and, hence, reports and queries perform the various functions suggested in the preceding sections. Now it will return to Metadata Wizard as shown, with the new Data Source appearing with the list of already created Data Sources. Click on the newly created Data Source and on the Next button. It will display all available Cubes on the DemoData TM1 Server, the machine name being the server name (localhost/ankitgar). Click on the Sales_Plan cube and then on Next.
Read more
  • 0
  • 0
  • 3894
Modal Close icon
Modal Close icon