How we can create an alteryx app for modelling flat tables?

[jithinrg]

Hi

I would like to create an app for creating dimensions and facts table from a single flat table for reporting purpose.

Can any one help me out how to develop it? Input and output should be dynamic enough to create/generate csv or excel or into/from sql database.

Please see the requirement snapshot below. 

Any help on this is greatly appreciated.

[caltang]

Kindly provide some raw data and then we in the community can help you out. An Analytic App is what you are looking at - based on your criteria, it is not difficult to do.

[jithinrg]

Hi @caltang , For the demo purpose, we can take the sample superstores data. But this app should be a common solution for these kind of flat files.

Sample - Superstore Sales (Excel).xls (tableau.com)

[caltang]

Please find the sample attached.

You can use the magic wand next to Run to see how it works. 

I've made the Orders data into a YXDB so that it is easier and faster for Alteryx to read.

If this is what you want, then you can replicate the interface tool with the Select Tool as much as you want to create your fact tables.

If you are stuck and wish for more help, ping me. 

Hope this helps.

[jithinrg]

@caltang , many thanks for the quick turn around.

I tested the workflow, but it is not summarizing and populating the primary keys for each records.

Also the workflow should be dynamic to take any kind of flat tables like this, and it should not be configured only for sample super stores.

I'm new to alteryx and doesn't know how to use the macro for iterative process.

[caltang]

I will give some pointers below:

1. If you are using Alteryx Server or Analytic Apps as an interface for users to extract raw data for their reporting, it would be a better idea to understand what sort of reports that they want and build a specific report analytic app with that data source. 
2. I understand that you want something that is applicable to all flat files (even with Superstore dataset as a base), but it is not possible because an Analytic App is a tailored solution to automate for your users. You will have to design the analytic app based on the data schema provided to you.
3. You mentioned macro for iterative process, but based on your screenshot, your use of Analytic Apps is more so as an interface for users to extract fact data and aggregated data for reporting/analysis. I do not see how iterative macros play into this part.

This may not be what you want to hear, but an analytic app must be carefully curated to solve your needs. 

The sample I made for you shows you how you can use the interface tools, more specifically the List Box, to choose your fields. This does not generate a primary key out of thin air as it is dependent on your dataset. For the super store dataset, the primary key has already been established with the Order ID field. 

Maybe, instead of what you are asking, perhaps you can ask your end users what sort of reports / data file requests that they wish to automate? Then from there, design your analytic app to suit their needs. 

Underlying fact is, you need to design based on your own dataset. Once you have done so, you may ask this question again in Designer thread and then the community can better assist you. 

Unfortunately for now, we cannot help you more if the above is not addressed. I hope for your kind understanding. 

[jithinrg]

hi @caltang , thank you for your pointers. I have created a draft workflow to summarize the columns based on selection with a batch macro. The problem is if we select multiple columns to create separate dimension files, all will get appended into the single table. How can we output each into different files..?

Attached the workflow for your reference.