Alteryx Part 3 – Split Data Into Files

Scenario/Problem: Can I split the data that I have in a file or from another source into different files according to a particular category? See the Example of Data below: I have a Customer CSV file which has Customer Segment filed as below and I need to split this data and generate into 4 files foe each segment Corporate Home Office Small Business Consumer Solution: Using Alteryx it is very easy to split the data into Different tools Using filter but it’s not dynamic Using the output tool and configure the output Filter tool is very common method, so, I am going to demo using the output tool Step 1: […]

Alteryx Part 3 – Removing Duplicates Tips & Tricks

Scenario/Problem: I often receive Duplicate data and how do I remove them using Alteryx In This Part, we will see, how to remove them in Alteryx Solution: as far as i know , there are 4 ways we can clean up duplicate rows and they are: Using Unique Tool Using Summarize Tool Using Tile Tool Multi-Row Formula Tool Unique Tool is straight Method where you just need to select the columns to be unique Summarize Tool is also another trick where you can group the data to remove the Duplicate Tile Tool is another way where you can create the sequence then filter the data Multi Row Tool also a great tool […]

Alteryx Part 2 – Parsing Semi Structured Data

Scenario/Problem: Sometime we receive the Semi Structured Data without Header and need to parse it. See the Example of Data below 0,1,2 10,11,12,44,55,66,77,88,999 1,2,3,4,5 The Problem with this data structure is No Header Data is not there for each column Solution :  If we select this file from Input tool it will not Grab all data , it will just grab the first row and truncate the next 2 due to the semi structure like below               but don’t think there is no solution , Yes there is , we can do it without writing code but using tools in Alteryx. i took another example of customers which […]

Part 1 – Overview of Alteryx

In this Part , let’s see what components we have in Alteryx Alteryx has 2 major Components in it Alteryx Designer Alteryx Server & Gallery Designer: This is most important piece In Alteryx. Here we design the workflow, Convert to App or Create Macros. So here basically , we deal with source systems such as files,Database,Big Data or Cloud to Blend & Prepare the data and do further Analytic process then Map the output to desired format such as Excel , Database , Tableau Extract etc. Alteryx Server & Gallery: All the Workflow,Apps will published into Alteryx Server where we can schedule a workflow or User can choose their App to be executed Here, Gallery can […]

Alteryx – A New Data Blending Tool

I have been working on a new Data Blending and Analytics tool called Alteryx , perhaps its there in the industry for several year but i got a change to know about it 5 months ago. well, what i see in this tool Data Blending : Fast Data Blending for your data preparation Analytics: Advanced Analytics where you can execute your R (open Source) code and take your model Output, and also there are so many predictive analysis in built tools available for building your model Sources: It supports most File systems,Database,Cloud and BigData as well. so you can do your data blending from any of the source as quickly as […]

Extract SSIS 2012 Package Data

Extract SSIS 2012 Package Data: There are ways to query the SSIS packages definition till 2008 using package data column from msdb..sysssispackages table But in 2012, it come with both package and project model So when we deploy the package in project deployment model we deploy in “SSISDB” and “Integration Services catalogs” The deal is now, How do we grab the package definition from SSISDB tables. Unfortunately there is no straight method as the table “[SSISDB].[internal].[packages]” does not keep the definition in “package_data” Not sure why but I can see this has been already reported to Microsoft Connect What is the alternative way? Well, there are many ways to […]

Export SSAS Data using SSIS / SSAS as data source in SSIS using Script Component

In this post I am going to show you how to export cube data from SSAS data source to excel using script component To do this, We need Framework to communicate with cube which can be found at below location C:Program FilesMicrosoft.NETADOMD.NET100Microsoft.AnalysisServices.AdomdClient.dll The scenario: We are going to use Adventure work cube data for the Internet product sales Amount and tax Amount for the 2004 year using the below MDX query and load the MDX query result into Excel file This is just for an example, you can modify the query as per your need Below is the query will result the product sales and tax amount for the […]

Building BI reports using from SSAS OLAP Cube

Building BI reports using from SSAS OLAP Cube: I showed you in my previous post Tree view SSRS parameter for Analysis Services Cube. how to build a tree view parameter using ADOMD .net for SSRS reports. In this post we will see how we can build BI reports from SSAS OLAP cube in using ADOMD. The report structure is as below Use cascading parameter type listbox with Multiple selection Populate the MDX result into Grid View User can choose their option to display what parameter they want using combo box The final web page will look like below  I used SSAS DMV queries to fetch the Dimension members to populate […]

Tree view SSRS parameter for Analysis Services Cube

Tree view SSRS parameter for Analysis Services Cube.

Tree view SSRS parameter for Analysis Services Cube

Tip: After doing all Good/Hard work in creating SSAS hierarchies, you may wonder how to see them in SSRS report as same in Cube. Actually you cannot do it directly in SSRS. Well, actually there are 2 ways Using PPS (performance point services) and report viewer In this post I am going to show you how we can create hierarchy type parameter First let’s see how it looks in SSRS when you create a parameter from SSAS cube From the image above you may see that it not easy to navigate thru the hierarchy levels and user may get frustrated while running the report Solution: Create a report using […]