Thursday, 31 March 2016

Alteryx Engine

  • The Alteryx Engine, which is written in C++, executes the workflows that are built in the Alteryx Designer and produces the output. 
  • The Engine supports direct connections to various data sources for accessing the data and then processes it in-memory during the execution of the workflow.
  • Processing that exceeds memory limitations is written to temp files on disk, which are deleted once the processing is complete. 
  • The Engine can be entirely self-contained in an Alteryx Designer deployment, scaled across an organization via the Alteryx Server, or deployed in the cloud via the Alteryx Analytics Gallery.
Depending on the workflow and process, the Alteryx Engine may:              
 1.Read or write to input/output files.
 2.Read or write to one or more databases.
 3.Upload or download data from the web.
 4.Submit email to an email server through SMTP protocols.
 5.Execute external runtime commands.

  • The Alteryx Engine seamlessly integrates with R. When users choose to install the suite of R-based tools and macros used for predictive analysis, Alteryx also installs the R program and the additional packages that provide connectivity between Alteryx and R and are required for the tools and macros to work. 
  • Users can use R scripts or the R-based macros to process data directly within a workflow. 
  • When the workflow is run, the Alteryx Engine communicates with the R engine via a command line executable to process and send back the data.


  • Developers can write applications that call directly into the Alteryx Engine using the Application Programming Interfaces (APIs) and the Alteryx Software Development Kit (SDK) gives developers an easy way to add their own programs to the Alteryx toolbox.
  • Both the Alteryx API and SDK are available with an Alteryx Server deployment.
  • Whether the Alteryx Engine is running on the desktop, through the API, or via the Alteryx Service, workflows may leverage tools that communicate with databases, output files,web services, email servers, or run commands.
Thanks Guru's,
Prazval.ks




Wednesday, 30 March 2016

Alteryx Components

The Alteryx Analytics platform offers two Components: 
1)Alteryx Designer
2)Alteryx Server

1)Alteryx Designer:-

 Alteryx Designer is a Windows based application that provides an intuitive drag-and drop user interface for users to create repeatable workflow processes.
  • Users can drag tools from a toolbox onto a canvas, connect them together, and edit their properties to create Alteryx workflows, apps, and macros.
  • Users can use these workflows to blend and enrich data from a range of sources, perform advanced analytics, and quickly produce results that can be easily shared with others.
  • Alteryx Designer is written mostly in C# and executes the workflows through a local instance of the Alteryx Engine

2)Alteryx Server:-
Alteryx Server is a web-based application that provides a comprehensive and scalable server-based analytics solution that provides end users and business decision makers the ability to share and run analytic applications using a web browser.
  • In an Alteryx Server deployment, the Scheduler interface component within Alteryx Designer allows users to schedule workflows to be executed at predetermined times or specific recurring intervals.
  • The Scheduler then communicates with the Alteryx Service where jobs are queued for execution at the appropriate time.
  • In addition to Designer and Engine components, the Alteryx Server includes Service and Gallery components
  • Users may use Alteryx Designer to publish their workflows, apps, and macros to the Alteryx Analytics Gallery where others can have access to running or downloading them. 


Thanks Guru's,
Prazval.ks

Friday, 25 March 2016

Tableau Processing PipeLine


Thanks Guru's,
Prazval.ks

Tableau Visual Cues

Application Terminology:-


Visual Cues for Fields:-


 

Visual Cues for Fields on Shelves:-



Thanks Guru's,
Prazval.ks

Thursday, 24 March 2016

Creating a Calendar with NO Calculations


1. Right click on the date field and duplicate it
2. Drag Date to Columns, choose M/Y as format
3. Drag Date to Rows, choose Week as format




4. Drag Date to Columns, choose Weekday as format
5. Drag Date to Rows, choose Week as format

6. Change Mark type to Square 


7.Right click-drag Date (Copy) to Columns, choose Weekday as format
8.Right click-drag Date (Copy) to Rows, choose Week as format
9.Filter to a single month
10.Increase the size of the squares until they fill the grid
11.Uncheck “Show Header” on all pills except MY(Date) and resize the view so the grid is made up of squares.
12.Format as desired
13.Expected Results as below:-



Thanks Guru's,
Prazval.ks


Duplication of Records in Tableau


Cardinality may affect the results returned when joining tables because joining tables that have one-to-many or many-to-many relationships can have the effect of duplicating values or multiplying rows by the number of times a primary key’s match appears in the secondary table.

In the diagram below you can see the left table has a one-to-many relationship with the right table because there are multiple prescriptions for the same patient ID. When these tables are joined, the result table shows that for every time Patient ID 395959 is matched with a prescription in the right table, the values from the left table duplicated the same number of times. Although the payment due for Patient 395959 should be $2000, aggregating the values in the result table produces the incorrect amount of $6000 ($2000 x 3 or the number of times the patient ID appears in the right table).


Solution:-
Tableau offers several solutions for addressing duplication of records when joining tables that have a one-to-many or many-to-many relationship. 

1)Changing the aggregation from SUM to AVG, MIN, or COUNTD may sometimes resolve the issue. 
2)Other times, creating a calculated field, using a table calculation or choosing to blend the data will provide a solution. 3)Custom SQL is another way to remove duplicates. 

The technique you use is dependent upon the data as well as what you are trying to accomplish.

Thanks Guru's,
Prazval.ks


Friday, 18 March 2016

Nested Table Calculations behaviour

A nested table calc is when one table calc is used inside another table calc, which is different from writing a calculation that uses two table calculation functions independently.  This is an important distinction because a nested table calc will allow you to compute the parent table calc separately from the child table calc, whereas a calculation containing two independent table calculation functions will act as a single table calc when determining how to compute the table calc,

In the Below Example,we are having two table calculations
1)Lookup   2)Index
While addressing  we need to set adressing Options individually for both the functions.



Thanks Guru's,
Prazval.ks

Visualization Best Practises


1)Avoid the clutter by storing the information in the tooltip.





2) Format the Tool Tips into Descriptive way for fast and easier legibility

3)Add Benchmarks(Reference Lines) to identify when the data traverses the zero line




4) Remove the unnecessary decimal points to reduce clutter



5)Hide the Axis if it is of no importance!!


6)Always place the labels into tooltips to get better visual effects



7)Always fix the axis tick marks to reduce the amount of information on the chart


8) Always Highlight Top colorsif we want to point out top performers by using Visual grouping(or)Sets 


9)Use the independent axis option to show of variability.


10)Always Color  the visualization with the Measures


11)Color the visualization with KPI’s for effective comparisons.


Thanks Guru's,
Prazval.ks

Wednesday, 16 March 2016

Display Username,Viz Creation time&Users who never logged into server

.    Using Tableau Desktop and the Tableau Server Repository, create a viz that displays a user’s name, the number of minutes since it was created, and indicate those who have never logged in (in gray below).





Steps:-

1.    Using the ‘readonly’ user to login to the tableau server database.
2.    In the data source window,join the following tables:



3.    Joins can be done based on the following images:



4.    Go into a new sheet.
5.    Create a calculated field that identifies the number of minutes that have passed between the time the user was created and now. 




6.    Build a customized Viz that looks like the following: 

7.    Done!


Thanks Guru's,
Prazval.ks

Tuesday, 15 March 2016

Connecting to the Tableau Server Repository

Challenge:-
Enable external connections to the Tableau Server Repository and, using Tableau Desktop, create a Viz showing the most popular workbooks in terms of Users and Views. 




Steps:-

1.    The Tableau Server repository is configured to block external connections by default. We can use 2 accounts to connect to the repository, ‘tableau’ and ‘readonly’, and these can be enabled with the following commands:

tabadmin dbpass --username tableau p@ssword
tabadmin dbpass --username readonly p@ssword

More details about this process can be found on this article.

2.    Open a new instance of Tableau Desktop and open a new ‘PostgreSQL’ connection:



More details about this connection can be found here.

3.    In the Data Source tab, bring in the ‘_views_stats’ and ‘_workbooks’ tables, join them by ‘workbook_id’ as indicated below:



4.Go into a new sheet and build a customized viz.


5.Done!!!

Thanks Guru's,
Prazval.ks

Thursday, 10 March 2016

Table Calculation Functions

1.WINDOW,RUNNING FUNCTIONS:-

WINDOW functions perform aggregations on a selected range of data that is included in the structure of the view. The range is set by negative integers moving backward from a point and positive integers moving forward from a point. The arguments FIRST() and LAST() as well as parameters and other fields can also be used to include all data from a given point to the beginning or end of the data set.





2.LOOKUP:-

LOOKUP looks forward (positive integers) or backward (negative integers) a specified number of dimension values in the view.  Backward and forward can be determined either by the structure of the view (i.e., left to right or top to bottom) or by a specified measure to sort on.





3.INDEX&SIZE:-

INDEX counts 1, 2, 3,… up through however many values there are in a given dimension or combination of dimensions.  
SIZE tells you the number of values in a given dimension or combination of dimension.  You can think of SIZE as the maximum INDEX value.




Thanks Guru's,
Prazval .ks

Groups and User Creation using TABCMD




Using TABCMD
·      Create four additional groups: Billing Users, Billing Manager, Facilities Users, and Facilities Manager.
·      Create all of the Billing and Facilities users using the “BILLFAC” CSV file.
·      Assign the Billing users to the Billing Users group using the “BILLUSERS” CSV file.
·      Assign the Billing manager to the Billing Manager group using the “BILLMGR” CSV file.
·      Assign the Facilities users to the Facilities Users group using the “FACUSERS” CSV file.
·      Assign the Facilities manager to the Facilities Manager group using the “FACMGR” CSV file.
·      You can find the files by following the directory path: C:\Program Files\Tableau
·      Link to the list of available Tabcmd Commands: http://onlinehelp.tableau.com/current/server/en-us/tabcmd_cmd.htm



Solution:-
·      Create Billing Users Group:
o  "C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" creategroup "Billing Users"

·      Create Billing Manager Group:
o  "C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" creategroup "Billing Manager"

·      Create Facilities Users Group:
o  "C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" creategroup "Facilities Users"

·      Create Facilities Manager Group:
o  "C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" creategroup "Facilities Manager"

·      Create all of the Billing and Facilities users:
o  "C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" createusers "c:\Program Files\Tableau\USERS\BILLFAC.csv"

·      Assign the Billing users to the Billing Users group:
o  "C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" addusers "Billing Users" --users "c:\Program Files\Tableau\USERS\BILLUSERS.csv"

·      Assign the Billing manager to the Billing Manager group:
o  "C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" addusers "Billing Manager" --users "c:\Program Files\Tableau\USERS\BILLMGR.csv"

·      Assign the Facilities users to the Facilities Users group:
o  "C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" addusers "Facilities Users" --users "c:\Program Files\Tableau\USERS\FACUSERS.csv"

·      Assign the Facilities manager to the Facilities Manager group:
o  "C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" addusers "Facilities Manager" --users "c:\Program Files\Tableau\USERS\FACMGR.csv"

FULL SCRIPT:
"C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" login -s http://localhost:80 -u admin -p admin > installLog.txt

"C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" creategroup "Billing Users"

"C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" creategroup "Billing Manager"

"C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" creategroup "Facilities Users"

"C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" creategroup "Facilities Manager"

"C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" createusers "c:\Program Files\Tableau\USERS\BILLFAC.csv"

"C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" addusers "Billing Users" --users "c:\Program Files\Tableau\USERS\BILLUSERS.csv"

"C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" addusers "Billing Manager" --users "c:\Program Files\Tableau\USERS\BILLMGR.csv"

"C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" addusers "Facilities Users" --users "c:\Program Files\Tableau\USERS\FACUSERS.csv"

"C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" addusers "Facilities Manager" --users "c:\Program Files\Tableau\USERS\FACMGR.csv"

"C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" logout >> installLog.txt

Thanks Guru's,
Prazval.ks

Wednesday, 9 March 2016

Project Creation and Content Publishing using TABCMD

Using TABCMD
·      Create two  projects for following two departments:
Billing and Facilities.
·      Publish up the following pieces of content found in the directory path: C:\Program Files\Tableau
o  The “InstantRendering” workbook belongs to the Billing department.
o  The “MultivariateAnalysis” workbook belongs to the Facilities department.
o  The “COGS” and “Coffee Chain” data sources belong to the Data Sources project.
·      Link to the list of available Tabcmd Commands: http://onlinehelp.tableau.com/current/server/en-us/tabcmd_cmd.htm


Solution:-

·      Create Billing Project:
o  "C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" createproject -n "Billing Project" -d "This project will hold all of the content related to the Billing department."

·      Create Facilities Project:
o  "C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" createproject -n "Facilities Project" -d "This project will hold all of the content related to the Facilities department.”

·      Publish “InstantRendering” workbook to the Billing Project:
o  "C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" publish "C:\Program Files\Tableau\WORKBOOKS\InstantRendering.twbx" -o -r "Billing Project"

·      Publish “MultivariateAnalysis” workbook to the Facilities Project:
o  "C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" publish "C:\Program Files\Tableau\WORKBOOKS\MultivariateAnalysis.twbx" -o -r "Facilities Project"

·      Publish “COGS” and “Coffee Chain” data sources to the Data Sources Project:
o  "C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" publish "C:\Program Files\Tableau\DATASOURCES\COGS.tds" -o -r "Data Sources Project"

o  "C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" publish "C:\Program Files\Tableau\DATASOURCES\Coffee Chain.tds" -o -r "Data Sources Project"

FULL SCRIPT:
"C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" login -s http://localhost:80 -u admin -p admin > installLog.txt

"C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" createproject -n "Billing Project" -d "This project will hold all of the content related to the Billing department." >> installLog.txt

"C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" createproject -n "Facilities Project" -d "This project will hold all of the content related to the Facilities department." >> installLog.txt

"C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" publish "C:\Program Files\Tableau\WORKBOOKS\InstantRendering.twbx" -o -r "Billing Project" >> installLog.txt

"C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" publish "C:\Program Files\TableauWORKBOOKS\MultivariateAnalysis.twbx" -o -r "Facilities Project" >> installLog.txt

"C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" publish "C:\Program Files\Tableau\DATASOURCES\COGS.tds" -o -r "Data Sources Project" >> installLog.txt

"C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" publish "C:\Program Files\Tableau\DATASOURCES\Coffee Chain.tds" -o -r "Data Sources Project" >> installLog.txt


"C:\Program Files (x86)\Tableau\Tableau Server\9.0\bin\tabcmd.exe" logout >> installLog.txt

Thanks Guru's,
Prazval.ks

Finding Student Best Subject

Lets achieve this using LOD's.  For this, We need to create 3 calculations, to find out the best scoring subject of each student ....