Microsoft sql server 2014 business intelligence development beginners guide free download. Microsoft SQL Server 2014 Business Intelligence Development Beginner’s Guide
Looking for:
Microsoft SQL Server Business Intelligence Development Beginner's Guide | Packt - Publisher resources |
Microsoft sql server 2014 business intelligence development beginners guide free download
He has worked with large enterprises around the world and delivered highquality data warehousing and BI solutions for them. He has worked with industries in different sectors, such as Health, Finance, Logistics, Sales, Order Management, Manufacturing, Telecommunication, and so on.
Reza has written books on SQL Server and databases. His blog contains the latest information on his presentations and publications. Reza is a Mentor and a Microsoft Certified Trainer. He has been in the professional training business for many years. He conducts extensive handedlevel training for many enterprises around the world via both remote and inperson training.
Smartphones and tablets. It syncs automatically with your account and allows you to read online or offline wherever you are. It will also be handy for BI program managers and directors who want to analyze and evaluate Microsoft tools for BI system implementation. Instructions often need some extra explanation so that they make sense, so they are followed with:. This heading explains the working of tasks or instructions that you have just completed. You will also find a number of styles of text that distinguish between different kinds of information.
Here are some examples of these styles, and an explanation of their meaning. Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: Expand the Chapter 02 SSAS Multidimensional database and then expand the dimensions.
New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: On the Select Destination Location screen, click on Next to accept the default destination.
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop. Open navigation menu. Close suggestions Search Search. User Settings. Skip carousel. Carousel Previous. Carousel Next. What is Scribd? Explore Ebooks. Bestsellers Editors' Picks All Ebooks. Explore Audiobooks. Bestsellers Editors' Picks All audiobooks.
Explore Magazines. Editors' Picks All magazines. Explore Podcasts All podcasts. Difficulty Beginner Intermediate Advanced. Explore Documents. Enjoy millions of ebooks, audiobooks, magazines, and more. Start your free days Read preview.
Publisher: Packt Publishing. Released: May 26, ISBN: Format: Book. Written in an easy-to-follow, example-driven format, there are plenty of stepbystep instructions to help get you started!
The book has a friendly approach, with the opportunity to learn by experimenting. This book is will give you a good upshot view of each component and scenarios featuring the use of that component in Data Warehousing and Business Intelligence systems. About the author RR. Related Podcast Episodes. Their patented technology has been used by more Nate started his career in as an accountant. Along his way, Nate encountered a mentor who introducing him to data warehousing and business intelligence.
He instantly realized that Mark brings up using Livebook as a Business Intelligence tool for doing analysis of a running application's data. Single Source of Truth: In mathematics, truth is universal. In data, truth lies in the where clause of the query.
As large organizations have grown to rely on their data more significantly for decision making, a common problem is not being able to agree on what the Craig is an ambitious leader with a proven track record for delivering major projects including data management, production, and business intelligence solutions.
On this episode, Craig and Cindi discuss the life- and cost-saving benefits of leveraging data to improve decision making in healthcare, how moving from financial services to healthcare has given Craig a more holistic view of what's possible with data, why an individual should never stop learning and broadening their skills at any age, and establishing beneficial relationships with vendors that make you partners in each others' success.
Key Takeaways: There's nothing like a life-changing event to demonstrate how data from the patient's perspective can be used. Craig demonstrated how he was able to research options for his own heart bypass surgery informed by the available data. Always broaden your s by The Data Chief 60 min listen.
Data Exploration For Business Users Powered By Analytics Engineering With Lightdash: An interview with Oliver Laslett about the open source Lightdash framework for business intelligence and how it builds on the work that your analytics engineers are doing with dbt. Think too hard about it, and you might actually find yourself Data Discovery From Dashboards To Databases With Castor: An interview about how the Castor platform approaches the problem of data discovery and preserving context for your organization.
What can you do with it? Ismail, the CTO and co-founder of Hingeto, a Y-combinator funded and fast-growing Silicon Valley startup, shares how they use business intelligence James is truly a unique guest as he credits much of his Grace also shares her take on why a liberal arts education is valuable in technology industries, plus how data can help marketers create personalized and impactful customer experiences.
Key Takeaways: Include your customers in the design process. Changing the design process to be consultative, collaborative, and more of a conversation with the customer ensures that the end result meets their needs. Such a minor mindset shift can lead to exceptional results. Critical thinking matters. In such a rapidly changing field as marketing, being able to intuitively bridge the gap between knowledge and expertise has become an even more valuable skill.
With some simple tips, you can cultiva by The Data Chief 41 min listen. To kick off this new podcast, we wanted to switch the roles… today, Cindi is our guest!
Tune in for a discussion between Cindi and Ian Faison, Executive Producer of The Data Chief, about culture, leadership, innovation, data for good, and what you can expect from future episodes of The Data Chief. Key Takeaways: How being an aspiring writer — but a lousy typist — got Cindi into technology.
The biggest changes Cindi has witnessed over the last 20 years. Where the new role of CDO Chief Data Officer fits into the hierarchy among the CIO and CAO, the barriers someone in this position can expect to face, and the ways we can expect this role to evolve in the months and years to come. Jonathan Sharr is the kind of story that keeps us going! Since then he went They discuss the role of data analytics in customer personalization, driving business, and creating a competitive advantage.
Astrato is a data analytics and business intelligence tool built on the cloud and for the cloud. Alexander discusses the features and capabilities of Astrato for Related Articles. Related categories Skip carousel. Free access for Packt account holders Instant updates on new Packt books Preface What this book covers What you need for this book Who this book is for Conventions Time for action — heading What just happened? Reader feedback Customer support Downloading the example code Downloading color versions of the images for this book Errata Piracy Questions 1.
Time for action — creating the first cube What just happened? Time for action — viewing the cube in the browser What just happened? Dimensions and measures Time for action — using the Dimension Designer What just happened? Time for action — change the order of the Month attribute What just happened?
Time for action — modifying the measure properties What just happened? Time for action — using a Named Query What just happened? Using dimensions Time for action — adding a Fact relationship What just happened? Hierarchies Time for action — creating a hierarchy What just happened?
Time for action — calculated members What just happened? Time for action — processing the data What just happened? Summary 3. Time for action — creating measures What just happened?
Creating hierarchies Time for action — creating a hierarchy from a single table What just happened? Time for action — creating a hierarchy from multiple tables What just happened? Data Analysis eXpression, calculated columns, and measures Time for action — using time intelligence functions in DAX What just happened? Securing the data Time for action — security in tabular What just happened?
Storage modes Time for action — creating a model with the DirectQuery storage mode What just happened? The Data Flow tab Time for action — loading customer information from a flat file into a database table with a Data Flow Task What just happened? Containers and dynamic packages Time for action — looping through CSV files in a directory and loading them into a database table What just happened? Summary 5.
Creating models and entities Time for action — creating a model and an entity What just happened? Time for action — creating an entity with data from the Excel Add-in What just happened? Time for action — change tracking What just happened? The entity relationship Time for action — creating a domain-based relationship What just happened?
Business rules Time for action — creating a simple business rule What just happened? Working with hierarchies Time for action — creating a derived hierarchy What just happened? Security and permission Time for action — permission walkthrough What just happened? Integration management Time for action — a subscription view What just happened? Time for action — entity-based staging What just happened? Summary 6.
Knowledge discovery Time for action — knowledge discovery What just happened? Domain and composite domain rules Time for action — composite domain rules What just happened? Synonyms and standardization Time for action — creating synonyms and setting standardization What just happened?
Matching Time for action — matching policy What just happened? Time for action — matching projects What just happened? Microsoft association rules Time for action — the Microsoft association rule What just happened?
Algorithm parameters Summary 8. Summary 9. Extended report development Parameters Time for action — adding parameters to a report What just happened? Printing and page configuration Time for action — changing a page's properties What just happened? Sorting and grouping Time for action — applying ordering and grouping on the data rows What just happened?
Expressions Time for action — changing the background color of data rows based on expressions What just happened? Adding charts Time for action — working with charts in Reporting Services What just happened? Deploying and configuring Time for action — deploying a report What just happened? Time for action — using Report Manager What just happened?
Summary The dashboard pages Time for action — creating a dashboard page What just happened? PPS dashboard's on-the-fly features Time for action — exploring on-the-fly features What just happened? Filters Time for action — working with filters What just happened?
Time for action — creating the first Power View dashboard What just happened? Map Time for action — geographical data visualization using Power View What just happened?
Scatter chart Time for action — visualizing time-based information with a scatter chart What just happened? Integrating Reports in Applications Designing.
Working with ReportViewer in a local processing mode Time for action — designing reports and working with the local processing mode What just happened?
Passing parameters to a report Time for action — changing a report configuration with a ReportViewer Object through code behind What just happened? Using the results of a mining model in an application Time for action — running DMX queries from a. NET application What just happened? ISBN www. Why subscribe? Fully searchable across every book published by Packt Copy and paste, print and bookmark content On demand and accessible via web browser Free access for Packt account holders If you have an account with Packt at www.
Instant updates on new Packt books Get notified! What this book covers Chapter 1, Data Warehouse Design , explains the first steps in thinking and designing a BI system. Conventions In this book, you will find several headings that appear frequently. To give clear instructions on how to complete a procedure or task, we use: Time for action — heading Action 1 Action 2 Action 3 Instructions often need some extra explanation so that they make sense, so they are followed with: What just happened?
Note Warnings or important notes appear in a box like this. Tip Tips and tricks appear like this. Reader feedback Feedback from our readers is always welcome. Start your free days. Rate as 1 out of 5, I didn't like it at all. Rate as 2 out of 5, I didn't like it that much.
Microsoft SQL Server Business Intelligence Development Beginner's Guide | Packt.Google Kitaplar
This is not a good beginners guide. It does not explain why all these steps are being done. I tried the sample book before purchasing; it read with promise. However, the sample really oversells it. I don't recommend this book. I agree about the grammar errors but overall a great book if your starting out and trying to understand Business Intelligence. The title Says but many of the samples are sql Well-written, but note that it piggybacks on the publicly available Microsoft tutorial.
This book really impressed me. Thank you for writing this. See all reviews. Top reviews from other countries. Very good book. Worth buying. Best book for basic level understanding. Report abuse. Your recently viewed items and featured recommendations. Back to top. Get to Know Us. Make Money with Us. Amazon Payment Products. Let Us Help You. Amazon Music Stream millions of songs. Amazon Advertising Find, attract, and engage customers. Amazon Drive Cloud storage from Amazon. Alexa Actionable Analytics for the Web.
Sell on Amazon Start a Selling Account. AmazonGlobal Ship Orders Internationally. ComiXology Thousands of Digital Comics. DPReview Digital Photography. Shopbop Designer Fashion Brands. Then, only a key to that table would be pointed from the customer table. In this way, every time the value Remuera changes, only one record in the geographical region changes and the key number remains unchanged. So, you can see that normalization is highly efficient in transactional systems.
This normalization approach is not that effective on analytical databases. If you consider a sales database with many tables related to each other and normalized at least up to the third normalized form 3NF , then analytical queries on such databases may require more than 10 join conditions, which slows down the query response.
In other words, from the point of view of reporting, it would be better to denormalize data and flatten it in order to make it easier to query data as much as possible. This means the first design in the preceding table might be better for reporting. However, the query and reporting requirements are not that simple, and the business domains in the database are not as small as two or three tables. So real-world problems can be solved with a special design method for the data warehouse called dimensional modeling.
There are two well-known methods for designing the data warehouse: the Kimball and Inmon methodologies. The Inmon and Kimball methods are named after the owners of these methodologies. Both of these methods are in use nowadays. The main difference between these methods is that Inmon is top-down and Kimball is bottom-up. In this chapter, we will explain the Kimball method. Both of these books are must-read books for BI and DW professionals and are reference books that are recommended to be on the bookshelf of all BI teams.
This chapter is referenced from The Data Warehouse Toolkit , so for a detailed discussion, read the referenced book. To gain an understanding of data warehouse design and dimensional modeling, it's better to learn about the components and terminologies of a DW.
A DW consists of Fact tables and dimensions. The relationship between a Fact table and dimensions are based on the foreign key and primary key the primary key of the dimension table is addressed in the fact table as the foreign key. Facts are numeric and additive values in the business process. For example, in the sales business, a fact can be a sales amount, discount amount, or quantity of items sold.
All of these measures or facts are numeric values and they are additive. Additive means that you can add values of some records together and it provides a meaning. For example, adding the sales amount for all records is the grand total of sales.
Dimension tables are tables that contain descriptive information. Descriptive information, for example, can be a customer's name, job title, company, and even geographical information of where the customer lives. Each dimension table contains a list of columns, and the columns of the dimension table are called attributes. Each attribute contains some descriptive information, and attributes that are related to each other will be placed in a dimension.
For example, the customer dimension would contain the attributes listed earlier. Each dimension has a primary key, which is called the surrogate key. The surrogate key is usually an auto increment integer value. The primary key of the source system will be stored in the dimension table as the business key. The Fact table is a table that contains a list of related facts and measures with foreign keys pointing to surrogate keys of the dimension tables.
Fact tables usually store a large number of records, and most of the data warehouse space is filled by them around 80 percent. Grain is one of the most important terminologies used to design a data warehouse. Grain defines a level of detail that stores the Fact table. For example, you could build a data warehouse for sales in which Grain is the most detailed level of transactions in the retail shop, that is, one record per each transaction in the specific date and time for the customer and sales person.
Understanding Grain is important because it defines which dimensions are required. There are two different schemas for creating a relationship between fact and dimensions: the snow flake and star schema. In the start schema, a Fact table will be at the center as a hub, and dimensions will be connected to the fact through a single-level relationship. There won't be ideally a dimension that relates to the fact through another dimension.
The following diagram shows the different schemas:. The snow flake schema, as you can see in the preceding diagram, contains relationships of some dimensions through intermediate dimensions to the Fact table.
If you look more carefully at the snow flake schema, you may find it more similar to the normalized form, and the truth is that a fully snow flaked design of the fact and dimensions will be in the 3NF. The snow flake schema requires more joins to respond to an analytical query, so it would respond slower.
Hence, the star schema is the preferred design for the data warehouse. It is obvious that you cannot build a complete star schema and sometimes you will be required to do a level of snow flaking. However, the best practice is to always avoid snow flaking as much as possible. After a quick definition of the most common terminologies in dimensional modeling, it's now time to start designing a small data warehouse. One of the best ways of learning a concept and method is to see how it will be applied to a sample question.
Assume that you want to build a data warehouse for the sales part of a business that contains a chain of supermarkets; each supermarket sells a list of products to customers, and the transactional data is stored in an operational system. Our mission is to build a data warehouse that is able to analyze the sales information.
Before thinking about the design of the data warehouse, the very first question is what is the goal of designing a data warehouse?
What kind of analytical reports would be required as the result of the BI system? The answer to these questions is the first and also the most important step. This step not only clarifies the scope of the work but also provides you with the clue about the Grain.
Defining the goal can also be called requirement analysis. Your job as a data warehouse designer is to analyze required reports, KPIs, and dashboards. After requirement analysis, the dimensional modeling phases will start.
Based on Kimball's best practices, dimensional modeling can be done in the following four steps:. In our example, there is only one business process, that is, sales. Grain, as we've described earlier, is the level of detail that will be stored in the Fact table. Based on the requirement, Grain is to have one record per sales transaction and date, per customer, per product, and per store.
Once Grain is defined, it is easy to identify dimensions. Based on the Grain, the dimensions would be date, store, customer, and product. It is useful to name dimensions with a Dim prefix to identify them easily in the list of tables. The next step is to identify the Fact table, which would be a single Fact table named FactSales. This table will store the defined Grain. After identifying the Fact and dimension tables, it's time to go more in detail about each table and think about the attributes of the dimensions, and measures of the Fact table.
Next, we will get into the details of the Fact table and then into each dimension. There is only one Grain for this business process, and this means that one Fact table would be required.
To connect to each dimension, there would be a foreign key in the Fact table that points to the primary key of the dimension table. The table would also contain measures or facts. For the sales business process, facts that can be measured numeric and additive are SalesAmount, DiscountAmount, and QuantitySold. The Fact table would only contain relationships to other dimensions and measures. The following diagram shows some columns of the FactSales :.
As you can see, the preceding diagram shows a star schema. We will go through the dimensions in the next step to explore them more in detail. Fact tables usually don't have too many columns because the number of measures and related tables won't be that much. However, Fact tables will contain many records. The Fact table in our example will store one record per transaction. As the Fact table will contain millions of records, you should think about the design of this table carefully.
The String data types are not recommended in the Fact table because they won't add any numeric or additive value to the table. The relationship between a Fact table and dimensions could also be based on the surrogate key of the dimension. The best practice is to set a data type of surrogate keys as the integer; this will be cost-effective in terms of the required disk space in the Fact table because the integer data type takes only 4 bytes while the string data type is much more.
Using an integer as a surrogate key also speeds up the join between a fact and a dimension because join and criteria will be based on the integer that operators works with, which is much faster than a string. If you are thinking about adding comments in this made by a sales person to the sales transaction as another column of the Fact table, first think about the analysis that you want to do based on comments.
No one does analysis based on a free text field; if you wish to do an analysis on a free text, you can categorize the text values through the ETL process and build another dimension for that. Then, add the foreign key-primary key relationship between that dimension to the Fact table. The customer's information, such as the customer name, customer job, customer city, and so on, will be stored in this dimension. You may think that the customer city is, as another dimension, a Geo dimension.
But the important note is that our goal in dimensional modeling is not normalization. So resist against your tendency to normalize tables. For a data warehouse, it would be much better if we store more customer-related attributes in the customer dimension itself rather than designing a snow flake schema.
The following diagram shows sample columns of the DimCustomer table:. The DimCustomer dimension may contain many more attributes. The number of attributes in your dimensions is usually high. Actually, a dimension table with a high number of attributes is the power of your data warehouse because attributes will be your filter criteria in the analysis, and the user can slice and dice data by attributes.
So, it is good to think about all possible attributes for that dimension and add them in this step. As we've discussed earlier, you see attributes such as Suburb , City , State , and Country inside the customer dimension. This is not a normalized design, and this design definitely is not a good design for a transactional database because it adds redundancy, and making changes won't be consistent.
However, for the data warehouse design, not only is redundancy unimportant but it also speeds up analytical queries and prevents snow flaking. The CustomerKey is the surrogate key and primary key for the dimension in the data warehouse. The CustomerKey is an integer field, which is autoincremented. It is important that the surrogate key won't be encoded or taken as a string key; if there is something coded somewhere, then it should be decoded and stored into the relevant attributes.
The surrogate key should be different from the primary key of the table in the source system. There are multiple reasons for that; for example, sometimes, operational systems recycle their primary keys, which means they reuse a key value for a customer that is no longer in use to a new customer. CustomerAlternateKey is the primary key of the source system. It is important to keep the primary key of the source system stored in the dimension because it would be necessary to identify changes from the source table and apply them into the dimension.
The primary key of the source system will be called the business key or alternate key. The date dimension is one of the dimensions that you will find in most of the business processes. There may be rare situations where you work with a Fact table that doesn't store date-related information.
This is obvious as you can fetch all other columns out of the full date column with some date functions, but that will add extra time for processing. So, at the time of designing dimensions, don't think about spaces and add as many attributes as required. The following diagram shows sample columns of the date dimension:. It would be useful to store holidays, weekdays, and weekends in the date dimension because in sales figures, a holiday or weekend will definitely affect the sales transactions and amounts.
So, the user will require an understanding of why the sale is higher on a specific date rather than on other days. You may also add another attribute for promotions in this example, which states whether that specific date is a promotion date or not. The date dimension will have a record for each date. The table, shown in the following screenshot, shows sample records of the date dimension:.
As you can see in the records illustrated in the preceding screenshot, the surrogate of the date dimension DateKey shows a meaningful value. This is one of the rare exceptions where we can keep the surrogate key of this dimension as an integer type but with the format of YYYYMMDD to represent a meaning as well.
In this example, if we store time information, where do you think would be the place for time attributes? Inside the date dimension? Definitely not. The date dimension will store one record per day, so a date dimension will have records per year and records for 10 years.
View all page feedback. In this article. Object Explorer. Use Template Explorer to build and manage files of boilerplate text that you use to speed the development of queries and scripts. Template Explorer. Use the deprecated Solution Explorer to build projects used to manage administration items such as scripts and queries.
Solution Explorer. Use the visual design tools included in Management Studio to build queries, tables, and diagram databases. Visual Database Tools. Use the Management Studio language editors to interactively build and debug queries and scripts.
Comments
Post a Comment