How-To Tutorials - Data

  (For more resources on Apache, see here.) We're going to get started by downloading Solr, examine its directory structure, and then finally run it. This sets you up for the next section, which tours a running Solr server. Get Solr: You can download Solr from its website: http://lucene.apache.org/ solr/. The last Solr release this article was written for is version 3.4. Solr has had several relatively minor point-releases since 3.1 and it will continue. In general I recommend using the latest release since Solr and Lucene's code are extensively tested. Lucid Imagination also provides a Solr distribution called "LucidWorks for Solr". As of this writing it is Solr 3.2 with some choice patches that came after to ensure its stability and performance. It's completely open source; previous LucidWorks releases were not as they included some extras with use limitations. LucidWorks for Solr is a good choice if maximum stability is your chief concern over newer features. Get Java: The only prerequisite software needed to run Solr is Java 5 (a.k.a. java version 1.5) or later—ideally Java 6. Typing java –version at a command line will tell you exactly which version of Java you are using, if any. Use latest version of Java! The initial release of Java 7 included some serious bugs that were discovered shortly before its release that affect Lucene and Solr. The release of Java 7u1 on October 19th, 2011 resolves these issues. These same bugs occurred with Java 6 under certain JVM switches, and Java 6u29 resolves them. Therefore, I advise you to use the latest Java release. Java is available on all major platforms including Windows, Solaris, Linux, and Apple. Visit http://www.java.com to download the distribution for your platform. Java always comes with the Java Runtime Environment (JRE) and that's all Solr requires. The Java Development Kit (JDK) includes the JRE plus the Java compiler and various diagnostic utility programs. One such useful program is jconsole, and so the JDK distribution is recommended. Solr is a Java-based web application, but you don't need to be particularly familiar with Java in order to use it. Solr's installation directory structure When you unzip Solr after downloading it, you should find a relatively straightforward directory structure: client: Convenient language-specific client APIs for talking to Solr. Ignore the client directory Most client libraries are maintained by other organizations, except for the Java client SolrJ which lies in the dist/ directory. client/ only contains solr-ruby , which has fallen out of favor compared to rsolr —both of which are Ruby Solr clients. contrib: Solr contrib modules. These are extensions to Solr. The final JAR file for each of these contrib modules is actually in dist/; so the actual files here are mainly the dependent JAR files. analysis-extras: A few text analysis components that have large dependencies. There are some "ICU" Unicode classes for multilingual support, a Chinese stemmer, and a Polish stemmer. clustering: A engine for clustering search results. dataimporthandler: The DataImportHandler (DIH) —a very popular contrib module that imports data into Solr from a database and some other sources. extraction: Integration with Apache Tika– a framework for extracting text from common file formats. This module is also called SolrCell and Tika is also used by the DIH's TikaEntityProcessor. uima: Integration with Apache UIMA—a framework for extracting metadata out of text. There are modules that identify proper names in text and identify the language, for example. To learn more, see Solr's wiki: http://wiki.apache.org/solr/SolrUIMA. velocity: Simple Search UI framework based on the Velocity templating language. dist: Solr's WAR and contrib JAR files. The Solr WAR file is the main artifact that embodies Solr as a standalone file deployable to a Java web server. The WAR does not include any contrib JARs. You'll also find the core of Solr as a JAR file, which you might use if you are embedding Solr within an application, and Solr's test framework as a JAR file, which is to assist in testing Solr extensions. You'll also see SolrJ's dependent JAR files here. docs: Documentation—the HTML files and related assets for the public Solr website, to be precise. It includes a good quick tutorial, and of course Solr's API. Even if you don't plan on extending the API, some parts of it are useful as a reference to certain pluggable Solr configuration elements—see the listing for the Java package org.apache.solr.analysis in particular. example: A complete Solr server, serving as an example. It includes the Jetty servlet engine (a Java web server), Solr, some sample data and sample Solr configurations. The interesting child directories are: example/etc: Jetty's configuration. Among other things, here you can change the web port used from the pre-supplied 8983 to 80 (HTTP default). exampledocs: Sample documents to be indexed into the default Solr configuration, along with the post.jar program for sending the documents to Solr. example/solr: The default, sample Solr configuration. This should serve as a good starting point for new Solr applications. It is used in Solr's tutorial. example/webapps: Where Jetty expects to deploy Solr from. A copy of Solr's WAR file is here, which contains Solr's compiled code. Solr's home directory and Solr cores When Solr starts, the very first thing it does is determine where the Solr home directory is. There are various ways to tell Solr where it is, but by default it's the directory named simply solr relative to the current working directory where Solr is started. You will usually see a solr.xml file in the home directory, which is optional but recommended. It mainly lists Solr cores. For simpler configurations like example/solr, there is just one Solr core, which uses Solr's home directory as its core instance directory . A Solr core holds one Lucene index and the supporting Solr configuration for that index. Nearly all interactions with Solr are targeted at a specific core. If you want to index different types of data separately or shard a large index into multiple ones then Solr can host multiple Solr cores on the same Java server. A Solr core's instance directory is laid out like this: conf: Configuration files. The two I mention below are very important, but it will also contain some other .txt and .xml files which are referenced by these two. conf/schema.xml: The schema for the index including field type definitions with associated analyzer chains. conf/solrconfig.xml: The primary Solr configuration file. conf/xslt: Various XSLT files that can be used to transform Solr's XML query responses into formats such as Atom and RSS. conf/velocity: HTML templates and related web assets for rapid UI prototyping using Solritas. The soon to be discussed "browse" UI is implemented with these templates. data: Where Lucene's index data lives. It's binary data, so you won't be doing anything with it except perhaps deleting it occasionally to start anew. lib: Where extra Java JAR files can be placed that Solr will load on startup. This is a good place to put contrib JAR files, and their dependencies. Running Solr Now we're going to start up Jetty and finally see Solr running albeit without any data to query yet. We're about to run Solr directly from the unzipped installation. This is great for exploring Solr and doing local development, but it's not what you would seriously do in a production scenario. In a production scenario you would have a script or other mechanism to start and stop the servlet engine with the operating system—Solr does not include this. And to keep your system organized, you should keep the example directly as exactly what its name implies—an example. So if you want to use the provided Jetty servlet engine in production, a fine choice then copy the example directory elsewhere and name it something else. First go to the example directory, and then run Jetty's start.jar file by typing the following command: >>cd example >>java -jar start.jar The > > notation is the command prompt. These commands will work across *nix and DOS shells. You'll see about a page of output, including references to Solr. When it is finished, you should see this output at the very end of the command prompt: 2008-08-07 14:10:50.516::INFO: Started SocketConnector @ 0.0.0.0:8983 The 0.0.0.0 means it's listening to connections from any host (not just localhost, notwithstanding potential firewalls) and 8983 is the port. If Jetty reports this, then it doesn't necessarily mean that Solr was deployed successfully. You might see an error such as a stack trace in the output if something went wrong. Even if it did go wrong, you should be able to access the web server: http://localhost:8983. Jetty will give you a 404 page but it will include a list of links to deployed web applications, which will just be Solr for this setup. Solr is accessible at: http://localhost:8983/solr, and if you browse to that page, then you should either see details about an error if Solr wasn't loaded correctly, or a simple page with a link to Solr's admin page, which should be http://localhost:8983/solr/admin/. You'll be visiting that link often. To quit Jetty (and many other command line programs for that matter), press Ctrl+C on the keyboard.

(For more resources on topic_name, see here.) We will cover the following topics in this article: The Layout Editor presentation Designing a Layout Export options The Layout Editor First, you have to choose a predefined layout from the Create Report Interface. As you can see in the following screenshot, this interface displays a list of predefined layouts: 3180EN_05_01 image 01 You can add your own predefined layouts to this list and make them available for your later use or even for all the users. After choosing a layout from the Basic Templates or the Shared Templates group, the Layout Editor Interface is displayed. Designing a Layout In the Layout Editor Interface, as shown in the following screenshot, you have tools to perform activities such as: Insert a component: Select the desired component from the Components pane on the left or from the Insert tab of the toolbar and drag-and-drop it into the design area Set component properties: Set the component properties from the Properties pane on the left or from the component-specific tab of the toolbar (only the most commonly used components) Insert a data element: Drag the element from the Data Source pane to the design area Drop Value Here Drop Label Here Drop Series Here 3180EN_05_02 image 02(within bullets) As shown in the preceding screenshot, a precise dropping area is marked. For example, in a chart you have the following marked areas: Set page layout options: In order to set the page layout options, use the Page Layout tab and the Properties pane Save the Layout: Use the activity icons from the toolbar on the right side In the following sections, a few elements will be inserted to complete our report design. You will also see the steps that you need to follow when inserting and setting the properties of these components. Text elements In order to make changes in the settings of Text elements, follow the steps given here: Click on the Insert tab and choose the Text Item component from the toolbar, as shown in the following screenshot: 3180EN_05_03 image 03 Click on the Text tab and set a font color for your text using the Font Color icon from the toolbar, as shown in the following screenshot: 3180EN_05_04 image 04 Set the text margins using the Properties panel, as shown in the following screenshot: 3180EN_05_05 image 05 In this way, we obtain the desired report title, as you can see in the following screenshot: 3180EN_05_06 image 06 In order to insert data elements in our report's components, we will use the following Data Model: 3180EN_05_07 image 07 Charts In order to create Charts, follow the steps given here: Click on the Insert tab and choose the Chart component from the toolbar, as shown in the following screenshot. Select the newly inserted chart and go to the Chart tab on the toolbar to set the chart type (Vertical Bar in this example) and the chart style (Project in this example). Drag the LOAN_PERIOD and the PRICE fields from the Data Source (from the left pane) over the Drop Value Here area of the design view. Drop the TITLE field from the Data Source over the Drop Label Here area: 3180EN_05_08 image 08 Data tables In order to create Data tables , follow the steps given here: Click on the Insert tab and choose the Data Table component from the toolbar, as shown in the following screenshot. Drag the fields LOAN_DATE, LOAN_PERIOD, TITLE, and YEAR from the Data Source over the area marked as Drop a Data Item Here. Select the LOAN_DATE column and in the Properties pane set the Formatting Mask to yyyy-mm-dd. For each column of the table, enter a suitable value for Width, in the Properties pane. For example, the fi rst column has a width of 1.00 inch: 3180EN_05_09 image 09

(For more resources on Oracle 11g, see here.) The Data Model Editor's interface deals with all the components and functionalities needed for the data model to achieve the structure you need. However, the main component is the Data Set. In order to create a data model structure in BIP, you can choose from a variety of data set types, such as: SQL Query MDX Query Oracle BI Analysis View Object Web Service LDAP Query XML file Microsoft Excel file Oracle BI Discoverer HTTP Taking advantage of this variety requires multiple Data Sources of different types to be defined in the BIP. In this article, we will see: How data sources are configured How the data is retrieved from different data sets How data set type characteristics and the links between elements influence the data model structure Administration Let's first see, how you can verify or configure your data sources. You must choose the Administration link found in the upper-right corner of any of the BIP interface pages, as shown in the following screenshot:     The connection to your database can be choosen from the following connection types: Java Database Connectivity (JDBC) Java Naming and Directory Interface (JNDI) Lightweight Directory Access Protocol (LDAP) Online Analytical Processing (OLAP) Available Data Sources To get to your data source, BIP offers two possibilities: YOu can use a connection. In order to use a connection, these are the available connection types: JDBC JNDI LDAP OLAP You can also use a file. In the following sections, the Data Source types&mdashJDBC, JNDI, OLAP Connections, and File&mdashwill be explained in detail. JDBC Connection Let's take the first example. To configure a Data Source to use JDBC, from the Administration page, choose JDBC Connection from the Data Sources types list, as shown in the following screenshot:     You can see the requested parameters for configuring a JDBC connection in the following screenshot: Data Source Name: Enter a name of your choice. Driver Type: Choose a type from the list. The relating parameters are: Database Driver Class: A driver, matching your database type. Connection String: Information containing the computer name on which your database server is running, for example, port, database name, and so on. Username: Enter a database username. Password: Provide the database user's password. The Use System User option allows you to use the operating system's credentials as your credentials. For example, in this case, your MS SQL Database Server uses Windows authentication as the only authentication method. When you have a system administrator in-charge of these configurations, all you have to do is to find which are the available Data Sources and eventually you can check if the connection works. Click on the Test Connection button at the bottom of the page to test the connection:     JNDI Connection JNDI Connection pool is in fact another way to access your JDBC Data Sources. Using a connection pool increases efficiency by maintaining a cache of physical connections that can be reused, allowing multiple clients to share a small number of physical connections. In order to configure a Data Source to use JNDI, from the Administration page, choose JNDI Connection from the Data Sources types list. The following screen will appear:     As you can see in the preceding screenshot, on the Add Data Source page you must enter the following parameters: Data Source Name: Enter a name of your choice JNDI Name: This is the JNDI location for the pool set up in your application server, for example, jdbc/BIP10gSource The users having roles included in Allowed Roles list only will be able to create reports using this Data Source. OLAP Connection Use the OLAP Connection to connect to OLAP databases. BI Publisher supports the following OLAP types: Oracle Hyperion Essbase Microsoft SQL Server 2000 Analysis Services Microsoft SQL Server 2005 Analysis Services SAP BW In order to configure a connection to an OLAP database, from the Administration page, choose OLAP Connection from the Data Sources types list. The following screen will appear:     On the Add Data Source page, the following parameters must be entered: Data Source Name: Enter a name of your choice OLAP Type: Choose a type from the list Connection String: Depending on the supported OLAP databases, the connection string format is as follows: Oracle Hyperion Essbase Format: [server name] Microsoft SQL Server 2000 Analysis Services Format: Data Source=[server];Provider=msolap;Initial Catalog=[catalog] Microsoft SQL Server 2005 Analysis Services Format: Data Source=[server];Provider=msolap.3;Initial Catalog=[catalog] SAP BW Format: ASHOST=[server] SYSNR=[system number] CLIENT=[client] LANG=[language] Username and Password: Used for OLAP database authentication File Another example of a data source type is File. In order to gain access to XML or Excel files, you need a File Data Source. In order to set up this kind of Data Source, only one step is required&mdashenter the path to the Directory in which your files reside. You can see in the following screenshot that demo files Data Source points to the default BIP files directory. The file needs to be accessible from the BI Server (not on your local machine):    

(Read more interesting articles on IBM Cognos here.) Showing images dynamically (Traffic Light report) Let us suppose that we have a report which shows month-on-month difference in sales quantity Getting ready Please note that you will need administrator rights on the Cognos server to complete this recipe. If the server is installed on your personal machine, you will have these rights by default. For this recipe, we need to first create three icons or images for red, yellow, and green. They should be already available on the Cognos server under <Cognos Installation>webcontentsamplesimages folder. If not, then create them using any image editor software or use the images supplied with this book. Once you have the three images which you need to conditionally show on the report, place them on the Cognos server under <Cognos Installation>webcontentsamplesimages folder. (If the folder is not there, create one). Change the IIS security to allow 'Anonymous Read and Browse' accesses. Now open the report that shows the month-on-month running differences. Insert a new 'image' from the insertable objects pane on the list report, as a new column. Now go to Condition Explorer and create a new string variable. Define the expression as: if ([Query1].[Running Difference] > 0)then ('green')else if ([Query1].[Running Difference] < 0)then ('red')else ('yellow') Call the variable Traffic and define three possible values for the same (red, yellow, and green). Now go back to the report page. Select the image. Open its URL Source Variable dialog. Choose the variable Traffic and click OK. From Condition Explorer, choose 'red' condition. Now click on the image again. It will allow you to define the image URL for this condition. Set the URL to: ../samples/images/Red.jpg Similarly, define the URL for 'yellow' and 'green' conditions as ../samples/images/yellow.jpg and ../samples/images/green.jpg respectively. Run the report to test it. How it works... Cognos Report Studio allows you to put the images in the report by specifying the URL of the image. The images can be anywhere on the intranet or internet. They will be displayed properly as long as the URL is accessible from Cognos application server and gateway. In this recipe, we are using a report which already calculates the Running Difference. Hence, we just had to define conditional variable to trap different possible conditions. The Image component allows us to define the URL for different conditions by attaching it to the Traffic variable in step 8. There's more... In this case, though the URL of the image changes dynamically, it is not truly 100% dynamic. There are three static URLs already defined in the report and one is picked up depending on the condition. We can also use a data item or report expression as source of the URL value. In that case, it will be totally dynamic, and based on the values coming from database; Cognos will work out the URL of the image and display it correctly. This is useful when the image filenames and locations are stored in the database. For example, Product Catalogue kind of reports. More info This recipe works fine in HTML, PDF, and Excel formats.We have used relative URLs for the images, so that report can be easily deployed to other environments where Cognos installation might be in a different location. However, we need to ensure that the images are copied in all environments in the folder mentioned in step 2. Handling missing image issue In the previous recipe, we saw how to add images to the report. You will be using that technique in many cases, some involving hundreds of images (For example, Product Catalogue).There will often be a case in which database has a URL or image name, whereas the corresponding image is either missing or inaccessible. In such a case, the web browser shows an error symbol. This looks quite ugly and needs to be handled properly. In this recipe, we will see how to handle this problem gracefully. Getting ready We will use the report prepared in previous recipe. We need to delete the Green.jpg file (or rename it to something else) from the server, in order to create the missing image scenario. How to do it... In the previous recipe, we added an image object and defined its conditional URLs . We need to replace that image with an HTML Item. For that, unlock the report objects and delete the image component. Add an HTML Item in the same column. Select this HTML item and from the Properties pane, set its HTML Source Variable to 'Traffic'. (Please note that we already have this conditional variable in the last recipe). Now define the HTML for different conditions. Start with 'red'. Choose 'red' from conditional explorer and define the HTML as: For 'yellow', define the HTML as: <img src="../samples/images/yellow.jpg" alt="No Change" onError="img2txt(this)"/> For 'green', define HTML as: <img src="../samples/images/green.jpg" alt="Upsell" onError="img2txt(this)"/> Now go back to the No Variable state by double clicking on the green bar, and add another HTML item on the report. Put it just before the list. Define this HTML as: <script>function img2txt(img) {txt = img.alt;img.parentNode.innerHTML=txt;}</script> Now run the report to test it. As you can see, if the image is missing, the report will now handle it gracefully and show some text instead of an error image. How it works... Here we are using our custom code to display the image, instead of using CRS's in-built Image component.We have pulled an HTML item onto the report and defined it to display different images depending on the condition using the <img border="0" /> tag. This tag allows us to define an alternative text and onError event as well. We are using the onError event to call our custom made JavaScript function called img2txt .This function replaces the HTML item with a text which was originally defined as 'alternative text'. Hence, if green.jpg is missing, this function will replace it with a text item Upsell. There's more... As we are using HTML code and JavaScript in this technique, it works in HTML format only. This technique will be useful for a lot of graphical reports (dashboards, scorecards, online product catalogues, and so on). Dynamic links to external website (Google Map example) In this recipe, we will introduce you to the 'Hyperlink' component. A report shows retailers information by products. It shows various fields like Retailer name, Contact information, City, and Postal zone. Business wants to have a link to Google maps that will show retailer's place on the map using the Postal zone information. As the addresses might change in the backend, the technique needs to be dynamic to pick up the latest postal zone. Getting ready Create a simple list report that shows Retailers information by Product lines. How to do it... From the 'Insertable Objects' toolbox, drag a hyperlink object onto the report as a new column. Change its Text property to Map. Set the URL Source Type to Report Expression and define the report expression as follows: 'http://maps.google.com/maps?q=' + [Query1].[City] Run the report to test it. As you can see, there is a link for each retailer record. If you Shift+click on the link, it will open the Google map for corresponding postal zone in a new window. How it works... Here we are using the 'Hyperlink' component of CRS. We can define the URL as any static link. However, for our requirements, we have defined a report expression. This allows us to provide a dynamic link which picks up the latest postal zone from the database. We are passing the postal zone to Google Maps as part of a URL. The hyperlink component works in HTML as well as Excel and PDF formats of report. This object currently does not have the property to define whether the link target should open in a new window or the same window. Just clicking on the link, opens the target in same window; whereas Shift+click opens in a new window. There's more... You can use this technique to call any external website that accepts parameters within a URL. You can pass multiple parameters too.   Alternating drill link In this recipe, we will learn about a limitation of drill link and overcoming it using Render Variable. There is a crosstab report which shows sales quantity by month and order method. We need to provide drill-through facility from the intersection. However, the drill-through target needs to be different, depending on the order method. If order method is e-mail, the drill-through from intersection should go to a report called 'Alternating Drill Link—Drill Report 2'. For all other order methods, it should go to 'Alternating Drill Link—Drill Report 1'. Getting ready Create a crosstab report to serve as the main report. Drag Month (shipment) on rows, Order method on columns and Sales Quantity on the intersection. Create two list reports to serve as drill reports. In the sample provided with this book, we have used two list reports for this. One accepts the Order method and Month. The other accepts only month and is designed to work for the order method 'E-mail'. How to do it... Create a drill through to first drill the report from the crosstab intersection. Now make sure that the report objects are unlocked. Select the intersection text item (which now looks like hyperlink as there is already a drill-through defined). Hold the Ctrl key down and drag the text to its right within a cell. This should create a copy of the text item within that cell and will look like the following: Now select this copy of the text item. Hit the drill-through button to open definitions. Delete the existing drill-through to first report. Create a new drill to a second report. So, now we have two text items in the cell, each going to different drill reports. Create a string type of Conditional Variable. Define it as: if ([Query1].[Order method] = 'E-mail') then ('E-mail')else ('Other')Call it OrderMethod and define the two values to be E-mail and Other. Now go back to the report page. Select the first text item from intersection. Open its Render Variable property . Choose the OrderMethod variable and select to Render for: Other. Similarly, define Render Variable for the second text item, but choose to Render for: E-mail. Run the report to test it. You will see that clicking on the intersection numbers opens first drill report for any order method other than E-mail. Whereas for the numbers under E-mail, the second drill report opens.

  (For more resources on SAP, see here.) Accessing an MDM server involves mounting and unmounting operations which we discuss in the following section. Mounting and unmounting an MDM server MDM server installations are accessible on the console only after they have been mounted. Multiple servers can be mounted within a single console session. We have a choice of mounting only those servers which need to be accessed. The server may or may not be in a running state when mounted in your console session. No password is required to mount a server in your console session even if it is password protected. The MDM Console provides the option of saving the list of currently mounted servers to an MDM Console Settings file. We can load this settings file in the console session and automatically get the previously saved server(s) list mounted as a group. An MDM server can be mounted by multiple MDM Consoles. Once an MDM server is started from any console, it runs on the machine where it is installed and is seen as running by all MDM Consoles that have mounted it. We can mount an MDM server as follows: Right-click on the root node (SAP MDM Servers) in the hierarchy pane tree and choose Mount MDM Server… from the context menu: Alternatively you many select the root node (SAP MDM Servers) and choose MDM Servers | Mount MDM Server… from the main menu: MDM opens the Mount MDM Server dialog prompting for the MDM Server input as displayed next: In the drop-down list input the region displaying the text Enter or select an MDM Server, type the name of the MDM server (typically the name of the machine on which the server is running) you want mounted or select it from the drop-down list. Alternatively (for non-Windows installations), type the name or IP address of any remote machine into the edit box in the Mount MDM Server dialog. Click on the OK button: The drop-down list of MDM Servers shows only those servers that you have previously mounted. If a specific server is not in the list, click on … (Browse) button to open the Select MDM Server dialog (see next) and select the machine on which the MDM Server has been installed from the list of Windows machines visible on the local network.   On successful mounting of the MDM server, you will see a new server node added in the tree structure of the hierarchy pane. Depending on the state of the MDM server, the corresponding icon is displayed in the tree node. The different states and the respective icons of the server node are listed in the following table: Status icon State of MDM server   MDM server is stopped   MDM server is running   MDM server is in one of the following states*: Server Inaccessible Communication Error Start Server Failed Invalid   If the MDM server is inaccessible via the console even after the server has been started, you can try unmounting and remounting the MDM server in the console to restore connectivity. Next we see how to unmount an already mounted MDM server: In the hierarchy tree, right-click on the MDM server that you want to unmount and choose Unmount MDM Server from the context menu. Alternatively, you may unmount the server by first selecting its node in the tree and then clicking on MDM Servers | Unmount MDM Server from the main menu. Unmounting an MDM server is also possible by using the MDM Servers pane (top-right) when the root node (SAP MDM Servers) is selected in the hierarchy tree. Then you can right-click on the MDM Server in the objects pane and select Unmount MDM Server from the context menu. The MDM server node disappears from the tree in the hierarchy pane. Unmounting a running MDM server while it is still running keeps the MDM repositories mounted and loaded even while the unmounted server remains disconnected from the console session. Unmounting and again re-mounting an MDM server within the same MDM Console session requires the MDM server's password to be re-entered to perform any server-level operations (like starting and stopping the server).

  (For more resources on SAP, see here.) Master Data Management Master Data Management seeks to ensure consistent and high-quality master data in a heterogeneous system environment. It includes determination and future avoidance of duplicate and inconsistent data, thus allowing reliable global reporting and operational efficiency of business execution. Benefits of Master Data Management MDM enables an organization to link all of its critical reference or master data shared by several disparate IT systems and groups under one single version of truth. This ensures that multiple inconsistent versions of the same master data are not used in different parts of an organization's operations. By providing front-line employees with more accurate and complete data, instead of inconsistent, incomplete, and often inaccurate data, organizations can realize many added benefits. The business analytical capability of an organization can be increased by utilizing MDM to provide consistent master data across all its operational applications. By achieving this, the master data that flows into a data warehousing system would also be consistent thus allowing the organization to leverage company-wide analytics and reporting. The benefits of MDM increase as the number and diversity of organizational departments, worker roles, and computing applications expand. The implementation of MDM is especially useful when companies merge as it can minimize confusion and optimize the efficiency of the new, larger organization. In addition, companies with a global footprint having an independent region-wise ERP implementation tend to consolidate with one ERP solution for all countries. In this scenario, MDM proves to be the necessary solution by unifying master data from each ERP system into a single unified master data such as Supplier, Material master, or Customer. MDM scenarios SAP NetWeaver MDM scenarios can be easily implemented by the customers to utilize the core functionality of SAP NetWeaver in a phase-wise manner. It includes streamlined IT scenarios as well as product-content management and global data synchronization capabilities. SAP NetWeaver MDM scenarios are broadly classified into the following two categories: IT scenarios Business scenarios IT scenarios IT scenarios are based on the lines of viewing the system comprising of various IT components and the flow of data between these entities. These scenarios can be applied to specific master data objects based on a model-driven approach. The following IT scenarios are listed within SAP NetWeaver MDM: Master Data Consolidation Master Data Harmonization Central Master Data Management Master Data Consolidation Consolidation involves matching, normalizing, cleansing, and storage of master data imported from heterogeneous client systems. SAP NetWeaver MDM offers out-of-the-box models covering globally relevant attributes for the following: Material Product Retail article Supplier Customer Business partner Employee This allows customers to also model additional content on an ad-hoc basis. Organizations can cleanse and consolidate master data on materials, retail articles, suppliers, customers, and employees in an interactive manner within heterogeneous environments. The cleansed and consolidated master data can then be consumed to perform company-wide analytics, for example, global spend analysis. The key capabilities of Master Data Consolidation include: Identification of identical or similar objects spread across the local systems Cleansing of master data objects on a need basis Providing ID mapping for unified, company-wide analytics, and reporting Components required for implementing Master Data Consolidation Masterdata consolidation utilizes the following SAP NetWeaver components: Process Integration (PI) Components of MDM such as: MDM Import Manager (required for map creation and for manual data import) MDM Import Server (required for automated master data import) MDM Data Manager (required for updating master data) MDM Syndication Server (required for automated master data export) MDM Syndicator (for manual master data export) Business Intelligence (BI) (only if data needs to be consumed for consolidated analytics such as global spend analysis) In the following diagram, we illustrate Master Data Consolidation: How this scenario integrates with other scenarios? MasterData Consolidation is the prerequisite for subsequent phases lying within the incremental approach followed by SAP NetWeaver MDM. Subsequent scenarios that follow Master Data Consolidation are Master Data Harmonization and Central Master Data Management. Master Data Harmonization Harmonization involves distribution of cleansed and consolidated high-quality master data within heterogeneous system landscapes. Organizations can make use of the out-of-the-box models offered by SAP NetWeaver MDM to cover globally relevant attributes for the following: Material Product Retail article Supplier Customer Business partner Employee Additional content can also be modeled by the customers on an ad-hoc basis. This scenario includes Master Data Consolidation to ensure high-quality master data within connected business systems in an interactive manner. An added benefit in this scenario is that it allows client-specific control on master data. Organizations can utilize the consolidated and harmonized master data to perform company-wide analytics, for example, global spend analysis. The key capabilities of Master Data Harmonization include: Streamlined processes for data load, consolidation, and distribution High-quality cleansed and de-duplicated master data within a heterogeneous system landscape Components required for implementing Master Data Harmonization MasterData Harmonization utilizes the following SAP NetWeaver components: Process Integration (PI) Components of MDM such as: MDM Import Manager (required for map creation and for manual data import) MDM Import Server (required for automated master data import) MDM Data Manager (required for updating master data) MDM Syndication Server (required for automated master data export) MDM Syndicator (for manual master data export) Business Intelligence (BI) (only if data needs to be consumed for consolidated analytics such as global spend analysis) In the following diagram, we illustrate Master Data Harmonization: How this scenario integrates with other scenarios In SAP NetWeaver's incremental approach, Master Data Harmonization is preceded by the Master Data Consolidation scenario. You can also leverage the consolidation and harmonization capabilities of Business Objects Data Services. Central Master Data Management Allows centralized maintenance and storage of master data with distribution mechanisms that ensure master data is delivered to remote systems that need it. Central Master Data Management puts into place corporate master data governance policies that ensures the overall master data quality of an organization. The differentiating aspect in this scenario with reference to Master Data Harmonization is that master data is created centrally using a rich client. Information is then delivered to target remote systems in an interactive manner. The key capabilities of Central Master Data Management include: Achieving Central Data ownership resulting in dramatic quality improvements Empowers companies to set their own standards for master data management Guarantees client-specific control on master data via local completion SAP NetWeaver MDM offers out-of-the-box models covering globally relevant attributes for the following: Material Product Retail article Supplier Customer Business partner Employee This allows customers to also model additional content on an ad-hoc basis. Components required for implementing Central Master Data Management Central Master Data Management utilizes the following SAP NetWeaver components: Process Integration (PI) Components of MDM such as: MDM Data Manager (required for updating master data) MDM Syndication Server (required for automated master data export) Business Intelligence (BI) (only if data needs to be consumed for consolidated analytics such as global spend analysis) In the following diagram, we illustrate Central Master Data Management: How this scenario integrates with other scenarios In SAP NetWeaver's incremental approach, Master Data Consolidation is a prerequisite for subsequent Central Master Data Management. Business scenarios In addition to IT scenario variants, SAP NetWeaver MDM also features business scenarios. This allows flexibility in adapting SAP NetWeaver Master Data Management to whatever business process flow the customer wants. The following business scenarios are described: Rich Product-Content Management Global Data Synchronization Customer Data Integration Rich Product-Content Management This scenario targets requirements of a centralized product-content management and multi-channel catalog publishing. It allows for importing and exporting product data, centrally managing content, and publishing disparate product data across the enterprise and between trading partners. Organizations can create custom print catalogs, web catalogs, or expose an MDM product repository to a business application (for example SAP SRM) through the Open Catalog Interface (OCI). Consequently, the capabilities of MDM are extended with business processes such as product introduction, cataloging, and publishing. The key capabilities of Rich Product-Content Management are as follows: High-performing load, aggregation, and search of product data Multidimensional search Flexible taxonomy Intelligent imaging and Web/print publishing APIs for seamless, multiplatform integration Scalability (up to millions of products) Organizations can utilize the following key benefits of implementing Rich Product-Content Management: Manage or exchange product data locally and globally Manage internal content Search electronic catalogs Print customized catalogs Syndicate product catalog content through multiple channels such as OCI, Web, and Print Presents role-based interfaces through a portal Process flow This business scenario includes the following processes: The following section discusses each of these processes in detail. Importing product data Start the upload of product master data (flat files) from the specified remote systems, or product information from suppliers (in Excel or TXT format) to MDM. This process has the following prerequisites: The Repository has been set up using the MDM Console and import maps have been created using the MDM Import Manager The inbound port has been defined using the MDM Console The MDM Import Server is running The inbound source data is staged in the inbound port Once the data is delivered to a specific inbound port, it is automatically picked up within a configurable time interval and queued up for import processing. The MDM Import Server maps and matches the imported data to the repository structure as per the import maps defined in the MDM Import Manager. Re-categorizing and enriching product data In this process, you search and merge identical records interactively using the MDM Data Manager. It provides different search patterns such as tree search, keyword search, free search, and so on. After de-duplication you can check if new data has been attached to the correct category and re-categorize it, if necessary. You can also enrich additional information in the MDM Data Manager and custom validations can be applied to check master data updates. Workflows can also be configured which are triggered to support the change processes. Support for adding images as additional information for repository items is available in the MDM Image Manager. Images can be imported into the repository and image variants (example thumbnails) can be created (using the MDM Console) for each image in addition to the original copy. These images are linked to the corresponding product items in the repository using the MDM Data Manager. Providing catalog content Using this process, you can choose to syndicate the product data, apart from print publishing such as Web publishing or exposing the MDM product repository, to a business application (such as, SAP SRM) through the Open Catalog Interface (OCI). The SRM-MDM web catalog provided by SAP contains the web interfaces developed by SAP to access the MDM catalog. The implementation would require a deployment into an additional NetWeaver component called SAP Enterprise Portal. In the case of web publishing, a custom Web Catalog can be developed using the APIs. As a prerequisite, a web application should have been created and deployed on a web server with an open connection to the MDM catalog. An MDM API can be used to perform search, read, and maintain the repository content. On the other hand, if the MDM product repository needs to be exposed to a business application, we can provide the content via the OCI. Using the OCI you can search for products and add the required items to a selection list. The list is then transferred to the shopping cart of the business application and the order is completed. Enabling print publishing Using this process, you can compose and set up a printed product catalog using the MDM Publisher. In order to do this you need to first create a family table using the MDM Console to enable the initial partitioning. As catalog printing is based on category-dependent pages and different product groups in a category have different layouts, further category partitioning can be defined in the MDM Data Manager. We can partition such categories using the field or attribute values to create product families. With the help of the MDM Publisher, you can assign default settings to create a common layout structure for the publication. We can then arrange a specific layout for the given product family such as eliminate redundancies, apply printed version display name, and structure tables. In order to start the publishing activities, a collection of families or non-family based records can be defined as a publication. The publication hierarchy, thus created, is not limited to the repository's taxonomy unlike the family hierarchy. You can freely add, delete, move, and split nodes to create your own structure for the catalog. Spread editor will enable you to concentrate specifically on page layout and design such as creating layout templates for publication. The next step involves using the DTP plug-in to send the publication data from MDM to a Desktop Publishing (DTP) application such as Adobe InDesign. Using the DTP application, some specialized format changes can be done and saved with the publication in MDM. This can be re-used with the next publishing run. Finally, an index for the complete publication is generated using the MDM Indexer.

  (For more resources on NHibernate, see here.)   Prepare our development environment We assume that you have a computer at hand which has Windows Vista, Windows 7, Windows Server 2003 or Windows Server 2008 installed. If you are using an Apple computer, then you can install, for example, Windows 7 as a virtual machine. First, install Microsoft Visual Studio 2010 Professional, Microsoft Visual C# 2010 Express or Microsoft Visual Basic 2010 Express on your system. The Express editions of Visual Studio can be downloaded from http://www.microsoft.com/express/windows. Note that NHibernate 3.x can also be used with the 2008 editions of Microsoft Visual Studio, but not with any older versions. NHibernate 3.x is based on the .NET framework version 3.5, and thus only works with IDEs that support this or a higher version of the .NET framework. Additionally, note that if you don't want to use Visual Studio, then there are at least two other free OSS options available to you: MonoDevelop is an IDE primarily designed for C# and other .NET languages. MonoDevelop makes it easy for developers to port .NET applications created with Visual Studio to Linux and to maintain a single code base for all platforms. MonoDevelop 2.4 or higher can be downloaded from http://monodevelop.com/download. SharpDevelop is a free IDE for C#, VB.NET, and Boo projects on Microsoft's .NET platform. It is open source. SharpDevelop 3.2 or higher can be downloaded from http://sharpdevelop.net/OpenSource/SD/Default.aspx. Furthermore, note that NHibernate also works on Mono: http://www.mono-project.com. Next, we need a relational database to play with. NHibernate supports all major relational databases like Oracle, MS SQL Server, MySQL, and so on. We will use MS SQL Server as our Relational Database Management System (RDBMS). Microsoft SQL Server is the most used RDBMS in conjunction with NHibernate and, in general, with .NET projects. The SQL Server driver for NHibernate is one of the most tested drivers in NHibernate's suite of unit tests, and when specific new features come out, it is likely that they will be first supported by this driver. Install the free Microsoft SQL Server 2008 R2 Express on your system if you have not already done so during the install of Visual Studio. You can download the express edition of MS SQL Server from here http://www.microsoft.com/express/Database/. For our samples, it really doesn't matter which version you download: the 32-bit or the 64-bit version. Just take the one that matches best with the bitness of your operating system. Make sure that you install SQL Server with the default instance name of SQL Express. Make sure you also download and install the free SQL Server Management Studio Express (SSMS) from the following link: http://www.microsoft.com/download/en/details.aspx?id=22985 Now, we are ready to tackle NHibernate. We can download NHibernate 3.1.0 GA from Source Forge http://sourceforge.net/projects/nhibernate/. The download consists of a single ZIP file containing the following content, as shown in the screenshot: The binaries that are always needed when developing an NHibernate based application can be found in the Required_Bins folder. Opening this folder, we find the files as shown in the following screenshot: Note that if you are downloading version 3.1 or newer of NHibernate, you will no longer find the two DLLs, Antlr3.Runtime.dll and Remotion.Data.Linq.dll, in the ZIP file that were present in version 3.0. The reason is that they have been IL merged into the NHibernate.dll. If we want to use lazy loading with NHibernate (and we surely will), then we also have to use some additional files which can be found in the Required_For_LazyLoading folder. Lazy loading is a technique that is used to load certain parts of the data only when really needed, which is when the code accesses it. There are three different options at hand. We want to choose Castle. The corresponding folder contains these files, as shown in the following screenshot: As we are also using Fluent NHibernate, we want to download the corresponding binaries too. Go grab the binaries from the Fluent NHibernate website and copy them to the appropriate location on your system. In either case, there is no installer available or needed. We just have to copy a bunch of files to a folder we define. Please download Fluent NHibernate, which also contains the binaries for NHibernate, from here (http://fluentnhibernate.org/downloads), as shown in the following screenshot. Make sure you download the binaries for NHibernate 3.1 and not an earlier version. Save the ZIP file you just downloaded to a location where you can easily find it for later usage. The ZIP file contains the files shown in the following screenshot: The only additional files regarding the direct NHibernate download are the FluentNHibernate.* files. On the other hand, we do not have the XSD schema files (nhibernate-configuration.xsd and nhibernate-mapping.xsd) included in this package and we'll want to copy those from the NHibernate package when implementing our sample.   Defining a model After we have successfully downloaded the necessary NHibernate and Fluent NHibernate files, we are ready to start implementing our first application using NHibernate. Let's first model the problem domain we want to create the application for. The domain for which we want to build our application is a product inventory system. With the application, we want to be able to manage a list of products for a small grocery store. The products shall be grouped by category. A category consists of a name and a short description. The product on the other hand has a name, a short description, a category, a unit price, a reorder level, and a flag to determine whether it is discontinued or not. To uniquely identify each category and product, they each have an ID. If we draw a class diagram of the model just described, then it would look similar to the following screenshot: Unfortunately, the class designer used to create the preceding diagram is only available in the professional version of Visual Studio and not in the free Express editions.   Time for action – Creating the product inventory model Let's implement the model for our simple product inventory system. First, we want to define a location on our system, where we will put all our code that we create. Create a folder called NH3BeginnersGuide on your file system. Inside this new folder, create another folder called lib. This is the place where we will put all the assemblies needed to develop an application using NHibernate and Fluent NHibernate. Locate the ZIP file containing the Fluent NHibernate files that you downloaded in the first section of this article. Extract all files to the lib folder created in the preceding step. Open Visual Studio and create a new project. Choose WPF Application as the project template. Call the project Chapter2. Make sure that the solution you create will be saved in the folder NH3BeginnersGuide you created in the preceding step. When using VS 2008 Pro, you can do this when creating the new project. If, on the other hand, you use the Express edition of Visual Studio, then you choose the location when you first save your project. (Move the mouse over the image to enlarge.) Add a new class to the project and call it Category. To this class, add a virtual (auto-) property called Id, which is of the int type. Also, add two other virtual properties of the string type, called Name and Description. The code should look similar to the following code snippet: namespace Chapter2{ public class Category { public virtual int Id { get; set; } public virtual string Name { get; set; } public virtual string Description { get; set; } }} Downloading the example code You can download the example code files here. Add another class to the project and call it Product. To this class, add the properties, as shown in the following code snippet. The type of the respective property is given in parenthesis: Id (int), Name (string), Description (string), Category (Category), UnitPrice (decimal), ReorderLevel (int), and Discontinued (bool). The resulting code should look similar to the following code snippet: namespace Chapter2{ public class Product { public virtual int Id { get; set; } public virtual string Name { get; set; } public virtual string Description { get; set; } public virtual Category Category { get; set; } public virtual decimal UnitPrice { get; set; } public virtual int ReorderLevel { get; set; } public virtual bool Discontinued { get; set; } }} What just happened? We have implemented the two classes Category and Product, which define our simple domain model. Each attribute of the entity is implemented as a virtual property of the class. To limit the amount of code necessary to define the entities, we use auto properties. Note that the properties are all declared as virtual. This is needed as NHibernate uses lazy loading by default.  

Oracle E-Business Suite 12 Financials Cookbook Take the hard work out of your daily interactions with E-Business Suite financials by using the 50+ recipes from this cookbook. Entering bank statements Bank statements are downloaded from the bank to a local directory. Once the file is received, the bank account balance and statement information can be loaded into the bank statement open interface tables, using the bank statement loader program or a custom loader program. The files can also be loaded automatically using an interface program or using the XML Gateway. Bank statements can also be entered manually. In this recipe, we will look at how to enter bank statements. Getting ready The bank statement shown next has been loaded into the open interface table: Let's review the transactions in the open interface: Select the Cash Management responsibility. Navigate to Bank Statements | Bank Statement Interface Lines. Select 95-6891-3074 in the Account field. Click on the Lines button to view the transactions in the interface tables. How to do it... Let's list the steps required to automatically enter the bank statements from the import and AutoReconciliation program: Select the Cash Management responsibility. Navigate to Other | Programs | Run, or select View | Requests from the menu. Click on the Submit a New Request button. Select Single Request from the Options. Click on the OK button. In the Submit Request form, select Bank Statement Import & AutoReconciliation from the list of values. Please note that we could run the Bank Statement Import program, to run only the import. Select the Parameters field, and select Kings Cross as the Bank Branch Name, select 95-6891-3074 as the Bank Account Number, and select 20110314-0001 as the parameter for the Statement Number From and the Statement Number To fields. Accept the default values for the remaining fields. Click on the OK button. We can schedule the program to run periodically, for example, every day. Click on the Submit button to submit the request. Let's review the imported bank statements: Navigate to Bank Statement | Bank Statements and Reconciliation. The imported statement is displayed. Click on the Review button. (Move the mouse over the image to enlarge it.) In the Bank Statement window, select the Lines button. The imported lines are displayed. How it works... Bank statements can be imported automatically, using a SQL*Loader script against the bank file to populate the bank statement open interface. The bank statement information is then imported into the Bank Statement windows using the Bank Statement Import program. There's more... Now, let's look at how to enter statements manually. Entering bank statements manually Let's enter the bank statement for the 15th of March manually. The lines on the statement are as follows: Payment of 213.80. Receipt of 3,389.89 from A.C. Networks. Credit of 7,500.00 for Non Sufficient Funds for the receipt from Advantage Corp. Bank Transfer payment of 1,000.00. Select the Cash Management responsibility. Navigate to Bank Statement | Bank Statements and Reconciliation. (Move the mouse over the image to enlarge it.) In the Reconcile Bank Statements window, click on the New button. In the Account Number field, enter 95-6891-3074, the other details are automatically entered. In the Date field enter 15-MAR-2011. In the Statement Number field enter 20110314-0002. In the Control Totals region, let's enter control totals based on our bank statement. The Opening Balance of 125,727.21 is entered based on the previous opening balance. In the Receipts field, enter 3,389.89 and 1 in the Lines field. In the Payments field, enter 8,713.80 and 3 in the Lines field. The Closing Balance of 98,495.56 is entered automatically. Let's enter the bank statement lines: Click on the Lines button. (Move the mouse over the image to enlarge it.) In the Bank Statements Lines form, enter 1 in the Line field. Select Payment as the Type. Enter 100 as the code. In the Transaction Date field, enter 15-MAR-2011. In the Amount field, enter 213.80. Select the next line, and enter 2 in the Line field. Select Receipt as the Type. Enter 200 as the code. In the Transaction Date field, enter 15-MAR-2011. In the Amount field, enter 3,389.89. Select the Reference tab, and enter A.C. Networks. Select the next line, and enter 3 in the Line field. Select NSF as the Type. Enter 500 as the code. In the Transaction Date field, enter 15-MAR-2011. In the Amount field, enter 7,500.00. Select the Reference tab, and enter Advantage Corp. Select the next line, and enter 4 in the Line field. Select Payment as the Type. Enter 140 as the code. In the Transaction Date field, enter 15-MAR-2011. In the Amount field, enter 1,000.00. Save the record.

  Oracle E-Business Suite 12 Financials Cookbook Take the hard work out of your daily interactions with E-Business Suite financials by using the 50+ recipes from this cookbook   Introduction Oracle E-business suite The liquidity of an organization is managed in Oracle Cash Management; this includes the reconciliation of the cashbook to the bank statements, and forecasting future cash requirements. In this article, we will look at how to create bank accounts and cash forecasts. Cash management integrates with Payables, Receivables, Payroll, Treasury, and General Ledger. Let's start by looking at the cash management process: The Bank generates statements. The statements are sent to the organization electronically or by post. The Treasury Administrator loads and verifies the bank statement into cash management. The statements can also be manually entered into cash management. The loaded statements are reconciled to the cash book transactions. The results are reviewed, and amended if required. The Treasury Administrator creates the journals for transactions in the General Ledger. Creating bank accounts Oracle Cash Management provides us with the functionality to create bank accounts. In this recipe, we will create a bank account for a bank called Shepherd Bank, for one of their branches called Kings Cross branch. Getting ready Log in to Oracle E-Business Suite R12 with the username and password assigned to you by the system administrator. If you are working on the Vision demonstration database, you can use OPERATIONS/WELCOME as the USERNAME/PASSWORD. We also need to create a bank before we can create the bank account. Let's look at how to create a bank and the branch: Select the Cash Management responsibility. Navigate to Setup | Banks | Banks.(Move the mouse over the image to enlarge it.) In the Banks tab, click on the Create button. Select the Create new bank option. In the Country field, enter United States. In the Bank Name field, enter Shepherds Bank. In the Bank Number field, enter JN316. Click on the Finish button. Let's create the branch and the address: (Move the mouse over the image to enlarge it.) Click the Create Branch icon: The Country and the Bank Name are automatically entered. Click on the Continue button.(Move the mouse over the image to enlarge it.) In the Branch Name field, enter Kings Cross. Select ABA as the Branch Type. Click on the Save and Next button to create the Branch address.(Move the mouse over the image to enlarge it.) In the Branch Address form, click on the create button. In the Country field, enter United States. In the Address Line 1 field, enter 4234 Red Eagle Road. In the City field, enter Sacred Heart. In the County field, enter Renville. In the State field, enter MN. In the Postal Code field, enter 56285. Ensure that the Status field is Active. Click on the Apply button. Click on the Finish button.

Oracle E-Business Suite 12 Financials Cookbook Adjusting Items in Inventory Item quantities can be adjusted in Inventory. In this recipe, we will use miscellaneous transactions to adjust Items. Let's try to enter transactions on some of the controls we have set up. We will try and enter a Miscellaneous Transaction for five paper widgets into Inventory. How to do it... Navigate to Transactions | Miscellaneous Transactions. In the Type field, select Miscellaneous receipt from the list of values. Click on the Transaction Lines button to enter the receipt. Search for the PRD20001 Item in the Item field. Select the Subinventory list of values and the field should be automatically populated with ACME-FG. In the Locator field, enter A1.1.2. The system should display an Error message to indicate that an invalid locator has been entered. Click on OK and enter A1.1.1–the system should accept this value. Enter a value of 5 in the Quantity field. In the account field, enter 01-000-1410-0000-000. This is the account that will be charged for the Inventory transaction. Select the Lot / Serial button. Enter the Lot number–LN10001. The expiration date is generated based on the setting in the Item definition. Enter the quantity of 5. Click on the Serial button. Enter SN10001 in the Start Serial Number field and press Tab on the keyboard. The SN10005 should be automatically populated in the End Serial Number field. Click on the Done button. Click on the Done button again in the next screen. Save the record. There's more... Let's search for the Items in Inventory. Searching for Items We will use the material workbench to search for the Items: Navigate to On-hand | Availability | On-hand Quantity. Enter PRD20001 in the Item / Revision field. Click on the Find button. Expand the Organizations tree to show LN10001. Review the Item details. Close the form. Classifying Items Items are grouped into logical classifications through categories. Categories can be further grouped into category sets. A default category set can be assigned to a functional area. When an Item is created, it is automatically added to the default category set. The groupings are mainly used for reporting purposes. Let's look at how to classify Items using categories. How to do it... Let's list the steps required to create category codes: Navigate to Setup | Items | Categories | Category Codes. Click on the New button to enter the Category. In the Structure Name, select Item Categories. In the Category field, enter BOOKS.MISC. In the Description field, enter Other Books. Save the record. Let's now create the Category Set, add the Category Codes to a new set called ACME Books, and assign it to the PRD20001 Item: Navigate to Setup | Items | Categories | Category Sets. Click on the New button to enter the category set. In the Name field, enter ACME Books. Enter a description, for example, ACME Paper Books. Select Item Categories for Flex Structure. Select Controlled At as the Org level. Enter BOOKS.MISC as the Default Category. Select the checkbox Allow Multiple Item Category Assignments. Add the following Category Codes to the list: BOOKS.MISC BOOKS.NEW BOOKS.USED Save the record. Let's now assign the categories to the PRD20001 Item: Navigate to Items | Organization Items. From the Menu, select Tools and then Categories. Select ACME Books in the Category Set. Enter BOOKS.NEW in the Category field. Save the record. How it works... The structure of the Item category is defined in the Item Flexfield structure and the values are held in the individual Value Sets. The combination of the individual values forms the category code. For example, the structure we used previously is made of two segments, defined in the Flexfield structure. The segments are Family and Class. BOOKS is a value in Family and MISC, NEW, and USED are individual values in Class.

  Oracle E-Business Suite 12 Financials Cookbook Take the hard work out of your daily interactions with E-Business Suite financials by using the 50+ recipes from this cookbook         Read more about this book       Oracle E-Business Suite 12 Financials is a solution that provides out-of-the-box features to meet global financial reporting and tax requirements with one accounting, tax, banking, and payments model, and makes it easy to operate shared services across businesses and regions. In this article by Yemi Onigbode, author of Oracle E-Business Suite 12 Financials Cookbook, we will start with recipes for creating Items. We will cover: Creating Items Exploring Item attributes Creating Item templates Exploring Item controls (For more resources on Oracle, see here.) Introduction An organization's operations include the buying and selling of products and services. Items can represent the products and services that are purchased and sold in an organization. Let's start by looking at the Item creation process. The following diagram details the process for creating Items: The Item Requester (the person who requests an Item) completes an Item Creation Form, which should contain information such as: Costing information Pricing Information Item and Product Categories Details of some of the Item attributes The inventory organization details Once complete, a message is sent to the Master Data Manager (the person who maintains the master data) to create the Item. The message could be sent by fax, e-mail, and so on. The Master Data Manager reviews the form and enters the details of the Item into Oracle E-Business Suite by creating the Item. Once complete, a message is sent to the Item Requester. The Item Requester reviews the Item setup on the system. Let's look at how Items are created and explore the underlying concepts concerning the creation of Items. Creating Items Oracle Inventory provides us with the functionality to create Items. Sets of attributes are assigned to an Item. The attributes define the characteristics of the Item. A group of attributes values defines a template, and a template can be assigned to an Item to automatically define the set of attribute values. An Item template defines the Item Type. For example, a Finished Good template will identify certain characteristics that define the Item as a finished good, with attributes such as "Inventory Item" and "Stockable" with a value of "Yes". Let's look at how to create an Item in Oracle Inventory. We will also assign a Finished Good template to the Item. Getting ready Log in to Oracle E-Business Suite R12 with the username and password assigned to you by the System Administrator. If you are working on the Vision demonstration database, you can use OPERATIONS/WELCOME as the USERNAME/PASSWORD: Select the Inventory Responsibility. Select the V1 Inventory Organization. How to do it... Let's list the steps required to create an Item: Navigate to Items | Master Items. Please note that Items are defined in the Master Organization. Enter the Item code, for example, PRD20001. Enter a description for the Item: Select Copy From from the tools menu (or press Alt+T). We are going to copy the attributes from the Finished Good template: We can also copy attributes from an existing Item. Enter Finished Good and click on the Apply button (or press Alt+A) and click on the Done button. Save the Item definition by clicking on the Save icon (or press Ctrl+S). How it works... Items contain attributes and attributes contain information about an Item. Attributes can be controlled centrally at the Master Organization level or at the Inventory Organization level. There's more... Once the Item is created, we need to assign it to a category and an inventory organization. Assigning Items to inventory organizations For us to be able to perform transactions with the Item in the inventory, we need to assign the Item to an inventory organization. We can also use the organization Item form to change the attributes at the organization level. For example, an Item may be classified as raw materials in one organization and finished goods in another organization. From the Tools menu, select Organization Assignment. Select the inventory organization for the Item. For example, A1–ACME Corporation. Click on the Assigned checkbox. Save the assignment. Assigning Items to categories When an Item is created, it is assigned to a default category. However, you may want to perform transactions with the Item in more than one functional area, such as Inventory, Purchasing, Cost Management, Service, Engineering, and so on. You need to assign the Item to the relevant functional area. A category within a functional area is a logical classification of Items with similar characteristics. From the Tools menu, select Categories. Select the Categories Set, Control Level, and the Category combination to assign to the Item: Save the assignment. Exploring Item attributes There are more than 250 Item attributes grouped into 17 main attribute groups. In this recipe, we will explore the main groups that are used within the financial modules. How to do it... Let's explore some Item attributes: Search for the Finished Good Item by navigating to Items | Master Items: Click on the Find icon. You then enter the Item code and click on the Find button to search for the Item. Select the tabs to review each of the attributes group: In the Main tab, check that the Item Status is Active. We can also enter a long description in the Long Description field. The default value of the primary Unit of Measure (UOM) can be defined in the INV: Default Primary Unit of Measure profile option. The value can be overwritten when creating the Item. The Primary UOM is the default UOM used in other modules. For example, in Receivables it is used for invoices and credit memos. In the Inventory tab, check that the following are enabled: Inventory Item: It enables the Item to be transacted in Inventory. The default Inventory Item category is automatically assigned to the Item, if enabled. Stockable: It enables the Item to be stocked in Inventory. Transactable: Order Management uses this flag to determine how returns are transacted in Inventory. Reservable: It enables the reservation of Items during transactions. For example, during order entry in Order Management. In the Costing tab, check that the following are enabled: Costing: Enables the accounting for Item costs. It can be overridden in the Cost Management module, if average costing is used. Cost of Goods Sold Account: The cost of goods sold account is entered. This is a general ledger account. The value defaults from the Organization parameters. In the Purchasing tab, enter a Default Buyer for the purchase orders, a List Price, and an Expense Account. Check that the following are enabled: Purchased: It enables us to purchase and receive the Item. Purchasable: It enables us to create a Purchase Order for the Item. Allow Description Update: It enables us to change the description of the Item when raising the Purchase Order. RFQ Required: Set this value to Yes to enable us to require a quotation for this Item. Taxable: Set this value to Yes with the Input Tax Classification Code as VAT–15%. This can be used with the default rules in E-Tax. Invoice Matching: Receipt Required–Yes. This is to allow for three-way matching. In the Receiving tab, review the controls. In the Order Management tab, check that the following are enabled: Customer Ordered: This enables us to define prices for an Item assigned to a price list. Customer Orders Enabled: This enables us to sell the Item. Shippable: This enables us to ship the Item to the Customer. Internal Ordered: This enables us to order an Item via internal requisitions. Internal Orders Enabled: This enables us to temporarily exclude an Item from internal requisitions. OE Transactable: This is used for demand management of an Item. In the Invoicing tab, enter values for the Accounting Rule, Invoicing Rule, Output Tax Classification Code, and Payment Terms. Enter the Sales Account code and check that the Invoiceable Item and Invoice Enabled checkboxes are enabled.

MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook More than 80 recipes for enriching your Business Intelligence solutions with high-performance MDX calculations and flexible MDX queries in this book and eBook Parts of an MDX query This section contains the brief explanation of the basic elements of MDX queries: members, sets, tuples, axes, properties, and so on. Regular members Regular dimension members are members sourced from the underlying dimension tables. They are the building blocks of dimensions, fully supported in any type of drill operations, drillthrough, scopes, subqueries, and probably all SSAS front-ends. They can have children and be organized in multilevel user hierarchies. Some of the regular member's properties can be dynamically changed using scopes in MDX script or cell calculations in queries - color, format, font, and so on. Measures are a type of regular members, found on the Measures dimension/hierarchy. The other type of members is calculated members. Calculated members Calculated members are artificial members created in a query, session, or MDX script. They do not exist in the underlying dimension table and as such are not supported in drillthrough and scopes. In subqueries, they are only supported if the connection string includes one of these settings: Subqueries = 1 or Subqueries = 2. See here for examples: http://tinyurl.com/ChrisSubquerySettings They also have a limited set of properties compared to regular members and worse support than regular members in some SSAS front-ends. An often practiced workaround is creating dummy regular members in a dimension table and then using MDX script assignments to provide the calculation for them. They are referred to as "Dummy" because they never occur in the fact table which also explains the need for assignments. Tuples A tuple is a coordinate in the multidimensional cube. That coordinate can be huge and is often such. For example, a cube with 10 dimensions each having 5 attributes is a 51 dimensional object (measures being that extra one). To fully define a coordinate we would have to reference every single attribute in that cube. Fortunately, in order to simplify their usage, tuples are allowed to be written using a part of the full coordinate only. The rest of the coordinate inside the tuple is implicitly evaluated by SSAS engine, either using the current members (for unrelated hierarchies) or through the mechanism known as strong relationships (for related hierarchies). It's worth mentioning that the initial current members are cube's default members. Any subsequent current members are derived from the current context of the query or the calculation. Evaluation of implicit members can sometimes lead to unexpected problems. We can prevent those problems by explicitly specifying all the hierarchies we want to have control over and thereby not letting the implicit evaluation to occur for those hierarchies. Contrary to members and sets, tuples are not an object that can be defined in the WITH part of the query or in MDX script. They are non-persistent. Tuples can be found in sets, during iteration or in calculations. They are often used to set or overwrite the current context, in other words, to jump out of the current context and get the value in another coordinate of the cube. Another important aspect of tuples is their dimensionality. When building a set from tuples, two or more tuples can be combined only if they are built from the same hierarchies, specified in the exact same order. That's their dimensionality. You should know that rearranging the order of hierarchies in a tuple doesn't change its value. Therefore, this can be the first step we can do to make the tuples compatible. The other thing is adding the current members of hierarchies present only in the other tuple, to match the other tuple's dimensionality. Named sets A named set is a user-defined collection of members, more precisely, tuples. Named sets are found in queries, sessions, and MDX scripts. Query-based named sets are equivalent to dynamic sets in MDX script. They both react to the context of subquery and slicer. Contrary to them, static sets are constant, independent of any context. Only the sets that have the same dimensionality can be combined together because what we really combine are the tuples they are built from. It is possible to extract one or more hierarchies from the set. It is also possible to expand the set by crossjoining it with hierarchies not present in its tuples. These processes are known as reducing and increasing the dimensionality of a set. Set alias Set aliases can be defined in calculations only, as a part of that calculation and not in the WITH part of the query as a named set. This is done by identifying a part of the calculation that represents a set and giving a name to that expression inside the calculation, using the AS keyword. This way that set can be used in other parts of the calculation or even other calculations of the query or MDX script. Set aliases enable true dynamic evaluation of sets in a query because they can be evaluated for each cell if used inside a calculated measure. The positive effect is that they are cached, calculated only once and used many times in the calculation or query. The downside is that they prevent block-computation mode because the above mentioned evaluation is performed for each cell individually. In short, set aliases can be used in long calculations, where the same set appears multiple times or when that set needs to be truly dynamic. At the same time, they are to be avoided in iterations of any kind. Axis An axis is a part of the query where a set is projected at. A query can have up to 128 axes although most queries have 1 or 2 axes. A query with no axis is also a valid query but almost never used. The important thing to remember is that axes are evaluated independently. SSAS engine knows in which order to calculate them if there is a dependency between them. One way to create such a dependency is to refer to the current member of a hierarchy on the other axis. The other option would be to use the Axis() function. Some SSAS front-ends generate MDX queries that break the axes dependencies established through calculations. The workaround calculations can be very hard if not impossible. Slicer The slicer, also known as the filter axis or the WHERE clause, is a part of the query which sets the context for the evaluation of members and sets on axes and in the WITH part of the query. The slicer, which can be anything from a single tuple up to the multidimensional set, interacts with sets on axes. A single member of a hierarchy in slicer forces the coordinate and reduces the related sets on axes by removing all non-existing combinations. Multiple members of the same hierarchy are not that strong. In their case, individual members in sets on axes overwrite the context of the slicer during their evaluation. Finally, the context established by the slicer can be overwritten in the calculations using tuples. Subquery The subquery, also known as the subselect, is a part of the query which executes first and determines the cube space to be used in the query. Unlike slicer, the subquery doesn't set the coordinate for the query. In other words, current members of all hierarchies (related, unrelated, and even the same hierarchy used in the subquery) remain the same. What the subquery does is it applies the VisualTotals operation on members of hierarchies used in the subquery. The VisualTotals operation changes each member's value with the aggregation value of its children, but only those present in the subquery. Because the slicer and the subquery have different behaviors, one should not be used as a replacement for the other. Whenever you need to set the context for the whole query, use the slicer. That will adjust the total for all hierarchies in the cube. If you only need to adjust the total for some hierarchies in the cube and not for the others, subquery is the way to go; specify those hierarchies in the subquery. This is also an option if you need to prevent any attribute interaction between your subquery and the query. The areas where the subquery is particularly good at are grouping of non-granular attributes, advanced set logic, and restricting members on hierarchies. Cell properties Cell properties are properties that can be used to get specific behaviors for cells. For example: colors, font sizes, types and styles, and so on. Unless explicitly asked for, only the Cell_Ordinal, Value, and Formatted_Value properties are returned by an MDX query. Dimension properties Dimension properties are a set of member properties that return extra information about members on axes. Intrinsic member properties are Key, Name, and Value; the others are those defined by the user for a particular hierarchy in the Dimension Designer. In client tools, dimension properties are often shown in the grid next to the attribute they are bound to or in the hint over that attribute.  

Oracle Information Integration, Migration, and Consolidation The definitive book and eBook guide to Oracle information integration and migration in a heterogeneous world Data services Data services are at the leading edge of data integration. Traditional data integration involves moving data to a central repository or accessing data virtually through SQL-based interfaces. Data services are a means of making data a 'first class' citizen in your SOA. Recently, the idea of SOA-enabled data services has taken off in the IT industry. This is not any different than accessing data using SQL, JDBC, or ODBC. What is new is that your service-based architecture can now view any database access service as a web service. Service Component Architecture (SCA) plays a big role in data services as now data services created and deployed using Oracle BPEL, Oracle ESB, and other Oracle SOA products can be part of an end-to-end data services platform. No longer do data services deployed in one of the SOA products have to be deployed in another Oracle SOA product. SCA makes it possible to call a BPEL component from Oracle Service Bus and vice versa. Oracle Data Integration Suite Oracle Data Integration (ODI)Suite includes the Oracle Service Bus to publish and subscribe messaging capabilities. Process orchestration capabilities are provided by Oracle BPEL Process Manager, and can be configured to support rule-based, event-based, and data-based delivery services. The Oracle Data Quality for Data Integrator, Oracle Data Profiling products, and Oracle Hyperion Data Relationship Manager provide best-in-class capabilities for data governance, change management hierarchical data management, and provides the foundation for reference data management of any kind. ODI Suite allows you to create data services that can be used in your SCA environment. These data services can be created in ODI, Oracle BPEL or the Oracle Service Bus. You can surround your SCA data services with Oracle Data Quality and Hyperion Data Relationship to cleanse your data and provide master data management. ODI Suite effectively serves two purposes: Bundle Oracle data integration solutions as most customers will need ODI, Oracle BPEL, Oracle Service Bus, and data quality and profiling in order to build a complete data services solution Compete with similar offerings from IBM (InfoSphere Information Server) and Microsoft (BizTalk 2010) that offer complete EII solutions in one offering The ODI Suite data service source and target data sources along with development languages and tools supported are: Data sourceData targetDevelopment languages and toolsERPs, CRMs, B2B systems, flat files, XML data, LDAP, JDBC, ODBCAny data sourceSQL, Java, GUI The most likely instances or use cases when ODI Suite would be the Oracle product or tool selected are: SCA-based data services An end-to-end EII and data migration solution Data services can be used to expose any data source as a service. Once a data service is created, it is accessible and consumable by any web service-enabled product. In the case of Oracle, this is the entire set of products in the Oracle Fusion Middleware Suite. Data consolidation The mainframe was the ultimate solution when it came to data consolidation. All data in an enterprise resided in one or several mainframes that were physically located in a data center. The rise of the hardware and software appliance has created a 'what is old is new again' situation; a hardware and software solution that is sold as one product. Oracle has released the Oracle Exadata appliance and IBM acquired the pure database warehouse appliance company Netezza, HP, and Microsoft announced work on an SQL Server database appliance, and even companies like SAP, EMC, and CICSO are talking about the benefits of database appliances. The difference is (and it is a big difference) that the present architecture is based upon open standards hardware platforms, operating systems, client devices, network protocols, interfaces, and databases. So, you now have a database appliance that is not based upon proprietary operating systems, hardware, network components, software, and data disks. Another very important difference is that enterprise software COTS packages, management tools, and other software infrastructure tools will work across any of these appliance solutions. One of the challenges for customers that run their business on the mainframe is that they are 'locked into' vendor- specific sorting, reporting, job scheduling, system management, and other products usually only offered from IBM, CA, BMC, or Compuware. Mainframe customers also suffer from a lack of choice when it comes to COTS applications. Since appliances are based upon open systems, there is an incredibly large software ecosystem. Oracle Exadata Oracle Exadata is the only database appliance that runs both data warehouse and OLTP applications. Oracle Exadata is an appliance that includes every component an IT organization needs to process information—from a grid database down to the power supply. It is a hardware and software solution that can be up and running in an enterprise in weeks instead of months for typical IT database solutions. Exadata provides high speed data access using a combination of hardware and a database engine that runs at the storage tier. Typical database solutions have to use indexes to retrieve data from storage and then pull large volumes of data into the core database engine, which churns through millions of rows of data to send a handful of row results to the client. Exadata eliminates the need for indexes and data engine processing by placing a lightweight database engine at the storage tier. Therefore, the database engine is only provided with the end result and does not have to utilize complicated indexing schemes, large amounts of CPU, and memory to produce the end results set. Exadata's capabilities to run large OLTP and data warehouse applications, or a large number of smaller OLTP and data warehouse applications on one machine make it a great platform for data consolidation. The first release of Oracle Exadata was based upon HP hardware and was for data warehouses only. The second release came out shortly before Oracle acquired Sun. This release was based upon Sun hardware, but ironically not on Sun Sparc or Solaris (Solaris is now an OS option). The Exadata source and target data sources along with development languages and tools supported are: Data sourceData targetDevelopment languages and toolsAny (depending upon the data source this may involve an intensive migration effort)Oracle ExadataSQL, PL/SQL, Java The most likely instances or use cases when Exadata would be the Oracle product or tool selected are: A move from hundreds of standalone database hardware and software nodes to one database machine A reduction in hardware and software vendors, and one vendor for hardware and software support Keepin It Real The database appliance has become the latest trend in the IT industry. Data warehouse appliances like Netezza have been around for a number of years. Oracle has been the first vendor to offer an open systems database appliance for both DW and OLTP environments. Data grid Instead of consolidating databases physically or accessing the data where it resides, a data grid places the data into an in-memory middle tier. Like physical federation, the data is being placed into a centralized data repository. Unlike physical federation, the data is not placed into a traditional RDBMS system (Oracle database), but into a high-speed memory-based data grid. Oracle offers both a Java and SQL-based data grid solution. The decision of what product to implement often depends on where the corporations system, database, and application developer skills are strongest. If your organization has strong Java or .Net skills and is more comfortable with application servers than databases, then Oracle Coherence is typically the product of choice. If you have strong database administration and SQL skills, then Oracle TimesTen is probably a better solution. The Oracle Exalogic solution takes the data grid to another level by placing Oracle Coherence, along with other Oracle hardware and software solutions, into an appliance. This appliance provides an 'end-to-end' solution or data grid 'in a box'. It reduces management, increases performance, reduces TCO, and eliminates the need for the customer having to build their own hardware and software solution using multiple vendor solutions that may not be certified to work together. Oracle Coherence Oracle Coherence is an in-memory data grid solution that offers next generation Extreme Transaction Processing (XTP). Organizations can predictably scale mission critical applications by using Oracle Coherence to provide fast and reliable access to frequently used data. Oracle Coherence enables customers to push data closer to the application for faster access and greater resource utilization. By automatically and dynamically partitioning data in memory across multiple servers, Oracle Coherence enables continuous data availability and transactional integrity, even in the event of a server failure. Oracle Coherence was purchased from Tangosol Software in 2007. Coherence was an industry-leading middle tier caching solution. The product only offered a Java solution at the time of acquisition, but a .NET offering was already scheduled before the acquisition took place. The Oracle Coherence source and target data sources along with development languages and tools supported are: Data sourceData targetDevelopment languages and toolsJDBC, any data source accessible through Oracle SOA adaptersCoherenceJava, .Net The most likely instances or use cases when Oracle Coherence would be the Oracle product or tool selected are: When it is necessary to replace custom, hand-coded solutions that cache data in middle tier Java or .NET application servers Your company's strengths are in application servers Java or .NET Oracle TimesTen Oracle TimesTen is a data grid/cache offering that has similar characteristics to Oracle Coherence. Both of the solutions offer a product that caches data in the middle tier for high throughput and high transaction volumes. The technology implementations are much different. TimesTen is an in-memory database solution that is accessed through SQL and the data storage mechanism is a relational database. The TimesTen solution data grid can be implemented across a wide area network (WAN) and the nodes that make up the data grid are kept in sync with your back end Oracle database using Oracle Cache Connect. Cache Connect is also used to automatically refresh the TimesTen database on a push or pull basis from your Oracle backend database. Cache Connect can also be used to keep TimesTen databases spread across the global in sync. Oracle TimesTen offers both read and update support, unlike other database in- memory solutions. This means that Oracle TimesTen can be used to run your business even if your backend database is down. The transactions that occur during the downtime are queued and applied to your backend database once it is restored. The other similarity between Oracle Coherence and TimesTen is that they both were acquired technologies. Oracle TimesTen was acquired from the company TimesTen in 2005. The Oracle TimesTen source and target data sources along with development languages and tools supported are: Data sourceData targetDevelopment languages and toolsOracleTimesTenSQL, CLI The most likely instances or use cases when Oracle TimesTen would be the Oracle product or tool selected are: For web-based read-only applications that require a millisecond responseand data close to where request is made For applications where updates need not be reflected back to the user in real-time Oracle Exalogic A simplified explanation of Oracle Exalogic is that it is Exadata for the middle tier application infrastructure. While Exalogic is optimized for enterprise Java, it is also a suitable environment for the thousands of third-party and custom Linux and Solaris applications widely deployed on Java, .NET, Visual Basic, PHP, or any other programming language. The core software components of Exalogic are WebLogic, Coherence, JRocket or Java Hotspot, and Oracle Linux or Solaris. Oracle Exalogic has an optimized version of WebLogic to run Java applications more efficiently and faster than a typical WebLogic implementation. Oracle Exalogic is branded with the Oracle Elastic cloud as an enterprise application consolidation platform. This means that applications can be added on demand and in real-time. Data can be cached in Oracle Coherence for a high speed, centralized, data grid sharable on the cloud. The Exalogic source and target data sources along with development languages and tools supported are: Data sourceData targetDevelopment languages and toolsAny data sourceCoherenceAny language The most likely instances or use cases when Exalogic would be the Oracle product or tool selected are: Enterprise consolidated application server platform Cloud hosted solution Upgrade and Consolidation of hardware or software Oracle Coherence is the product of choice for Java and .NET versed development shops. Oracle TimesTen is more applicable to database-centric and shops more comfortable with SQL.

  MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook More than 80 recipes for enriching your Business Intelligence solutions with high-performance MDX calculations and flexible MDX queries in this book and eBook Iteration is a very natural way of thinking for us humans. We set a starting point, we step into a loop, and we end when a condition is met. While we're looping, we can do whatever we want: check, take, leave, and modify items in that set. Being able to break down the problems in steps makes us feel that we have things under control. However, by breaking down the problem, the query performance often breaks down as well. Therefore, we have to be extra careful with iterations when data is concerned. If there's a way to manipulate the collection of members as one item, one set, without cutting that set into small pieces and iterating on individual members, we should use it. It's not always easy to find that way, but we should at least try. Iterating on a set in order to reduce it Getting ready Start a new query in SSMS and check that you're working on the right database. Then write the following query: SELECT { [Measures].[Customer Count], [Measures].[Growth in Customer Base] } ON 0, NON EMPTY { [Date].[Fiscal].[Month].MEMBERS } ON 1 FROM [Adventure Works] WHERE ( [Product].[Product Categories].[Subcategory].&[1] ) The query returns fiscal months on rows and two measures: a count of customers and their growth compared to the previous month. Mountain bikes are in slicer. Now let's see how we can get the number of days the growth was positive for each period. How to do it... Follow these steps to reduce the initial set: Create a new calculated measure in the query and name it Positive growth days. Specify that you need descendants of current member on leaves. Wrap around the FILTER() function and specify the condition which says that the growth measure should be greater than zero. Apply the COUNT() function on a complete expression to get count of days. The new calculated member's definition should look as follows, verify that it does. WITH MEMBER [Measures].[Positive growth days] AS FILTER( DESCENDANTS([Date].[Fiscal].CurrentMember, , leaves), [Measures].[Growth in Customer Base] > 0 ).COUNT Add the measure on columns. Run the query and observe if the results match the following image: How it works... The task says we need to count days for each time period and use only positive ones. Therefore, it might seem appropriate to perform iteration, which, in this case, can be performed using the FILTER() function. But, there's a potential problem. We cannot expect to have days on rows, so we must use the DESCENDANTS() function to get all dates in the current context. Finally, in order to get the number of items that came up upon filtering, we use the COUNT function. There's more... Filter function is an iterative function which doesn't run in block mode, hence it will slow down the query. In the introduction, we said that it's always wise to search for an alternative if available. Let's see if something can be done here. A keen eye will notice a "count of filtered items" pattern in this expression. That pattern suggests the use of a set-based approach in the form of SUM-IF combination. The trick is to provide 1 for the True part of the condition taken from the FILTER() statement and null for the False part. The sum of one will be equivalent to the count of filtered items. In other words, once rewritten, that same calculated member would look like this: MEMBER [Measures].[Positive growth days] AS SUM( Descendants([Date].[Fiscal].CurrentMember, , leaves), IIF( [Measures].[Growth in Customer Base] > 0, 1, null) ) Execute the query using the new definition. Both the SUM() and the IIF() functions are optimized to run in the block mode, especially when one of the branches in IIF() is null. In this particular example, the impact on performance was not noticeable because the set of rows was relatively small. Applying this technique on large sets will result in drastic performance improvement as compared to the FILTER-COUNT approach. Be sure to remember that in future. More information about this type of optimization can be found in Mosha Pasumansky's blog: http://tinyurl.com/SumIIF Hints for query improvements There are several ways you can avoid the FILTER() function in order to improve performance. When you need to filter by non-numeric values (i.e. properties or other metadata), you should consider creating an attribute hierarchy for often-searched items and then do one of the following: Use a tuple when you need to get a value sliced by that new member Use the EXCEPT() function when you need to negate that member on its own hierarchy (NOT or <>) Use the EXISTS() function when you need to limit other hierarchies of the same dimension by that member Use the NONEMPTY() function when you need to operate on other dimensions, that is, subcubes created with that new member Use the 3-argument EXISTS() function instead of the NONEMPTY() function if you also want to get combinations with nulls in the corresponding measure group (nulls are available only when the NullProcessing property for a measure is set to Preserve) When you need to filter by values and then count a member in that set, you should consider aggregate functions like SUM() with IIF() part in its expression, as described earlier.  

  MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook More than 80 recipes for enriching your Business Intelligence solutions with high-performance MDX calculations and flexible MDX queries in this book and eBook Skipping axis There are situations when we want to display just a list of members and no data associated with them. Naturally, we expect to get that list on rows, so that we can scroll through them nicely. However, the rules of MDX say we can't skip axes. If we want something on rows (which is AXIS(1) by the way), we must use all previous axes as well (columns in this case, which is also known as AXIS(0)). The reason why we want the list to appear on axis 1 and not axis 0 is because a horizontal list is not as easy to read as a vertical one. Is there a way to display those members on rows and have nothing on columns? Sure! This recipe shows how. Getting ready Follow these steps to set up the environment for this recipe: Start SQL Server Management Studio (SSMS) or any other application you use for writing and executing MDX queries and connect to your SQL Server Analysis Services (SSAS) 2008 R2 instance (localhost or servernameinstancename). Click on the New Query button and check that the target database is Adventure Works DW 2008R2. How to do it... Follow these steps to get a one-dimensional query result with members on rows: Put an empty set on columns (AXIS(0)). Notation for empty set is this: {}. Put some hierarchy on rows (AXIS(1)). In this case we used the largest hierarchy available in this cube – Customer hierarchy of the same dimension. Run the following query: SELECT { } ON 0, { [Customer].[Customer].[Customer].MEMBERS } ON 1 FROM [Adventure Works] How it works... Although we can't skip axes, we are allowed to provide an empty set on them. This trick allows us to get what we need – nothing on columns and a set of members on rows. There's more… Notice that this type of query is very convenient for parameter selection of another query as well as for search. See how it can be modified to include only those customers whose name contains the phrase "John": SELECT { } ON 0, { Filter( [Customer].[Customer].[Customer].MEMBERS, InStr( [Customer].[Customer].CurrentMember.Name, 'John' ) > 0 ) } ON 1 FROM [Adventure Works] In the final result, you will notice the "John" phrase in various positions in member names: The idea behind If you put a cube measure or a calculated measure with a non-constant expression on axis 0 instead, you'll slow down the query. Sometimes it won't be so obvious, sometimes it will. It will depend on the measure's definition and the number of members in the hierarchy being displayed. For example, if you put the Sales Amount measure on columns, that measure will have to be evaluated for each member in the rows. Do we need those values? No, we don't. The only thing we need is a list of members; hence we've used an empty set. That way, the SSAS engine doesn't have to go into cube space. It can reside in dimension space which is much smaller and the query is therefore more efficient. Possible workarounds In case of a third-party application or a control which has problems with this kind of MDX statement (i.e. expects something on columns and is not working with an empty set), we can define a constant measure (a measure returning null, 0, 1 or any other constant) and place it on columns instead of that empty set. For example, we can define a calculated measure in the MDX script whose definition is 1, or any other constant value, and use that measure on the columns axis. It might not be as efficient as an empty set, but it is a much better solution than the one with a regular (non-constant) cube measure like the Sales Amount measure.   Handling division by zero errors Another common task is handling errors, especially division by zero type of errors. This recipe offers a way to solve that problem. Not all versions of Adventure Works database have the same date range. If you're not using the recommended version of it, the one for the SSAS 2008 R2, you might have problems with queries. Older versions of Adventure Works database have dates up to the year 2006 or even 2004. If that's the case, make sure you adjust examples by offsetting years in the query with a fixed number. For example, the year 2006 should become 2002 and so on. Getting ready Start a new query in SQL Server Management Studio and check that you're working on Adventure Works database. Then write and execute this query: WITH MEMBER [Date].[Calendar Year].[CY 2006 vs 2005 Bad] AS [Date].[Calendar Year].[Calendar Year].&[2006] / [Date].[Calendar Year].[Calendar Year].&[2005], FORMAT_STRING = 'Percent' SELECT { [Date].[Calendar Year].[Calendar Year].&[2005], [Date].[Calendar Year].[Calendar Year].&[2006], [Date].[Calendar Year].[CY 2006 vs 2005 Bad] } * [Measures].[Reseller Sales Amount] ON 0, { [Sales Territory].[Sales Territory].[Country].MEMBERS } ON 1 FROM [Adventure Works] This query returns 6 rows with countries and 3 rows with years, the third row being the ratio of the previous two, as its definition says. The problem is that we get 1.#INFM on some cells. To be precise, that value (the formatted value of infinity), appears on rows where the CY 2005 is null. Here's a solution for that. How to do it... Follow these steps to handle division by zero errors: Copy the calculated member and paste it as another calculated member. During that, replace the term Bad with Good in its name, just to differentiate those two members. Copy the denominator. Wrap the expression in an outer IIF() statement. Paste the denominator in the condition part of the IIF() statement and compare it against 0. Provide null value for the True part. Your initial expression should be in the False part. Don't forget to include the new member on columns and execute the query: MEMBER [Date].[Calendar Year].[CY 2006 vs 2005 Good] AS IIF ([Date].[Calendar Year].[Calendar Year].&[2005] = 0, null, [Date].[Calendar Year].[Calendar Year].&[2006] / [Date].[Calendar Year].[Calendar Year].&[2005] ), FORMAT_STRING = 'Percent' The result shows that the new calculated measure corrects the problem – we don't get errors (the rightmost column, compared to the one on its left): How it works... A division by zero error occurs when the denominator is null or zero and the numerator is not null. In order to prevent this error, we must test the denominator before the division and handle the case when it is null or zero. That is done using an outer IIF() statement. It is enough to test just for zero because null = 0 returns True. There's more... SQLCAT's SQL Server 2008 Analysis Services Performance Guide has lots of interesting details regarding the IIF() function: http://tinyurl.com/PerfGuide2008 Additionally, you may find Jeffrey Wang's blog article useful in explaining the details of the IIF() function: http://tinyurl.com/IIFJeffrey Earlier versions of SSAS If you're using a version of SSAS prior to 2008 (that is, 2005), the performance will not be as good. See Mosha Pasumansky's article for more info: http://tinyurl.com/IIFMosha