cbcc52ea

Alter Snowflake with Dynamic SQL and Python

Thu, 17 Dec 2020 14:09:39 GMT

Adding Python to Alteryx can make your Snowflake dreams come true

Anyone who follows me knows how much I love the combination of Alteryx and Snowflake. Alteryx provides a logic layer that enables any analyst to manipulate data and Snowflake provides the scale needed to get some amazing insights across large data sets. However, I have found there were always a few things missing. The Alteryx In-DB tools, while comprehensive, cannot do everything. There are some gaps (most notably - ahem - any Alteryx product managers reading this - simple Pivot or Unpivot tools in-DB). There is also a lot of functionality like changing warehouses, schemas or databases that cannot be easily done from within Alteryx.

Enter the python tool

Now , I am more business and less code . I muddle through code when I have to , and I finally decided this was something I had to do. I had to be able to use a mix of custom SQL with the native in-db tools on S nowflake. I was pleasantly surprised that this was WAY easier th a n I expected with building some custom macros that take advantage of the S nowflake - P ython connectors.

To Start with , t he newer versions of Alteryx with integrated jupyter notebooks help a lot!

When you drop a P ython tool on to the Alteryx canvas , this notebook will open up. You can enter in your code (or for us non-coders - find the code on stack overflow and paste it in :) )

This code loads the packages that are required to execute code against S nowflake. As a note, you may get an error when loading these. If you do, try to run Alteryx as an administrator on your machine. You also may get success by adding install_type=User to your command (package = "snowflake-sqlalchemy", install_type=User ). Right clicking on Alteryx designer and running as administrator is what worked for me.

from ayx import Package

from ayx import Alteryx

Alteryx.installPackages(package="snowflake-connector-python")

Alteryx.installPackages(package="snowflake-sqlalchemy")

I am using the standard code samples from snowflake on how to set up your connections ; With one little trick :) .

As your see , we are creating a variable object , inputs , which will read the data stream #1 that I drag into my input tool. This data stream will contain the basic information about the S nowflake account. We can set these values using interface tools as part of a macro or analytic app.

The code sfUser = (inputs['username'].values[0]) sets that variable to the first row of the column 'username'.

The rest below is just the standard S nowflake connector syntax. You will know it is working as we have requested S nowflake to send the current version (which you will see in your P ython tool or in your A lteryx messages when you run this in a workflow . )

from ayx import Alteryx

import snowflake as sf

inputs = Alteryx.read("#1")

sfUser = (inputs['username'].values[0])

sfAccount = (inputs['account'].values[0])

sfPswd = (inputs['password'].values[0])

from sqlalchemy import create_engine

engine = create_engine(

'snowflake://{user}:{password}@{account}/'.format(

user = sfUser,

password = sfPswd,

account = sfAccount,

)

con = sf.connector.connect(

user = sfUser,

password = sfPswd,

account = sfAccount,

)

sfq = con.cursor()

inputs2 = Alteryx.read("#2")

for index, row in inputs2.head().iterrows():

sfq.execute(row['SQL'])

print(row['SQL']+" executed")

con.close()

engine.dispose()

Now for the dynamic part

What we are doing here is creating a second box for the SQL script we want to run.

The command sfg.execute('WILL EXECUTE THIS SQL SCRIPT'). This problem with this is it only accepts one statement at a time.

That said, we can read in multiple SQL statements and use a very simple loop ; to loop through each statement and execute it.

inputs2 = Alteryx.read("#2")

for index, row in inputs2.head().iterrows():

sfq.execute(row['SQL'])

print(row['SQL']+" executed")

con.close()

engine.dispose()

This means we can input individual SQL statements and execute them in an Alteryx workflow

Thats a wrap!

Now , it ' s just a question of connecting a few interface tools. I am splitting the SQL statement into rows using a semicolon to mirror the effect of writing out a long SQL statement. The input and output tools on the bottom are really just there to give this macro a seamless in-db feel. We could very easily tweak the P ython macro to return a S nowflake select statement as a data frame and have an output from the macro as well. However, the purpose of this macro i s to automate some administrative tasks in a workflow. I can now have complete command of my S nowflake environment from within Alteryx, and I can easily build up a suite of macros to deal with common tasks or execute anything remotely in S nowflake from Alteryx!

Now in Action!

Full code below:

Why You Need Real Time Right Now

Mon, 02 Nov 2020 12:28:09 GMT

Time is the enemy of forecasting

In our previous post, we discussed why d emand forecasting matters and how an accurate demand forecast can mathematically and quantitatively improve your service level and/or inventory levels (cost). Now it is time to focus on the biggest uncertainty of them all . Time.

Advanced forecasting algorithms can help eliminate uncertainty through reducing variables that are “unknown” by understanding relationships at a scale that no human could comprehend. This helps turn the seemingly “unknown” in to a “known”. As we stated earlier, advances in math and cloud computing make finding these relationships much easier and can be computed almost instantaneously. However, the element of time is a tricky compounding factor. Even little unknowns become big over time as time compounds all errors and introduces more uncertainties.

The most certainty you have is in the precise moment you make an estimate. When you look ahead 1 day, 1 week or even 1-month (as most corporate S&OP) cycles still do – then you must start to rely less and less on facts and more and more on assumptions. Assumptions mean uncertainty and uncertainty means cost.

There are loads of examples of this. When I lived in Florida, every September during hurricane season , we would get the dreaded cone of uncertainty any time a hurricane formed. As below shows , the further out you get, the expected position gets wider. By day 5 - the uncertainty stretched over 500 miles – hardly accurate enough to do any planning of significance .

Would you wait 1 week for a forecast update on this?

The models that make up this track are changing all the time, based on hundreds of new factors like wind shear, temperature and pressure. If you lived in Florida, you would not want to wait a week to have this forecast updated, right? At this point the storm would have passed, so your only option would be to prepare (cost) or hope for the best (risk). Well this is exactly what happens in most companies. An aggregate demand (say a weekly bucket) gets rolled into a monthly forecast, which then gets discussed in an S&OP meeting (usually 2 – 3 weeks after the month has ended) . The downstream factories and suppliers then get wide ranges to plan against, which they ultimately fill with safety stock inventory or padded delivery dates.

Enter Real time forecasting (instant ML)

If you could look in real-time, everyday there are hundreds of variables influencing your demand. Conditions can change rapidly which requires models to be rebuilt based on live data (real sales, real stock availability, real weather conditions, promotions, pricing…. the list goes on). We have shown that , w ith machine learning , we can model many of these, but a static model is not great either (look what happened to all those companies that had rigid statistical forecasting models in the time of COVID). To obtain the best possible projection of demand, c ompanies need to work on data as close to real-time as possible and recompute models based on what is happening now, not what we think might happen in a week or even what happened last week.

Let ' s look at a real example of a recent customer and a new product launch. This was a new produc t with little history , and the launch team made some best estimates of launch demand. As you can see , with instant ML we are able to remodel and re - predict the forecasts using the most real-time data from the launch. This provides a quick consensus of models and enables resources to be diverted to meet true demand. Sticking with the original model for longer would have created an almost certain inventory impact and huge bullwhip across the supply chain. Using real-time data ensures that we are keeping our variability down as low as possibl e, which enables us to keep our service level high and our cost down.

We closed off our last post with the following thought...

Know what you do not know costs you. (and there is a pretty accurate equation for this)

Well if you are reforecasting with an out of date model, or you are reforecasting without the latest available data - you are definitely leaving information that you know out of your model and forecasts. This is costing you money and/or impacting your service.

Next, we will start to explore how technologies can close these gaps – and make sure that you are providing your supply chain with the most accurate demand. That means leveraging all the variables you have (at the lowest possible level) when predicting your demand and doing it as fast and as often as you can.

In Supply Chain, Maths is cool again

Mon, 14 Sep 2020 13:43:12 GMT

Why use maths?

You cannot go far these days without hearing about data science, digital transformation or AI. This is especially true in the supply chain discipline. However, when taking it back to basics, what AI, ML and Data Science are really doing are solving mathematical equations that are hardly new. These equations are just trying to optimize a series of variables to minimize cost and maximize service – something supply chain professionals have been doing for decades. So what has changed? Cloud Computing and Cloud Storage!

Cloud Computing is enabling supply chain professionals to perform more math, faster.

Cheap Storage is providing the ability to store more data about products and environmental variables which gives more fuel for the math to solve.

Maths solve variables. Variables = Risk = Cost

One of my very first managers simplified this in one of my first meetings about supply chain design as an Intern at American Home Products (now many mergers later – part of Pfizer). He said “If I know what supply I am getting today , and I know exactly what I am selling tomorrow, m y inventory should be zero”.

Now , we all know that is unfortunately not the case – and all companies in the supply chain inevitably end up with inventory. However , unpacking that statement a little further all he was saying was that inventory is a buffer for variability (also known as risk). We need inventory because we don’t know what we are going to get today (supply) , and we certainly don’t know what we are going to sell tomorrow (demand). It's really all a constant balance between supply and demand. On the supply side, we use inventory to create some buffer and on the demand side we can manage service expectations and line fill rate.

Less data = more cost

In the pharmaceutical industry – where I learned the supply chain trade, there were often some very high consequences for not having enough stock, ranging from lost sales on very high margin products to adversely affecting human health. When I was doing planning, we were reliant on supply chain segmentation – usually we classified products as A, B or C based on their importance to the company and/or society. In other words – never run out of an A product... ever . B products - bad news but survivable. C products - just get it back in stock quickly. What struck me was how much excess inventory was always piled on to A products, even products that had years of history showing stable demand and stable supply. The lack of data and inability to process it just meant products got grouped with inventory and safety policies into 3 categories - even across 100s or 1000s of products. This may have worked (at times) but it failed to take into account that inventory equal s cos t. Having more than you need costs your business something, either through obsole sce nce risk, cost of capital, cost of storage or cost of capacity. Now sometimes that cost is justified , but often it is not. However , the good news is that this can be solved for.

The inventory, supply, demand, service relationship

Our equation above is a more complex version of the first equation. It is the cumulative distribution function for a normally distributed equation and models random demand variability fairly well. σ is your variability (how good your forecast is) and x is expected deviation from a forecast and f(x) is the probability of that deviation happening. To simplify = if you know your variability , you can precisely determine the probability of having a certain amount of stock in the future.

This means if you wanted to ensure that your service level was 98% , and you can model your supply and demand with a variability of σ , we can say exactly how much inventory is needed. This also means that if we reduce variability, we can reduce inventory and get the same service level. Solving these kinds of equations can be quite difficult by hand, but they are pretty easy for computers and can be solved with a few lines of code at an impressive speed and scale.

Forget A,B,C segments - segment everything

What this means is the idea of having supply chain segments is changing. We don’t need to classify items into A, B, C categories anymore. In fact, one can calculate target inventory for every item in your organizatio n, at every step in the supply chain…..independently. All you need to know is how much variability there is in supply and in demand and what availability you are comfortable with. The math will take care of the rest.

Get Variability down

A common saying I have heard throughout my career (in supply chain and otherwise) is: You don’t know what you don’t know . This is very true, but it is best to start with what you do know. This means build out your demand forecasts with as much data as you can. There are tools which can take this data and use it to lower your variability by training demand forecasting algorithms across vast numbers of iterations and combinations of factors. This enables the deconstruction of even the most seemingly random events into mathematically solvable equations with increasingly small degrees of error.

On the demand side , things like:

Seasonality and hyper-seasonality (yearly and weekly or even daily trends)
Events (promotions, holidays, advertising)
External Factors (weather, traffic, competitor activity)

On the supply side things like:

Failure rates
Shipping lanes
Frequency of production

By modeling what has happened in the past and overlaying what you know about the future (Christmas does happ en every year and around the same time :), we can start to apply what we know to our expectations - which lowers our overall variability.

Whats next?

Demand Data has partnered with Tangent Works, a leading machine learning time-series company. We are busy working with a number of CPG and retail companies to take all their data and leverage Tangent Works' TIM engine to create a new breed of time-series forecasts (either demand or supply) that go beyond traditional statistical methods to incorporate the features of each product and link in your supply chain. The results are improved forecast accuracy but most importantly forecasts that are modeled for each unique aspect in your supply chain. Over the coming weeks, we will show you how to use the TIM engine to lower your variability and how to do it at speed, which will overcome another one of life's great variables....time.

By James Smith - Demand Data

Weather Based Marketing with Alteryx and Snowflake

Mon, 14 Sep 2020 10:30:57 GMT

Getting Started:

This post will give you all the tools and details on how to use weather to drive business/sales to your organization. The high level process will involve taking locations of interest (in our sample, we will use retail locations of major chains) and then establish a custom weather criteria. The output is a list of locations meeting that criteria upon which an action can be taken. We will explore actions in a later post but such actions include things like: creating a custom facebook territory for a campaign, sending customers targeted weather specific offers, inputting into a retailers target stock program, adjusting your stores forecast....the list goes on).

Pre-requisites

To proceed from here , you will need to have the following:

1) Alteryx designer --> Free trial here

2) Snowflake account --> Free trial here

3) Setup Alteryx for data exchange --> post here

4) Request the following data sets in the S nowflake data exchange --> Global retail data, weather sample set (Search www. demanddata.io or email us for links!)

How it all works

Connect to the weather sample set in Alteryx , and the first step is to get an overview of all of the weather stations that are available. You can do this with a group by station id, latitude, longitude and stream that into Alteryx. This allows us to bring a minimal amount of data into our local environment. You can do the same with your locations of interest- these can be anything with a lat/long pair (or do some Geocoding --> future post on this soon!). Make sure you put some point tools on your coordinates to turn them into spatial objects and then use a find nearest tool to map all your locations to a weather station. Then , use a summarize tool or a unique tool to get back unique weather stations. What we have effectively done here is mapped all our locations of interests (our stores, our customers, our suppliers etc) to a weather station. We can put in some confidence limits around how far away a station is but I've found in the US a 20 mile radius captures most of the population. ( O f course we could use Alteryx to PRECISELY calculate this number)!

Now that this is done , we can push the relevant weather stations back to S nowflake with a data stream in tool. Here we use a temporary table and join back to our main weather day. Effectively , we have done a nice little geospatial filter in S nowflake with just 3 tools in Alteryx! We have also effectively mapped all o f our locations of interest to a weather node (on which we have history and a live forecast through S nowflakes data exchange). From here we have some options- we can get our weather history and do some modelling (in a later post we will show how to use weather history to improve sales forecasting) , but f or our first introduction to this topic , we are going to work only with the forecast. Our data set contains a json file with the next 15 days of forecasts.

Now again back to options - if you are good with SQL , you can use the flatten functions within S nowflake to flatten all this data out, however , I , personally , find it much easier to do with Alteryx and the json parse capabilities which make unpacking and figuring out this data fairly painless. If going the Alteryx route, you need a json parse tool to bring all the elements out, then a text parse tool to expand out fields . Then you can use a cross tab tool to pivot all your elements and fields into a more manageable flattened structure. By sorting based on our forecast date and us ing a unique tool , we bring in the latest forecast only for each city or weather station id.

Now , we just need to deal with the conversions i n the different fields. In this dataset, temperature is all based in degrees Kelvin. For those of you who forgot your primary school chemistry, Kelvin is just Celsius re-based to absolute zero. It ' s quite a simple formula to convert , just subtract 273.15. (Why 273.15? You are going to have to talk to a physics PhD for that one , I'm afraid). For the Americans who adopted Fahrenheit with its easy to remember numbers like freezing water at 32 and boiling water at 212 , that conversion is just multiply by 9/5 and add 32. Finally , time ; all the coders will recognize the time field as unix time , but for the rest of us , it ' s just the number of seconds that have occurred since the epoch (really the beginning of time for computers) which at some point was arbitrarily decided at Jan 1st, 1970. Apparently , the 60's didn ' t really exist (for unix developers anyway). A nifty Alteryx trick here is to use the multi-field formula , so you can apply the same temperature formulas to many fields at once.

Now that we have all the weather stations that are close to our locations of interest , all that is left to do is bring back the metadata about our locations. As a note here , we will likely have multiple locations for the same weather conditions but that is to be expected. You may have 100 locations in central London for example , which may all be mapped to a single weather condition station. From here , you can see the forecast against all of your locations of interest, or you can take it up a notch and make this dynamic with a few analytic app anchors. In this case, we have taken all of the different fields and mapped them to allow a user to select locations that fit certain weather criteria.

So what these means is you can establish a user based criteria for rain or drought, hot or cold, windy or calm, clear or cloudy and run this app to produce all the locations of interest that meet this certain criteria at some point in the next 14 days. We have hooked this up to a simple report tool and for example , shown all the Tesco stores in the UK that are expected to get 30mm of rain on any given day. Bad news in the southwest UK towards the end of September.

So from here - it is really up to you to think creatively about how this type of automated data can be used to drive your business. With the weather history we mentioned earlier maybe you have used machine learning to predict which products sell well on hot and sunny days, or that consumers who are stuck indoors on rainy days use your service or visit your locations more. Then its just a question of creating live predictions of when these conditions will exist so that you can ensure your inventory is adequately stocked, or your store is adequately staffed or that you are positioning your product online at just the right moment. The Alteryx APA platform can make all of this possible with easy integrations. Increase target stock or target staffing at locations in your ERP systems - no problem. Create a custom Facebook territory and upload it directly into Facebook audience manager (easy). Send a custom email campaign with the perfect product for the upcoming weekend - just join on postcode and create custom campaign audiences for your marketing teams. So umbrella salespeople in Southampton - be ready :)

Connect Snowflake with Alteryx

Thu, 13 Aug 2020 15:02:07 GMT

Alteryx + Snowflake = Awesomeness

The flexibility and scale of Snowflake enables any user to bust through any misconceptions about data availability or data size. However, it does require a good deal of SQL knowledge to get insights and is more suited to engineers that analysts as direct users. When paired with Alteryx, the power of the Snowflake data engine plus all of the data sets in the Snowflake marketplace become instantly accessible in an intuitive drag and drop interface.

Getting your PC ready

I promise this will be the last admin step needed to achieve unlimited data superpowers. It is annoying I admit, but a small step compared to what you will achieve. The snowflake drivers do not come standard with windows machines so they have to be downloaded and installed. Note this process as to be repeated on all machines that will connect to snowflake (including server if you are lucky enough to have alteryx server)

The link is below (for a windows 64 bit machine). If you are using a 32-bit machine, please read the advisory on the main page and start a gofundme account to try and score a new laptop before attempting to continue. If you are using linux then I am certain you are not reading this step-by-step guide. If you are using a mac, you can afford to pay someone to do this for you and cant use Alteryx anyway (which I understand is a heated debate but best taken up elsewhere)

https://sfc-repo.snowflakecomputing.com/odbc/win64/latest/index.html

Making the connection

You can connect Alteryx to databases using both the regular input tool or the in database tool. For simplicity , I am focusing on the in-DB tools. The reality is the in-DB can easily perform the functions of the regular tool plus much more , so I recommend us ing them always when connecting to databases. Under in-database, click through to connect In-DB. Under datasource , you should see S nowflake as an option (if not review the first step).

File or User connection?

Decision time. Windows (and therefore Alteryx) allows for 2 types of ODBC connections, one is user based (where the setup is stored with the user of the machine) and the other is file based (where the credentials are stored in a file that can be shared). Personally , I like the file option because it ' s easily transportable (to a server for instance) or to share with a fellow analyst. However , this may give your security team the total creeps , so admittedly , the more secure way is a user based connection. Either way , choose your option . I f you choose the file based method , you will need to specify a folder where the connection details will go.

This being W indows there are...well..a lot of windows. You will need to create your machine based connection to S nowflake first.

From here you need to enter in your parameters . There are a few gotchas here to understand.

1) Datasource - this is the name of the datasource (eg. SNOWFLAKE_CENSUS)

2) User/password - for your S nowflake account

3) Server - this is your S nowflake URL - WITHOUT the https://

4) Database, Schema - these are the DEFAULT locations when you sign-in. These can be changed but if not explicitly changed in a workflow will default here .

5) Warehouse - this is the compute resource this connection will use. This can ' t be changed (easily anyway) so I highly recommend you use a warehouse that is always on extra-small. If someone increases its size and you are happily blasting away with Alteryx you can get some bill surprises .

6) Role. This also can ' t easily be changed. I strongly suggest you don ' t use accountadmin for this unless this is your own personal playground - then you own the toys play with them as you wish :)

7) Tracing. This can be blank but I recommend 4 (minimal logging) .

8) Authenticator/Proxy/No-Proxy - Leave this all blank. If you are using multi-factor and/or proxy servers lets hope you are not following this step by step guide. ( I f you are then email us and we can certainly help with setting this up) .

Now , you will be asked to configure a write driver. THIS IS IMPORTANT . WAKE UP NOW!

If you plan on writing little or no data to S nowflake from Alteryx , then select same as read driver here. When I say little I typically mean 1000 rows at a time or less. This is because standard ODBC drivers insert into databases one row at a time. This is super inefficient even for S nowflake. If you do same as read driver and later decide you want to write 1,000,000 rows into snowflake with Alteryx you are

a) GOING TO BURN LOADS AND LOADS OF CASH

b) GOING TO THINK SNOWFLAKE IS HORRIBLE AND REGRET READING THIS BLOG

c) WILL GO BACK TO EXCEL AND LOSE ALL HOPE OF A CAREER IN DATA

If you do want to load lots of data into S nowflake fear not and use the bulk loading option. At the time of this writing, bulk loading requires an s3 bucket and account. However , there are alternative options in late stage beta and Demand Data has specifically built loaders for Azure and GCP (contact us for interest). Setting up the s3 loader in self-explanatory but a little tricky. If you get stuck , contact us and we can help.

**TECHNICAL ASIDE**** Read if you care. The bulk loader works by zipping your data into chunks and compressing it and uploading it into a temporary storage and then triggering S nowflake to read this data directly into the engine. It is amazingly efficient H ow efficient? We can load from Alteryx 1,000,000 rows (1GB) in under 2 minutes. Using the standard loader, this would take 24 hours or more.

PHEW - Now you are done with that. You should be able to pull up a list of tables and connect to one using standard SQL or the visual query builder.

Basic Census Analytics with Alteryx

Thu, 13 Aug 2020 14:56:57 GMT

US Census has details on 230,000 areas in the US

Now that you have connected to the census data, you can begin to explore it in huge detail using Alteryx. In this post, we can use the spatial tools to find local information about any area or zip code in the US.

The following link contains the workflow in Alteryx. It is based on a connection to the safegraph data set in the data exchange post .

US_POPULATION_BY_BLOCK

If you have followed the previous posts and setup a file connection, all you need to do is go to Options-->Advanced Options-->Workflow Dependencies and replace the my connection file with the file you setup to connect to your instance.

From her e, I'll walk through the basic workflow and then we can do a deeper dive into how this data set is structured so we can ask more questions.

WORKFLOW WALKTHROUGH

The top leg of the workflow starts with the census questions. This contains all the questions on the 2010 census. We first filter the questions to get questions related to population "TABLE_ID" LIKE B0110%e%. Snowflake uses standard SQL syntax meaning that the % symbol is a wildcard. This says give me all tables starting with B0110 then that have an "e" and then any other value after that. B0110 are all the population related questions and the e means estimate. You can also get m meaning margin of error.

On the bottom leg of the workflow we are going to connect to the responses for the B01 table (this has the answers to each question) - the top leg has the questions themselves. Here we can select the questions we are interested in. In the example it's just total population. But you could easily add other questions like male population between the ages of 22 to 24 by selecting B01001E10, as an example .

After each leg , we will use the datastream out tool to bring the result set into Alteryx. On the bottom le g, we need a simple pivot to get the question_id as a row instead of a field (header). J oining will bring the question into context of the answer. Then with the select tool we just choose the question context and now we can an answer by census block.

Now a census block isn ' t very useful to us without context, b ut there is a census block metadata table as well in the data source, w hich has details on each census block. That table for reference is "METADATA_CBG_GEOGRAPHIC_DATA"

Downloading this data and joining it back by census group gives you population as well as coordinates and the land area and water area for each census group.

Adding a create points tool from the spatial object bar enables us to transform this data into a map object visible through any browse tool.

Now that is absurdly detailed!! And remember we have details on this level for over 7,000 individual questions from the census. In future posts we will show how to build this up to aggregate trade areas around your specific locations of interest.

Open your store with Alteryx

Thu, 13 Aug 2020 14:49:39 GMT

Data sets and Workflows used in Alteryx Webinar

This post contains the content and workflows used in the opening your store with Alteryx webinar. These data sets will enable you to replicate and expand on the work to help retailers assess the impact of COVID on their business.

The full package of data and workflows is available in the attached zip file or go through and review piece by piece. ZIP

Get COVID Cases

The workflow for covid cases is attached below:

COVID WORKFLOW

The prerequisite data sets for this are:

1) Snowflake data exchange share - starschema dataset (see connecting to `S nowflake ) OR feel free to plug in data from Johns Hopkins which is available HERE (might need some tweaking)

2) Census shape files - available HERE

3) Census 2019 population estimate - available HERE

GET Mobility Data

The workflow for mobility data is attached:

MOBILITY WORKFLOW

The prerequisites data sets for this are:

1) Google mobility index

Get Population by Block

This is easy! Just look at the other post --> http://www.demanddata.io/basic-census-analytics

Bring it all together

The link to this workflow:

Bring it all together

Prerequisites for this workflow.

1) Connection to D emand D ata retailer database (See post on connecting via S nowflake) - or import your own locations of interest!!

2) Mobility to Geo mapping table (maps the name of each county to its coordinates) --> HERE

3) Employee Data (this is dummy data but connect your own!!) --> HERE

4) US zipcodes all match to coordinates. All US zip codes and their geographic metadata --> HERE

Then you need the ouputs from the first 3 workflows as well.

Getting Started with Snowflake Data Exchange

Wed, 12 Aug 2020 11:17:06 GMT

Get Live Data from Snowflake Data Exchange

In this post, we will run through how to connect your existing Snowflake account to the Snowflake data exchange or set up a trial so you can immediately start accessing live external data for your analytics.

Setup a new account:

If you already have access to a S nowflake account , congratulations! You can skip these steps. If not , go to trial.snowflake.com. On the below page, indicate your edition (Enterprise is the correct choice for 95% of applications) and your chosen cloud. Within minutes , you will receive an email with your account id. It looks something like XX1234-us-west.azure.snowflake.com. When you activate the link, create your account admin username and password , and you are ready to go.

Once your account is setup , on the worksheet tab, my recommendation is to immediately execute the following code:

CREATE WAREHOUSE MY_COMPUTE_WAREHOUSE;

ALTER WAREHOUSE MY_COMPUTE_WAREHOUSE SET WAREHOUSE_SIZE = "X-Small", AUTO_SUSPEND = 60;

This will create a brand new compute warehouse and set it to the smallest size with the minimum auto-off (after 60 seconds). Unless you are dealing with huge data sets, for the type of investigation we will be doing these settings will ensure you get the most of your $400 in free credit (and if you stick to an extra-small warehouse you will easily get a full month of querying in!)

Connect and Search the Data Exchange

From your main page, just hit the marketplace logo and begin to search through all the data providers that are available.

From here , make sure you have selected account admin role and select your listing to subscribe to it. Note that standard listings are available immediately. Y ou may need to set the role to ACCOUNTADMIN to subscribe. Subscribing to a p ersonalized listing require s some interaction with the provider and may or may not have a separate subscription fee. I n the image below , we are going to use this data set provided by S afegraph to power some of next steps.

Once you have done that, back in S nowflake you will immediately see all the tables in a new database in S nowflake (you may have to refresh . )

Then in line with best practice , r un this line of code to allow for a non accountadmin to use the database. (It is advised that you limit the number of people who use accountadmin role and do not use it as a role for querying data . )

GRANT IMPORTED PRIVILEGES ON DATABASE "SAFEGRAPH_AZURE_WESTEUROPE_SAFEGRAPH_CENSUS_DATA" TO ROLE SYSADMIN

From here , you are ready to query this data, however as we believe in no code/low code , the next post will show you how to connect to this data from Alteryx and the fun will begin!

cbcc52ea

Alter Snowflake with Dynamic SQL and Python

Adding Python to Alteryx can make your Snowflake dreams come true

Enter the python tool

Now for the dynamic part

Thats a wrap!

Now in Action!

Full code below:

Why You Need Real Time Right Now

Time is the enemy of forecasting

Enter Real time forecasting (instant ML)

We closed off our last post with the following thought...

In Supply Chain, Maths is cool again

Why use maths?

Maths solve variables. Variables = Risk = Cost

Less data = more cost

The inventory, supply, demand, service relationship

Forget A,B,C segments - segment everything

Get Variability down

Whats next?

By James Smith - Demand Data

Weather Based Marketing with Alteryx and Snowflake

Getting Started:

Pre-requisites

How it all works ﻿

Connect Snowflake with Alteryx

Alteryx + Snowflake = Awesomeness

Getting your PC ready

Making the connection

Basic Census Analytics with Alteryx

US Census has details on 230,000 areas in the US

WORKFLOW WALKTHROUGH

Open your store with Alteryx

Data sets and Workflows used in Alteryx Webinar

Get COVID Cases

GET Mobility Data

Get Population by Block

Bring it all together

Getting Started with Snowflake Data Exchange

Get Live Data from Snowflake Data Exchange

Setup a new account:

﻿

Connect and Search the Data Exchange﻿

How it all works

Connect and Search the Data Exchange