Elixir Universe
Contents
1. 1 Comments Add any comments that you desire for your own understanding These comments are not displayed in the Ad Hoc report or dashboard when published 2 Enumeration By defining the attributes of individual fields the system can validate the record values using the cleansing process For example you might specify that the field Gender is a nominal enumeration of M and F The cleansing process would then warn you of any records containing m or Female 20 Using the Universe Designer Ordinal enumeration attributes allow you to specify the order in which to display data while retaining the original order For example you might specify that the field Fruit is an ordinal enumeration of Apple and Orange Only Apple and Orange records can pass the validation in cleansing process Foreign Key A foreign key is a field or collection of fields in one table that uniquely identifies a row of another table Enter the name of the referenced table and the name of the referenced column In case of a compound foreign key where the foreign key is made up of two or more columns the sequence number defines the order of the columns that make up the key Format Enter the format of the data in this column Nullable Specify whether the data can be Null values Primary Key A Primary Key is necessary for all tables in the Repository Universe Ensure that each table has a unique Primary Key column This is important for2. Create a new Universe Enter name and universe type The JDBC universe allows data to be 4 3 read from a relational database using J Repository a JDBC driver 4 3 JDBC Java Database Connectivity Stave allows access to data via SQL statements If your data is all defined in a relational database and a JDBC driver is available then this is your best choice Previous Next Finish Cancel 4 Click Finish The universe is then created and opened in the Universe Designer Creating a Universe Creating a Repository Universe To create a Repository Universe Y 2 4 Login to the Ambience Designer Right click the location where you want to add the Universe Select Add Universe as shown in Figure 3 1 Menu to Add a Universe Enter a name for the Universe and select the type as Repository as shown in Figure 3 3 Adding a Repository Universe Figure 3 3 Adding a Repository Universe Add Universe Create a new Universe Enter name and universe type Name Sales F x gt JDBC Repository Universe The Repository universe allows data to X be read from any Elixir datasource Repository Elixir allows access to a wide variety of 4 3 data sources including JDBC Excel P 2 Stave XML and flat files Using this type you can mix data from any supported format The datasources can be embedded in the universe or maintained as external links Pr
3. seeds c rr ER ERIS REPERI RR ERE E RE tye PESEE 31 Other Actions ipee gene ee pU RR EI UI 31 Stave REPOSILOLY 5 cero CR ses bee SUP day a oa ES ER TE SEE ege Dense ve ERO sainte 31 Mapping Stave Columns to Logical Tables seem 32 Operations ON Stdye onec peret Voces Sayed adage ssdess savetsendveeecessgeeSenness 32 lil List of Figures 3 Menu to Add Universe iter bte t to RIEN Eve PO eere Rt ge Oc bx ee terre E 4 32 Adding JDBC Universe nette eese ete repe os ETES aS 4 3 3 Adding a Repository Univ rse rere rm eer ERI e E par ER ERR Spr sactessssenbansase 5 3 4 Adding a Stave Universe isis vcs esee tenerte umet e ra veter tapes rebus et gerent 6 3 5 Selecting a Stave Table rot et It RU RE br PCR Eee Ere net Ev Ere 6 4 1 Universe Example 4 ette derer tret der muere ue re rore e rer des cee 8 4 2 Examining Table Str ctute cire esee nee rte Peer yh ER SERERE ERR Pens 9 4 3 Setting Attributes ene i EE EERE lee iieri M ene rM ESEESE E S 10 2 4 Adding Transtorms iore Re Fee OON ates ah wean v eese PO On donee ea RAE tees 11 4 5 Examining Output Data ecce eere Eve etre deeper ea unoque rie e ip dee 11 4 6 Inspecting Column Data 2 ien rend eren epe eb p ERR Ete er Rr epp reda 12 4 7 Viewing the Data Summary sss He He mee hee eere hee trennen 13 5 1 Universe Designer cs metr e Og E te PE te e PORTET U RECS E e E 14 5 2 Selecting
4. Exporting the Universe To export the Universe for use by Ad Hoc Dashboard and Ad Hoc Report 1 m Click the Admin Tools icon 2 Select the Universe as shown in Figure 5 13 Exporting the Universe 26 Using the Universe Designer Figure 5 13 Exporting the Universe Universe Mapping Universe Name Universe File ElixirSamples Universe Cars universe D isi Publish Template Dashboard Publish Template Report Optionally click the Edit EJ h icon and enter your desired Universe name The Universe is mapped with this name and can now be referred to in the Ad Hoc Dashboard and Ad Hoc Report applications An example of mapping is shown in Figure 5 14 Mapping the Universe 27 Using the Universe Designer Figure 5 14 Mapping the Universe System Libraries Monitor Unive rse Universe Mapping Universe Name Universe File Edit Universe a ElixirSamples Universe Cars universe q File ElixirSamples Universe Cars universe i7 4 Click either Publish Template Dashboard or Publish Template Report to save the Universe in a format suitable for use by Ad Hoc Dashboard and Ad Hoc Report respectively Setting the Universe Access Permissions By default everyone can access the Universe provided it is saved in a publicly accessible folder i e not in any User folder Optionally to set restrict the users and groups who can access
5. driver is available for your database then use a JDBC Universe to access your data Repository Universe You can use any Elixir data source to read your data when using this Universe You can access data from a large number of data sources such as JDBC Excel XML and flat files You can mix data from any supported format You can either embed the data sources in the Universe or maintain them as external links Stave The Stave universe allows data to be read from a column based Stave datastore Every column in a Stave table is already sorted and grouped This enables lightning fast data retrieval and facilitates better data compression Each column in Stave has the following characteristics e An attribute one of Boolean Decimal Double Long or String e An encoder decoder A compressor decompressor gzip is the default e Attributes e The row index acts as the Primary Key Chapter 3 Creating a Universe Before You Begin Note the following points when creating any Universe Create a Universe file based on the origins and stability of the data For example you can create a Stave Universe if the data is stable and rarely changes If your data comes from a JDBC database use a JDBC Universe Or if you need to fetch data from multiple kinds of data sources use a Repository Universe If you want your Universe to be usable by others do not save it in your user folder instead save it in a publicly accessible fo
6. in memory tables data sources that rarely change Marking a data source as Static Cache causes its entities to be cached in memory 19 Using the Universe Designer Some examples of data sources that can be cached are a table of zip codes and the periodic table 4 Table Security Enter the names of the users who are allowed to view this table User names can be separated by spaces tabs or new lines By default leaving this blank all users are allowed to view this table You can also set Column level as well as Row level security by selecting the Security Transform For detailed information on transform categories refer to the Elixir Transform User Manual For JDBC and Stave Universes you can set the Column level and Row level security from within the Column Attributes tab See the following Column Attributes section for more information Column Attributes This section allows you to set attributes for a single column in the table You can enumerate values set the format for display set the column as a primary key or as a foreign key and set the column values to be in a defined range The various column attributes are shown in Figure 5 8 Setting Column Attributes Figure 5 8 Setting Column Attributes Column Attribute Type Comments E Comments Enumeration ForeignKey Format Nullable PrimaryKey Range RegExp Ok Cancel The following are the various column attributes
7. the Universe Click the Universe Access d D icon 2 Select the Users and Groups who are allowed to access the Universe as shown in Figure 5 15 Setting the Universe Access Permissions 28 Using the Universe Designer Figure 5 15 Setting the Universe Access Permissions Universe Access D Allowed Users Groups P x A Access Users Groups admin group2 Ok Cancel 3 Click OK to save the access settings JDBC Repository A JDBC Universe allows data to be read from a relational database using a JDBC driver If your data is defined in a relational database and a JDBC driver is available then JDBC Universe is recommended Before getting started add the JDBC driver into opt elixir 1ib to make it available to all Java Virtual Machines Double click a JDBC universe from the folder tree to open it in the Universe Designer The Universe Designer displays as shown in Figure 5 16 Universe Designer for a JDBC Universe 29 Using the Universe Designer Figure 5 16 Universe Designer for a JDBC Universe 7228s sau o u _ d Settings Bows isaer suc mer Shea aramacers Joue transforms uara B Tyan name Daserecion e e e Tatia Attrinutes Colm attioutas Attributes nae Vue Configuring a Connection Pool JDBC allows access to data through SQL statements To configure the connection pool click the 8 icon For example y
8. Ad Hoc Dashboard For most cases a table has only one Primary Key therefore the sequence number is 0 If you are working on a datasource without an obvious Primary Key for example Fruit Sales ds add a Sequence Transform to create a new ID column and add a Column Attribute to mark it as a Primary Key Since Fruit Sales ds uses a composite Primary Key an alternative approach is to add a Column Attribute to mark Company as PrimaryKey 0 and add another Column Attribute to mark Fruit as PrimaryKey 1 Range Specify the start value and end value for the data in the column RegExp Define and test the regular expression syntax Either enter the regular expression or select from the list This helps ensure that the data is correctly entered before processing Security Only applicable for JDBC and Stave Universes Allows you to set the Column level and Row level security for JDBC and Stave Universes Enter the names of users and groups who are granted the right to view the column or row data User names can be separated by spaces tabs or new lines An example of setting Column security is shown in Figure 5 9 Setting Column Security 21 Using the Universe Designer Figure 5 9 Setting Column Security Column Attribute Type security 7 Column Security _ Row Security test sales Note the following points when setting up Column or Row security If you do not set up security all users are all
9. CHEVROLET INC Midwest City OK 73110 243378 CFC22528 2 HUDIBURG CHEVROLET INC Midwest City OK 73110 435653 CFC20794 Inspector The Inspector tab enables you to review the contents of a column It displays column information value frequency based on the data type as well as the chart type and plot Select the column to inspect and click the Load Data icon ON An example Inspector tab is shown in Figure 5 11 Inspector Tab 24 Using the Universe Designer Figure 5 11 Inspector Tab co eee Chart type Pie Frequency Plot against ZIP area_code Information Name Value Min length 2 Max length 10 Row count 33 862 Null count 4 962 15 Unique values 273 1 Min value 201 Max value 989 area_code Value Frequen Summary The Summary tab displays the column names data types and Primary and Foreign Key information An example Summary tab is shown in Figure 5 12 Summary Tab 25 Using the Universe Designer Figure 5 12 Summary Tab trade in odometer reading Integer trade in registration end Date ForeignKey Date Date 0 trade in registration start Date ForeignKey Date Date 0 trade in registration state String trade in title state String trade in vehicle category String trade in vehicle drive train String trade in year Integer vendor id Integer Related Tables Cars Date Month USRegion USState
10. Elixir Universe Release 3 5 0 Flixir PY ae Elixir Technology Pte Ltd Elixir Universe Release 3 5 0 Elixir Technology Pte Ltd Published 2014 Copyright 2014 Elixir Technology Pte Ltd All rights reserved Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries Microsoft and Windows are trademarks of Microsoft Corporation Table of Contents F Introduction ose eot rt Re ee pat andes dette ERREUR pre passe bea detag yet e e ter dc EHE 1 What 18 4 Universe vv emt c p acess cte ee ts CUP P TIR Lo aes 1 2 Universe Types deo ios pepe HER Ee voce ER ERE E MEE ERE ER CEREREEPERPS 2 JDBC Universe iue a eren 2 Repository Universe pe bt erp e E Te SR Re ied Reb Ree Rte Fes E e eet ed 2 SUAVE T 2 3 Creating a Universe iuc ere tereti Er cong sisboes CASATE RESEPTOR Er Eoia EEEE 3 Before You Bein iie tegi eene gti estet eettore p hence ipie ee e ected vip ters 3 Creating a JDBG Universe certo tet TERRE Pate e eps ee ee Re rre Poeta eas 3 Creating a Repository Universe sessesseseseeee m e mI He mH men hen he mee ree ree nte 5 Creating a Stave Universe ceci a peer e siTe ERR ee RP S ESPES Ep E EEP IRR Eee 5 4 Universe Designer Walkthrough sssssse emen emm entren 8 Universe Designer oet ER E Re Pete Ext ertet eee tt ipe Peto Ere terea Rte gens 8 Examining Table Structure nsccesosccrres restresor EEEE ee I He
11. Rire hte rere Ere Pere 32 5 20 Mapped Table for a Stave Universe sssessse IH emere 33 Chapter 1 Introduction What is a Universe A universe is a collection of related data tables which provides access to all information needed to construct an ad hoc report or dashboard In cases where the system knows the primary key and foreign key relationships between the tables it automatically joins data from various tables and presents you the information seamlessly Logically each Universe should represent a single domain For example in a vehicle store there could be a Universe named Cars which may contain tables for model details sales and service details Similarly there could be another Universe named Bikes containing all bike related information Using the above example you can pull in statistics such as the number of cars sold versus the number of bikes sold how reliable each car was popularity for a particular vintage model of a car versus the same model of a bike and so on This manual describes the different kinds of universes and how to create and use them effectively Chapter 2 Universe Types There are three types of universes that you can create This chapter explains these types JDBC Universe The JDBC Universe allows data to be read from a relational database using a JDBC driver You can access your data using SQL statements If you have all your data in a relational database and a JDBC
12. String Exporting the Output Data You can easily export the output data for use by the Ad Hoc Dashboard and the Ad Hoc Report applications Refer to the section called Exporting the Universe for the details Chapter 5 Using the Universe Designer The Universe Designer allows you to create or import data sources into the universe examine the rows in a table set various table and column attributes select the data to be transformed or displayed and create various transforms to transform the data to your desired results Repository Universe Double click a repository universe from the folder tree to open it in the Universe Designer The Universe Designer displays as shown in Figure 5 1 Universe Designer Figure 5 1 Universe Designer Elnur Ambieace 3 5 0 a QU omie Alrbutes num vake e Adding a New Table A Repository Universe allows you to select data from various table columns across multiple data sources If the system deduces the relationships between the various columns and tables based on the primary and foreign keys it seamlessly joins and the data and outputs the results To add a new table column to the Universe l Click the Add Table icon El 2 Select the data source for which you want to add a table Repository universe supports a large number of data sources In the example shown in Figure 5 2 Selecting a Data Source Repository Universe we sel
13. a Data Source Repository Universe 0c cee ceeeeeec ence cece me 15 5 3 Adding a Column uere reete ed Eres ot Rr PR ERERPD ERE e ERR R ERES 15 5 4 Adding Column Val e eese EE EEE EE ESEE ener r pe Ee een R eve 16 5 5 Importing a Data Source eerte tre vete etate ie pr eee vette ras pasate 17 5 6 Viewing a Schema ni noioe e Rege bep de ert ae cas epe iei erre ep IER RR ee HRS 18 5 7 Setting Table Attributes nine err rrt EE HR ed Re er Hue PRESE RE e rre dre ERR RRRERA 19 5 8 Setting Column Attributes siso asee hene Era EE E enhn hen ment ent entre nennen 20 3 9 Setting Column S curnty us ertet poteet ER co tic bate Eee to rel ae DEN E e 22 9 10 Rows MP 24 5 THE Inspector Tab o iet EP Rt e edere E EAE EESE TESES 25 3 12 Summary Tab sisie eie etre gr eM DER psi ired 26 3 13 Exporting the Universe ee tere Peter et dea erate saa POR Te eS RT p Rhe ica 27 5 14 Mapping the Universe c cesot cece cece cece cece em e mH me men EEE ree hee IE ETE EEE 28 5 15 Setting the Universe Access Permissions esses Heer 29 5 16 Universe Designer for a JDBC Universe esses e 30 5 17 Configuring the Connection Pool 2 0 0 0 cee eeccee cee ceeeceeeeeeeca e eene hene 30 5 18 Universe Designer for a Stave Universe sssssssssseseee ee eme 31 5 19 Table Mapping for a Stave Universe 2 2 3 e nee edge tere
14. al facility nmvtis id Integer disposal facility contact String sales type String invoice amount Decimal trade in VIN String trade in vehicle category String trade in make String v dnm m mnl Sda smi nm mn Parameters The Parameters tab displays the parameters of the data source if any The Parameter processor retrieves parameters from one input and applies them to another flow This allows flows to be developed and reused with different sets of parameters Parameters can be maintained independent of the flows For more information on Parameters see Parameter Processor in the Data Designer Manual Attributes This section allows you to add table and column attributes You can define Primary Key and Foreign Key relationships between the various columns You can set table level security set up a table to be cached in memory and set dimension parameters Table Attributes This allows you to set the following attributes for the table as a whole Click the Add icon b The various table attributes are shown in Figure 5 7 Setting Table Attributes 18 Using the Universe Designer Figure 5 7 Setting Table Attributes ZZ Table Attribute Type Change Dimension Capture Insert timestamp field Update timestamp field Delete timestamp field Ok Cancel 1 Change Dimension Capture This is used to determine and track the data that has changed so that suitable actions can be taken u
15. ate disposal status String disposal facility nmvtis id Integer disposal facility contact String sales type String invoice amount Decimal trade in VIN String trade in vehicle category String f trade in make String The data parameters if any are displayed in the Parameter tab Setting Attributes You can set various attributes such as table security primary and foreign keys and the format of the data You can set attributes that are common to a table as a whole and also individual column attributes An example of setting attributes is show in Figure 4 3 Setting Attributes Universe Designer Walkthrough Figure 4 3 Setting Attributes Transforms Field Transform vendor_id Integer e dealer name String city String e state String o ZIP String area_code String phone String f invoice id z involce num Column Attribute invoice date sale date disposal_status Comments disposal_facility_nmvtis_id Enumeration disposal_facility_contact Foreignkey sales type Format invoice amount trade in VIN Nullable trade in vehicle category PrimaryKey l trade_in_make Range Table Attributes Column Attrib RegExp Attributes sale date 6 Name Value Foreignkey Date Dat ok cancel Transforming Data You can add various transforms to manipulate the output as desired Fo
16. e mee ee rene 8 Setting Attributes 45i ere Heer Ere Sends syocseeeaegsendevecvassasepseesacuvtss ta ITEE 9 Transforming Data aace re o Er EE eA UM pere REIN SAPE 10 Examining Output Data tr reete te irte re Re reip Rer Ree certe ree baee 11 Inspecting Column Data sorone ea E Ee E em em eH e mH me meme hee rre rre ne 11 Viewing the Summary of Data cos iscccccusssccessessesi segs serees dgucdessgs r OnE KESSE Eoi EEE ISu ERS S PETE 12 Exporting the Output Data osete cece cece ee cece ace em mH mH e mem emen rre nree ne 13 5 Using the Universe Designer eicit te Eee a Pet hee ere Ente E to gei POR gR eRe RR 14 Repository Universe eet ees sra ES n devs EOS ET E EE EE de Pe E Se SERV DO 14 Addins New Table sisi icri I Rex ERE evden EPIRI ST aa E ATEAN 14 Importing a Data Source isise cece cece ence ne ce eee IH HH eee eere 16 Settings Tab secte sette eret etre estes a qe EU re Ure 17 ROWS EE 24 InSpectot a Gs PR ETE EPOR ERR ORO AREE ER PO ER OPER FREIER PER EVEIQ ad soups 24 Nili EE 25 Exporting the Universe 5 sas tet tr RR etg ree te PE SERES 26 Setting the Universe Access Permissions sssessss ee 28 JDBC Repository nti erm ete P PI HER T EEE PE IE SHEER Sa we SEPT e Ere nS 29 Configuring a Connection Pool ssssssssesses eee eee eere 30 Addins Tables ttt rte te reto etete ettet to perte E E 31 Pasting Tables iere 8a sacks BO echt Bn eee hocks 31 Viewing SQL is sessions
17. ect a Tabular data source Tranatarme Destination Using the Universe Designer Figure 5 2 Selecting a Data Source Repository Universe ZZ DataSource Wizard New Datasource Choose a datasource type KA ARFF Form a Ul Google inary ps Spreadsheet Ur Cache ia JDBC a DBF i LDAP p Excel n OLAP4J uL FileSystem fs Object Previous Next Finish 3 Enter a Name and a Description for the data source Click the Add icon 2 5 Enter a name for the column and select its data type as shown in Figure 5 3 Adding a Column Figure 5 3 Adding a Column Add Column al Name Quantity ok Cancel 6 Click OK to save the column 7 Click the Add icon again and repeat the process to add as many columns as you need 8 Click Next You need to enter the values for the columns you have saved Click the Add icon Oo 10 Enter the values for the columns you have saved as shown in Figure 5 4 Adding a Column Value 15 Using the Universe Designer Figure 5 4 Adding a Column Value DataSource Wizard Define Tabular Datasource Enter Tabular values y EF Add Col Quantity 1000 A ok Cancel y D Row Quantity Revenue S x na a Previous Next Finish Cancel j 11 Click OK to save the column 12 Click the Add icon again and repeat the process to add as many values as y
18. ection called Repository Universe Stave Repository Stave is a column based repository Data is stored in columns Stave offers better data compression and extremely fast data retrieval Double click a Stave Universe from the folder tree to open it in the Universe Designer The Universe Designer displays as shown in Figure 5 18 Universe Designer for a Stave Universe Figure 5 18 Universe Designer for a Stave Universe Stave Column Type Universe Table Universe Column Company String DataStore Stavelndex nteger Date Date Dale day nteger Date_dow nteger Date_month nteger Date_quarter nteger Date_year nteger Fruit String ld nteger Quantity nteger Unit Cost Decimal WeightkG Decimal 31 Using the Universe Designer Mapping Stave Columns to Logical Tables Before the Stave columns can be used you need to map them to a logical table Click the Mapping tab For each row enter a name for the Universe table and a name for the column as shown in Figure 5 19 Table Mapping for a Stave Universe Figure 5 19 Table Mapping for a Stave Universe Mapping Tables Stave Column Type Universe Table Universe Column Datastore Stavelndex Integer Fruits Index Date Date Fruits Date Date day Integer Fruits Dey Jate_dow Integer De aak Date month Integer Fruits Month Date quarter Integer Fruits Quarzer Dale year Integer Fruits Year Fruit String Integer Fruits 10 Quantity Integer Fruits Quan
19. evious Next Finish Cancel Click Finish The universe is then created and opened in the Universe Designer Creating a Stave Universe To create a Stave Universe 1 2 Login to the Ambience Designer Right click the location where you want to add the Universe Select Add Universe as shown in Figure 3 1 Menu to Add a Universe Enter a name for the Universe and select the type as Stave as shown in Figure 3 4 Adding a Stave Universe Creating a Universe Figure 3 4 Adding a Stave Universe Add Universe Create a new Universe Enter name and universe type Name Sales 4 3 i R gt IDBC Stave Universe The Stave universe allows data to be 4 read from the column based Stave 3 Repository datastore Stave is particularly suited to the data X Stave access patterns needed for business analytics Previous Next Finish Cancel Click Next Select the Stave table that contains the data for the universe as shown in Figure 3 5 Selecting a Stave Table Figure 3 5 Selecting a Stave Table ZZ Add Universe ali ia TO Define a Stave Universe Choose the table defining stave columns Table Fruits Stave v Columns Previous Next Cancel Creating a Universe See the Data Designer manual to know how to create a Stave table 5 Click Finish The universe is then created and opened in the Universe Designer Chapter 4 Universe Desig
20. f the data in the Inspector tab is show in Figure 4 6 Inspecting Column Data Universe Designer Walkthrough Figure 4 6 Inspecting Column Data Column trade in year trade in year Information Chart type Name Value Average 1 995 201 Median 1 996 StandardDevi 4 116 Variance 16 94 Row count 33 862 Null count 0 096 Unique values 25 096 Min value 1984 Max value 2008 trade in year Value Frequency Value Count il 2995 3342 1997 3286 1998 3201 1999 3200 1996 3095 1994 2825 2000 2173 1993 2117 1992 1662 2001 1455 1991 1376 1990 1184 1989 1164 1988 921 2002 780 1987 617 1986 536 1985 510 2003 220 1984 106 The example displays statistical data of the entities in the selected column and the number of cars that were traded in for each year Viewing the Summary of Data The Summary tab displays the data type of each column along with the primary and foreign key relationships between the columns An example of the data in the Summary tab is show in Figure 4 7 Viewing the Data Summary 12 Universe Designer Walkthrough Figure 4 7 Viewing the Data Summary trade in odometer reading Integer trade in registration end Date ForeignKey Date Date 0 trade in registration start Date ForeignKey Date Date 0 trade in registration state String trade in title state String trade in vehicle category
21. lder such as Public Review the data from the Universe Designer by viewing it in the Rows tab as described in the section called Rows Map the Universe file so that it can be referenced by other tools This can only be done by an administrator An example of Mapping is shown in the section called Exporting the Universe Assign access rights to the Universe as desired This is shown in the section called Setting the Universe Access Permissions Finally save the Universe as a Published Template for Ad Hoc Dashboard and Ad Hoc Report This is described in the section called Exporting the Universe Creating a JDBC Universe To create a JDBC Universe 1 2 Login to the Ambience Designer Right click the location where you want to add the Universe Select Add Universe as shown in Figure 3 1 Menu to Add a Universe Creating a Universe Figure 3 1 Menu to Add a Universe gt ij Elixirsamples gt ij Public gt ig Temp ER Copy 3 Connection Pool Paste 3 DataSource Rename fe HTML Delete ff JavaScript Delete children Job Export Map Import f Report Template 8 Safe Text Universe lt gt XML amp Folder 3 Enter a name for the Universe and select the type as JDBC as shown in Figure 3 2 Adding a JDBC Universe Figure 3 2 Adding a JDBC Universe Add Universe ema on ames
22. ner Walkthrough This chapter illustrates how a Universe looks in the Universe designer and the various functionality it supports You can easily create your Universe in a full graphical setting and export it for use Universe Designer An example of the Cars Universe that is shipped with Ambience is shown in Figure 4 1 Universe Example Figure 4 1 Universe Example Tee OA LV larsunvense in 5 Lud Rows Insaeccor Summary Name T Region Once you create a Universe the relevant tables are present on the left pane You can examine the contents of each table inspect the various columns manipulate data using Transforms set various data attributes and finally export the Universe for use by Ad Hoc Dashboard and Ad Hoc Report Examining Table Structure Nama Table Column Attrbutes Attribut Transtorm You can view the columns that are in a table in the Schema tab An example is shown in Figure 4 2 Examining Table Structure Cetails Destination Universe Designer Walkthrough Figure 4 2 Examining Table Structure aI Bids A 2 Cars universe Schema Parameters E Date E Month Name Type vendor id Integer uanagian dealer_name String Ed usstate city String amp state String ZIP String area_code String phone String invoice id Integer invoice num String invoice date Date e sale date D
23. ort including the sales type column The above procedure demonstrates column security You can only view the columns that are protected if you are a user who has the access privilege set in the column security settings The whole column is protected based on username groups The following procedure demonstrates row security For this demo we use the same Universe report users and groups and their privileges as in the column security demo a b Remove the column security restriction set earlier in the column security demo Open Designer edit the Universe and set the row security for the state column to the word test Save the Universe Open Ambience and try logging in as the following users to view the report test gt can view all the rows in the table e WY gt can view only the state WY rows in the table test2 can view only the state CA rows in the table because test 2 is a member of the CA group e admin gt cannot view any rows in the table no state matches the word admin A user can belong to multiple groups for example test 2 could be made a member of the CA and AK groups and see both sets of records The above procedure demonstrates row security Rows are removed based on access rights field value To set an attribute for a column Click a column from the Schema Click the Column Attributes tab Click the Add Attribute icon e L Select the Attribute type 23 Using
24. ou can install MySQL Workbench and use com mysql jdbc Driver for the JDBC Universe Modify the host and database name in the URL field enter the user name and password and click Test Connection Figure 5 17 Configuring the Connection Pool Connection Pool Wizard Define JDBC Connection Enter JDBC datasource parameters JDBC Driver required Driver Suggestions Driver URL User Password V Test Connection Previous Next Finish Cancel 30 Using the Universe Designer If the connection test succeeds you have the option to define the Connection Pool Parameters on the next page On the Connection Pool JDBC Properties page you can set any customized properties for your JDBC driver by setting the keys and values After you complete the settings click Finish Adding Tables Click the Add Tables Ed icon Tables from the relational datasource will display inthe Add Tables window You can choose from these tables or invert your selection Click OK The tables are joined automatically You can create custom tables by using expressions Pasting Tables Click the Paste icon to copy tables from another JDBC Universe and paste them here Viewing SQL Click the SQL tab This displays the SQL that will be executed for the table Click the Load Data icon to load the table contents Other Actions All other actions that you can perform on a JDBC Universe are the same as in the s
25. ou need 13 Click Finish to add the data source to the Universe Importing a Data Source To import an existing data source into the Universe L Click the Import data source icon 2 2 Select the data source to be imported as shown in Figure 5 5 Importing a Data Source Using the Universe Designer Figure 5 5 Importing a Data Source ZZ choose Datas gt amp Adhoc s v DataSource kj ARFF ds A Binary ds i3 DBF ds 3 Excel ds B FileSystem ds B Fruit100 ds amp Fruit1000 ds O FruitSales ds 3 JDBC With Connection Po B JDBC Without Connection A Object ds Lj Random ds 3 Tabular ds A Text ds kj XML ds gt ij cars gt resource Y 4 gt Ok Cancel 3 Click OK Settings Tab The Settings tab displays the structure of the data source You can add table and column attributes to each field in the data source and add transforms to manipulate the data as desired before output Schema Tab Click the Schema tab to view the database schema of the data source An example is shown in Figure 5 6 Viewing a Schema 17 Using the Universe Designer Figure 5 6 Viewing a Schema Schema Parameters Name Type vendor_id Integer a dealer_name String city String amp state String ZIP String area_code String phone String invoice id Integer invoice num String invoice date Date ea sale date Date disposal status String dispos
26. owed to view the column or row data Make sure that the users or groups to whom you grant access have at least the SignIn Adhoc Report Viewand Adhoc Dashboard View permissions The users or groups also need access to the JDBC or Stave Universe The following is a demonstration of column security a Create either a JDBC Cars Universe or a Stave Cars Universe Row and Column security do NOT work with Repository Universe yet Add some new users and groups as follows e Users test2 WY e Groups CA Make user test 2 a member of the group CA Ensure that users admin test test2 and WY have the following privileges e SigniIn privileges e Adhoc Report View and Adhoc Dashboard View privileges e Access to the JDBC or Stave Universe 22 Using the Universe Designer Sign in to Ambience as user admin Create an Adhoc Report in Public with a Table band that contains these columns all from Cars table city dealer name sales type and state You will be able to view all columns as selected Next open up Designer edit the Universe and set the column security forthe sales type column to test 2 Save the Universe Now from Ambience as admi n refresh your report You will not be able to view the values in sales type column as they are hidden since you do not have the permission to view this column Log out of Ambience as admin and log back in as user test 2 You should now be able to view all columns in the rep
27. r an in depth explanation of transforms see the Elixir Transform User Manual An example of adding transforms is show in Figure 4 4 Adding Transforms 10 Universe Designer Walkthrough Figure 4 4 Adding Transforms Transforms e Transform Details Destination e Transform Add Transform Choose Transform Type Boolean Compare Constant Date Decimal Discard Discard Duplicates Float Integer Reduce Retain Security Sequence Sort Ez v Previous Next Finish Cancel Examining Output Data The Rows tab displays the data that will be output This output data includes entities that have been transformed by any transforms you have added An example of the data in Rows is show in Figure 4 5 Examining Output Data Figure 4 5 Examining Output Data e O amp amp Pagel of 68 33 862 Records vendor id dealer name city state Integer String String String 1 HUDIBURG MOTORS INC Midwest City OK 1 HUDIBURG MOTORS INC Midwest City OK 1 HUDIBURG MOTORS INC Midwest City OK 1 HUDIBURG MOTORS INC Midwest City OK 1 HUDIBURG MOTORS INC Midwest City OK 1 HUDIBURG MOTORS INC Midwest City OK 1 HUDIBURG MOTORS INC Midwest City OK Inspecting Column Data The Inspector tab enables you to review the contents of a column It displays column information value frequency based on the data type as well as the chart type and plot An example o
28. sing the changed data Tables whose changes must be captured usually have a column that indicates the time of last change Any row in any table that has a timestamp in that column that is more recent than the last time data was captured is considered to have changed Enter the name of the columns that contain the time of the last change This attribute is reserved for future use 2 Slowly Changing Dimension Dimension is a logical grouping of data such as customer or product information With Slowly Changing Dimension SCD data changes slowly rather than changing on a time based regular schedule For example assume you have a dimension that tracks the sales from each salesman for each region If a Salesman moves to a different region at the end of a month what happens to the sales data from the next month Should all the data including historical data for the salesman be reflected in the new region Slowly Changing Dimension takes care of these situations Type 1 This option overwrites all old data with the new Historical data is not preserved Type 2 This option preserves historical data by creating multiple records for a given natural key in the dimensional tables with separate surrogate keys and different version numbers Enter the surrogate key for type 1 along with the start and end fields for which the SCD is valid This attribute is reserved for future use 3 Static Cache For increased performance you can cache
29. the Universe Designer 5 Enter the parameters as appropriate 6 Click OK to save the attribute Adding Transforms The Transforms tab enables you to create and edit transforms to a schema Click the Add icon e b to invoke the Transform Wizard For detailed information on transform categories refer to the E1ixir Transform User Manual Rows The Rows tab displays either the original data of a table or the output of column data if there are PEE transforms created Click the Load Data icon e to display the rows An example Rows tab is shown in Figure 5 10 Rows Tab Figure 5 10 Rows Tab Settings Rows Inspector O O amp YW Pagel of 68 33 862 Records vendor id dealer_name city state ziP area code phone invoice id invoice nurr integer String String String String String String integer String l HUDIBURG MOTORS INC Midwest City OK 73110 844935 T402624 Tal i HUDIBURG MOTORS INC Midwest City OK 73110 877006 t40263a l HUDIBURG MOTORS INC Micwest City OK 73110 1015597 1313214 1 HUDIBURG MOTORS INC Midwest City OK 73110 978594 T40243A 1 HUDIBURG MOTORS INC Midwest City OK 73110 815643 t31416a E HUDIBURG MOTORS INC Midwest City OK 73110 910672 T400664 l HUDIBURG MOTORS INC Midwest City OK 73110 645172 t401 74a T HUDIBURG MOTORS INC Midwest City OK 73110 1131021 t31422a 2 HUDIBURG CHEVROLET INC Micwest City OK 73110 431130 cFC11842 2 HUDIBURG
30. tity Unit Cost Decimal Fruits Cost WeightkKG Decimal Fruits Weight Observe how Stave automatically names the Date columns once you enter a name for the Universe table for the column marked Date This occurs since Date in Stave is internally represented as multiple rows one each for Day Day of Week Month Quarter and Year Similarly Stave automatically names the Time columns once you enter a name for the Universe table for the column marked Time This occurs since Time in Stave is internally represented as multiple rows one each for Hour and Minute Operations on Stave Once you finish Mapping click the Tables tab You will see the mapped Universe table as shown in Figure 5 20 Mapped Table for a Stave Universe 32 Using the Universe Designer Figure 5 20 Mapped Table for a Stave Universe Name Type Company String Cost Decimal Date Date Day Integer ID Integer Month Integer Quantity Integer Quarter Integer Weight Decimal Year Integer 2 5 Column Attributes Attributes p 9 Name Value Click the table and proceed with the operations as specified in the section called Repository Universe 33
Download Pdf Manuals
Related Search