/*query DBAs */
--=================================================================================
/*Consulta Tamanho das Tabelas*/
SELECT
CASE
when t.NAME = '/BIC/FZSD_C23' then 'WS - Vendas Integradas Histórico'
when t.NAME = '/BIC/VZSD_C23F' then 'FACTVIEW - INFOCUBE ZSD_C23 '
ELSE '???' END as ETL,
t.NAME AS Entidade,
COUNT(DISTINCT(p.partition_id)) AS partition_id,
SUM(DISTINCT(p.rows)) AS Registros,
COUNT(DISTINCT(c.column_id)) AS Qtd_Cols,
SUM(a.total_pages) * 8 AS EspacoTotalKB,
SUM(a.used_pages) * 8 AS EspacoUsadoKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS EspacoNaoUsadoKB,
MAX(t.modify_date) AS Ultima_Atualizacao,
MAX(t.create_date) AS Ultima_Atualizacao2
FROM
sys.tables t
INNER JOIN
sys.all_columns c ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE upper(t.NAME) LIKE upper( '%logy%' )
--t.NAME NOT LIKE 'xpto%' and
--t.is_ms_shipped = 0 and
--i.OBJECT_ID > 255
GROUP BY
t.Name -- ,p.partition_id
ORDER BY Ultima_Atualizacao DESC ,
4 desc
--Registros DESC ;
--=================================================================================
/*Consulta Colunas das Tabelas like*/
SELECT distinct
t.NAME AS Entidade,
c.Name AS Coluna , c.max_length, c.precision, c.object_id
FROM
sys.tables t
INNER JOIN
--select * from
sys.all_columns c ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE upper(c.NAME) LIKE upper( '%logy%' )
--t.NAME NOT LIKE 'xpto%' and
--t.is_ms_shipped = 0 and
--i.OBJECT_ID > 255
order by 1,2
--=================================================================================
/*Consulta Global Variables
select @@VERSION ;
select @@SERVERNAME ;
select @@CONNECTIONS ;
select @@MAX_CONNECTIONS ;
select @@CPU_BUSY ;
select @@ERROR ;
select @@IDENTITY ;
select @@IDLE ;
select @@IO_BUSY ;
select @@LANGID ;
select @@LANGUAGE ;
--select @@MAXCHARLEN ;
select @@PACK_RECEIVED ;
select @@PACK_SENT ;
select @@PACKET_ERRORS ;
select @@ROWCOUNT ;
select @@SPID ;
select @@TEXTSIZE ;
select @@TIMETICKS ;
select @@TOTAL_ERRORS ;
select @@TOTAL_READ / @@TOTAL_WRITE ;
select @@TRANCOUNT ;
*/
GetAccessToken: () => let url = "https://wwwXPTO.com/Auth/GetApiToken", body = "{ ""user"": ""userXPTO"", ""pwd"": ""123$""} ", AccessToken = Json.Document(Web.Contents(url, [Headers=[#"Content-Type"="application/json"], Content=Text.ToBinary(body)])), access_token = AccessToken[token] in access_token ======================================================== GetList: let // Get_Data Query = () => let Source = Json.Document(Web.Contents("https://wwwXPTO.com/GetList", [Headers=[Authorization="Bearer " & GetAccessToken()]])), #"Converted to Table" = Record.ToTable(Source), Value = #"Converted to Table"{4}[Value] in Value in Query ======================================================== FAT_Table: let Source = GetList(), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in #"Converted to Table"
#"Changed Type2" = Table.TransformColumnTypes(dataset,{{"Date", type datetime}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Volume", type number}, {"Dividends", type number}, {"Stock Splits", type number}, {"Adj Close", type number}, {"Updated", type datetime}}),
#"Parsed Date" = Table.TransformColumns(#"Changed Type2",{{"Date", each Date.From(DateTimeZone.From(_)), type date}}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded DIM_Tickers",{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Volume", Int64.Type}}),
var AcumLastday = MAXX(FILTER(FAT_COVID_PT_Matrix, FAT_COVID_PT_Matrix[data_dados]=(EARLIER(FAT_COVID_PT_Matrix[data_dados])-1)),FAT_COVID_PT_Matrix[obitos])
There are many different ways of telling a story, but everything starts with an idea. So to help you get started we've rounded up some of the most awesome data visualisation tools available on the web.
Chart Studio from Plotly can produce some very slick graphics
Make charts, presentations and dashboards with this flexible software. You can perform your analysis using JavaScript, Puthon, R, Matlab, Jupyter or Excel, and there are several options for importing data. The visualisation library and online chart creation tool allow you to make great-looking graphics.
Anyone can use this to make sense of data from multiple services
DataHero enables you to pull together data from cloud services and create charts and dashboards. No technical abilities are required, so this is a great tool for your whole team to use.
Although armed with only six chart types, open source library Chart.js is the perfect data visualisation tool for hobbies and small projects. Using HTML 5 canvas elements to render charts, Chart.js creates responsive, flat designs, and is quickly becoming one of the most poplar open-source charting libraries. Read more about how to use Chart.js here.
Packed with graphs, charts, maps and more, Tableau Public is a popular data visualisation tool that's also completely free. Users can easily drag and drop data into the system and watch it update in real-time, plus you can collaborate with other team members for quick project turnaround.
RAWGraphs is an open web app with a simple interface
Open, customisable, and free to download and modify, RAWGraphs lets users create vector-based data visualisations. Data can be safely uploaded from apps to computers, plus it can be exported as an SVG or PNG and embedded in your webpage.
Help visitors explore dense data sets with JavaScript library Dygraphs
Dygraphs is a fast, flexible open source JavaScript charting library that allows users to explore and interpret dense data sets. It's highly customisable, works in all major browsers, and you can even pinch to zoom on mobile and tablet devices.
ZingChart lets you create HTML5 Canvas charts and more
ZingChart is a JavaScript charting library and feature-rich API set that lets you build interactive Flash or HTML5 charts. It offers over 100 chart types to fit your data.
InstantAtlas enables you to create highly engaging visualisations around map data
If you're looking for a data viz tool with mapping, InstantAtlas is worth checking out. This tool enables you to create highly-interactive dynamic and profile reports that combine statistics and map data to create engaging data visualisations.
Integrate and develop interactive maps within your site with this cool tool
Modest Maps is a lightweight, simple mapping tool for web designers that makes it easy to integrate and develop interactive maps within your site, using them as a data visualisation tool.
The API is easy to get to grips with, and offers a useful number of hooks for adding your own interaction code, making it a good choice for designers looking to fully customise their user's experience to match their website or web app. The basic library can also be extended with additional plugins, adding to its core functionality and offering some very useful data integration options.
Use OpenStreetMap data and integrate data visualisation in an HTML5/CSS3 wrapper
Another mapping tool, Leaflet makes it easy to use OpenStreetMap data and integrate fully interactive data visualisation in an HTML5/CSS3 wrapper.
The core library itself is very small, but there are a wide range of plugins available that extend the functionality with specialist functionality, such as animated markers, masks and heatmaps. It's perfect for any project where you need to show data overlaid on a geographical projection.
Billed as a "computational knowledge engine", the Google rival WolframAlpha is really good at intelligently displaying charts in response to data queries, without the need for any configuration. If you're using publicly available data, this offers a simple widget builder to make it really simple to get visualisations on your site.
Visualize Free is a hosted tool that allows you to use publicly available datasets, or upload your own, and build interactive visualisations to illustrate the data. The visualisations go well beyond simple charts, and the service is completely free, plus while development work requires Flash, output can be done through HTML5.
Orientated towards making positive change to the world, Better World Flux has some lovely visualisations of some pretty depressing data. It would be very useful, for example, if you were writing an article about world poverty, child undernourishment or access to clean water. This tool doesn't allow you to upload your own data, but does offer a rich interactive output.
A comprehensive JavaScript/HTML5 charting solution for your data visualisation needs
FusionCharts Suite XT brings you 90+ charts and gauges, 965 data-driven maps, and ready-made business dashboards and demos. FusionCharts comes with extensive JavaScript API that makes it easy to integrate it with any AJAX application or JavaScript framework. These charts, maps and dashboards are highly interactive, customisable and work across all devices and platforms.
jqPlot is a nice solution for line and point charts
Another jQuery plugin, jqPlot is a nice solution for line and point charts. It comes with a few nice additional features such as the ability to generate trend lines automatically, and interactive points that can be adjusted by the website visitor, updating the dataset accordingly.
D3.js is a JavaScript library that uses HTML, SVG, and CSS to render some amazing diagrams and charts from a variety of data sources. This library, more than most, is capable of some seriously advanced visualisations with complex data sets. It's open source, and uses web standards so is very accessible. It also includes some fantastic user interaction support.
JavaScript InfoVis Toolkit includes a handy modular structure
A fantastic library written by Nicolas Belmonte, the JavaScript InfoVis Toolkit includes a modular structure, allowing you to only force visitors to download what's absolutely necessary to display your chosen data visualisations. This library has a number of unique styles and swish animation effects, and is free to use (although donations are encouraged).
If you need to generate charts and graphs server-side, jpGraph offers a PHP-based solution with a wide range of chart types. It's free for non-commercial use, and features extensive documentation. By rendering on the server, you're guaranteed a consistent visual output, albeit at the expense of interactivity and accessibility.
Highcharts is a JavaScript charting library with a huge range of chart options available. The output is rendered using SVG in modern browsers and VML in Internet Explorer. The charts are beautifully animated into view automatically, and the framework also supports live data streams. It's free to download and use non-commercially (and licensable for commercial use). You can also play with the extensive demos using JSFiddle.
Google Charts has an excellent selection of tools available
The seminal charting solution for much of the web, Google Charts is highly flexible and has an excellent set of developer tools behind it. It's an especially useful tool for specialist visualisations such as geocharts and gauges, and it also includes built-in animation and user interaction controls.
It isn't graphically flexible, but Excel is a good way to explore data: for example, by creating 'heat maps' like this one
You can actually do some pretty complex things with Excel, from 'heat maps' of cells to scatter plots. As an entry-level tool, it can be a good way of quickly exploring data, or creating visualisations for internal use, but the limited default set of colours, lines and styles make it difficult to create graphics that would be usable in a professional publication or website. Nevertheless, as a means of rapidly communicating ideas, Excel should be part of your toolbox.
Excel comes as part of the commercial Microsoft Office suite, so if you don't have access to it, Google's spreadsheets – part of Google Docs and Google Drive – can do many of the same things. Google 'eats its own dog food', so the spreadsheet can generate the same charts as the Google Chart API. This will get your familiar with what is possible before stepping off and using the API directly for your own projects.
CSV (Comma-Separated Values) and JSON (JavaScript Object Notation) aren't actual visualization tools, but they are common formats for data. You'll need to understand their structures and how to get data in or out of them.
Crossfilter in action: by restricting the input range on any one chart, data is affected everywhere
As we build more complex tools to enable clients to wade through their data, we are starting to create graphs and charts that double as interactive GUI widgets. JavaScript library Crossfilter can be both of these. It displays data, but at the same time, you can restrict the range of that data and see other linked charts react.
Tangle creates complex interactive graphics. Pulling on any one of the knobs affects data throughout all of the linked charts
The line between content and control blurs even further with Tangle. When you are trying to describe a complex interaction or equation, letting the reader tweak the input values and see the outcome for themselves provides both a sense of control and a powerful way to explore data. JavaScript library Tangle is a set of tools to do just this.
Dragging on variables enables you to increase or decrease their values and see an accompanying chart update automatically. The results are only just short of magical.
Aimed more at specialist data visualisers, the Polymaps library creates image and vector-tiled maps using SVG
Polymaps is a mapping library that is aimed squarely at a data visualisation audience. Offering a unique approach to styling the the maps it creates, analogous to CSS selectors, it's a great resource to know about.
It isn't easy to master, but OpenLayers is arguably the most complete, robust mapping solution discussed here
OpenLayers is probably the most robust of these mapping libraries. The documentation isn't great and the learning curve is steep, but for certain tasks nothing else can compete. When you need a very specific tool no other library provides, OpenLayers is always there.
Kartograph's projections breathe new life into our standard slippy maps
Kartograph's tag line is 'rethink mapping' and that is exactly what its developers are doing. We're all used to the Mercator projection, but Kartograph brings far more choices to the table. If you aren't working with worldwide data, and can place your map in a defined box, Kartograph has the options you need to stand out from the crowd.
Carto provides an unparalleled way to combine maps and tabular data to create visualisations
Carto is a must-know site. The ease with which you can combine tabular data with maps is second to none. For example, you can feed in a CSV file of address strings and it will convert them to latitudes and longitudes and plot them on a map, but there are many other users. It's free for students; for everyone else, there are various monthly pricing plans.
Processing provides a cross-platform environment for creating images, animations, and interactions
Processing has become the poster child for interactive visualizations. It enables you to write much simpler code which is in turn compiled into Java.
There is also a Processing.js project to make it easier for websites to use Processing without Java applets, plus a port to Objective-C so you can use it on iOS. It is a desktop application, but can be run on all platforms, and given that it is now several years old, there are plenty of examples and code from the community.
NodeBox is a quick, easy way for Python-savvy developers to create 2D visualisations
NodeBox is an OS X application for creating 2D graphics and visualizations. You need to know and understand Python code, but beyond that it's a quick and easy way to tweak variables and see results instantly. It's similar to Processing, but without all the interactivity.
A powerful free software environment for statistical computing and graphics, R is the most complex of the tools listed here
How many other pieces of software have an entire search engine dedicated to them? A statistical package used to parse large data sets, R is a very complex tool, and one that takes a while to understand, but has a strong community and package library, with more and more being produced.
The learning curve is one of the steepest of any of these tools listed here, but you must be comfortable using it if you want to get to this level.
A collection of machine-learning algorithms for data-mining tasks, Weka is a powerful way to explore data
When you get deeper into being a data scientist, you will need to expand your capabilities from just creating visualisations to data mining. Weka is a good tool for classifying and clustering data based on various attributes – both powerful ways to explore data – but it also has the ability to generate simple plots.
Gephi in action. Coloured regions represent clusters of data that the system is guessing are similar
When people talk about relatedness, social graphs and co-relations, they are really talking about how two nodes are related to one another relative to the other nodes in a network. The nodes in question could be people in a company, words in a document or passes in a football game, but the maths is the same.
Gephi, a graph-based visualiser and data explorer, can not only crunch large data sets and produce beautiful visualisations, but also allows you to clean and sort the data. It's a very niche use case and a complex piece of software, but it puts you ahead of anyone else in the field who doesn't know about this gem.
iCharts can have interactive elements, and you can pull in data from Google Docs
The iCharts service provides a hosted solution for creating and presenting compelling charts for inclusion on your website. There are many different chart types available, and each is fully customisable to suit the subject matter and colour scheme of your site.
Charts can have interactive elements, and can pull data from Google Docs, Excel spreadsheets and other sources. The free account lets you create basic charts, while you can pay to upgrade for additional features and branding-free options.
Create animated visualisations with this jQuery plugin
Flot is a specialised plotting library for jQuery, but it has many handy features and crucially works across all common browsers including Internet Explorer 6. Data can be animated and, because it's a jQuery plugin, you can fully control all the aspects of animation, presentation and user interaction. This does mean that you need to be familiar with (and comfortable with) jQuery, but if that's the case, this makes a great option for including interactive charts on your website.
