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

Question

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.

jithinrg · Answer

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.

DimModeller.yxzp

caltang · Answer

I will give some pointers below:

* 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. 
* 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.
* 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 · Answer

@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.