Useful business analysis requires you to effectively transform data into actionable information. This book helps you use SQL and Excel to extract business information from relational databases and use that data to define business dimensions, store transactions about customers, produce results, and more. Each chapter explains when and why to perform a particular type of business analysis in order to obtain useful results, how to design and perform the analysis using SQL and Excel, and what the results should look like.
Useful business analysis requires you to effectively transform data into actionable information. This book helps you use SQL and Excel to extract business information from relational databases and use that data to define business dimensions, store transactions about customers, produce results, and more. Each chapter explains when and why to perform a particular type of business analysis in order to obtain useful results, how to design and perform the analysis using SQL and Excel, and what the results should look like.
This comprehensive and authoritative guide will teach you the DAX language for business intelligence, data modeling, and analytics. Leading Microsoft BI consultants Marco Russo and Alberto Ferrari help you master everything from table functions through advanced code and model optimization. You’ll learn exactly what happens under the hood when you run a DAX expression, how DAX behaves differently from other languages, and how to use this knowledge to write fast, robust code. If you want to leverage all of DAX’s remarkable power and flexibility, this no-compromise “deep dive” is exactly what you need. Perform powerful data analysis with DAX for Microsoft SQL Server Analysis Services, Excel, and Power BI Master core DAX concepts, including calculated columns, measures, and error handling Understand evaluation contexts and the CALCULATE and CALCULATETABLE functions Perform time-based calculations: YTD, MTD, previous year, working days, and more Work with expanded tables, complex functions, and elaborate DAX expressions Perform calculations over hierarchies, including parent/child hierarchies Use DAX to express diverse and unusual relationships Measure DAX query performance with SQL Server Profiler and DAX Studio
Get complete instructions for manipulating, processing, cleaning, and crunching datasets in Python. Updated for Python 3.6, the second edition of this hands-on guide is packed with practical case studies that show you how to solve a broad set of data analysis problems effectively. You’ll learn the latest versions of pandas, NumPy, IPython, and Jupyter in the process. Written by Wes McKinney, the creator of the Python pandas project, this book is a practical, modern introduction to data science tools in Python. It’s ideal for analysts new to Python and for Python programmers new to data science and scientific computing. Data files and related material are available on GitHub. Use the IPython shell and Jupyter notebook for exploratory computing Learn basic and advanced features in NumPy (Numerical Python) Get started with data analysis tools in the pandas library Use flexible tools to load, clean, transform, merge, and reshape data Create informative visualizations with matplotlib Apply the pandas groupby facility to slice, dice, and summarize datasets Analyze and manipulate regular and irregular time series data Learn how to solve real-world data analysis problems with thorough, detailed examples
This new edition includes some key topics relating to the latest version of MS Office, including use of the ribbon, current Excel file types, Dashboard, and basic Sharepoint integration. It shows how to automate operations, such as curve fitting, sorting, filtering, and analyzing data from a variety of sources. The book allows users to analyze data and automate the preparation of custom reports and demonstrates how to assign Excel VBA code to the new "Ribbon" user interface.
If you have large quantities of data in a Microsoft Access database, and need to study that data in depth, this book is a data cruncher's dream. Access Data Analysis Cookbook offers practical recipes to solve a variety of common problems that users have with extracting Access data and performing calculations on it. Each recipe includes a discussion on how and why the solution works. Whether you use Access 2007 or an earlier version, this book will teach you new methods to query data, different ways to move data in and out of Access, how to calculate answers to financial and investment issues, and more. Learn how to apply statistics to summarize business information, how to jump beyond SQL by manipulating data with VBA, how to process dates and times, and even how to reach into the Excel data analysis toolkit. Recipes demonstrate ways to: Develop basic and sophisticated queries Apply aggregate functions, custom functions, regular expressions, and crosstabs Apply queries to perform non-passive activities such as inserting, updating, and deleting data Create and manipulate tables and queries programmatically Manage text-based data, including methods to isolate parts of a string and ways to work with numbers that are stored as text Use arrays, read and write to the Windows registry, encrypt data, and use transaction processing Use the FileSystemObject, use XML with XSLT, communicate with SQL Server, and exchange data with other Office products Find answers from time-based data, such as how to add time, count elapsed time, work with leap years, and how to manage time zones in your calculations Deal with business and finance problems, including methods for calculating depreciation, loan paybacks, and Return on Investment (ROI) Explore statistical techniques, such as frequency, variance, kurtosis, linear regression, combinations and permutations Access Data Analysis Cookbook is a one-stop-shop for extracting nuggets of valuable information from your database, and anyone with Access experience will benefit from these tips and techniques, including seasoned developers. If you want to use your data, and not just store it, you'll find this guide indispensable.
Because the analysis of copious amounts of data and the preparation of custom reports often take away time from true research, the automation of these processes is paramount to ensure productivity. Exploring the core areas of automation, report generation, data acquisition, and data analysis, Automated Data Analysis Using Excel illustrates how to m
With the explosion of data, computing power, and cloud data warehouses, SQL has become an even more indispensable tool for the savvy analyst or data scientist. This practical book reveals new and hidden ways to improve your SQL skills, solve problems, and make the most of SQL as part of your workflow. You'll learn how to use both common and exotic SQL functions such as joins, window functions, subqueries, and regular expressions in new, innovative ways--as well as how to combine SQL techniques to accomplish your goals faster, with understandable code. If you work with SQL databases, this is a must-have reference. Learn the key steps for preparing your data for analysis Perform time series analysis using SQL's date and time manipulations Use cohort analysis to investigate how groups change over time Use SQL's powerful functions and operators for text analysis Detect outliers in your data and replace them with alternate values Establish causality using experiment analysis, also known as A/B testing
Build agile and responsive business intelligence solutions Create a semantic model and analyze data using the tabular model in SQL Server 2016 Analysis Services to create corporate-level business intelligence (BI) solutions. Led by two BI experts, you will learn how to build, deploy, and query a tabular model by following detailed examples and best practices. This hands-on book shows you how to use the tabular model’s in-memory database to perform rapid analytics—whether you are new to Analysis Services or already familiar with its multidimensional model. Discover how to: • Determine when a tabular or multidimensional model is right for your project • Build a tabular model using SQL Server Data Tools in Microsoft Visual Studio 2015 • Integrate data from multiple sources into a single, coherent view of company information • Choose a data-modeling technique that meets your organization’s performance and usability requirements • Implement security by establishing administrative and data user roles • Define and implement partitioning strategies to reduce processing time • Use Tabular Model Scripting Language (TMSL) to execute and automate administrative tasks • Optimize your data model to reduce the memory footprint for VertiPaq • Choose between in-memory (VertiPaq) and pass-through (DirectQuery) engines for tabular models • Select the proper hardware and virtualization configurations • Deploy and manipulate tabular models from C# and PowerShell using AMO and TOM libraries Get code samples, including complete apps, at: https://aka.ms/tabular/downloads About This Book • For BI professionals who are new to SQL Server 2016 Analysis Services or already familiar with previous versions of the product, and who want the best reference for creating and maintaining tabular models. • Assumes basic familiarity with database design and business analytics concepts.
This book includes step-by-step examples and case studies that teach users the many power tricks for analyzing data in Excel. These are tips honed by Bill Jelen, &“MrExcel,&” during his 10-year run as a financial analyst charged with taking mainframe data and turning it into useful information quickly. Topics include perfectly sorting with one click every time, matching lists of data, data consolidation, data subtotals, pivot tables, and much more.
