MSBI (Microsoft Business Intelligence) is the powerful suite tool developed by Microsoft which offers best solutions for Business Intelligence and Data-Mining Queries. This tool uses Microsoft visual studio data tools + SQL Server. It provides different processes for different tools for BI solutions.
MSBI stand for Microsoft Business Intelligence. This intense suite is made out of tools which help in giving best answers for Business Intelligence and Data Mining Queries. This tool utilizes Visual studio alongside SQL server. It basically means the process of understanding the complex data work, allocating it, analyzing it and setting up a proper report that will help in business decisions. BI stands for Business Intelligence, refers to set of techniques or tools which helps in searching, finding and analyzing best data or relevant info out of huge data for good business decision making. SQL Server Analysis Services (SSAS) SQL Server Integration Services (SSIS) SQL Server Reporting Services (SSRS) Performance Point Services (PPS) SSSAS stands for SQL Server Analytical Services. SSAS analyze service, SSAS analyze from the stored data. This analyzing process done with a online analytical process component and data mining capabilities. For analyze process it construct a multi dimensional structures called cubes. SSIS stands for SQL server Integration Services. It is the data-warehousing arm of the SQL Server 2008 R2 suite — equipped with superior Extract, Transform and Load (ETL) capabilities. It provides the vehicle for moving data from different data sources to another and changing the data, if necessary. SSRS stands for SQL Server Reporting Tool. SSRS is a reporting service, now represent analyze data in a graphical way SSRS issued. SSRS generate reports; analyze data, Plans, Scorecards, Dash boards, Excel etc.SSRS 2005 had two separate services. SSRS 2008 is easier to configure, deploy and manage without losing any functionality. SSRS Shares internal components with SQL Server. It enables better memory management capabilities. It has Supports single instance, multi instance and Scale Out. PPS stands for PerformancePoint Services. It is a SharePoint Server 2013 service application. It enables users to create business intelligence (BI) dashboards that provide insight into an organization’s performance. You can create custom reports, filters, tabular data sources, and scorecard transforms to extend the native functionality of PerformancePoint Services. Data warehouse is commonly used for reporting and business analysis purposes. This system is actually the output of integrated data from multiple sources and stored into a centralized repository. The Data warehouse stores the current and historical data, so it is easy to generate trend reports, predictive analysis and comparison reports. It’s very helpful for the top management to take the quick decisions about the business. A Data Mart means that it’s a small part of a Data Warehouse and indicates only a single part (for example sales or finance). It always holds more summarized information. Rs.exe utility is used for organizing the report on report server. It comes with the report server and can be modify accordingly. Aggregate: It applies aggregate functions to Record Sets to produce new output records from aggregated values. Audit: Adds Package and Task level Metadata – such as Machine Name, Execution Instance, Package Name, Package ID, etc.. Charactermap: Performs SQL Server column level string operations such as changing data from lower case to upper case. Conditionalsplit: Separates available input into separate output pipelines based on Boolean Expressions configured for each output. Copy column: Add a copy of column to the output we can later transform the copy keeping the original for auditing. Dataconversion: Converts columns data types from one to another type. It stands for Explicit Column Conversion. Dataminingquery: Used to perform data mining query against analysis services and manage Predictions Graphs and Controls. Derivedcolumn: Create a new (computed) column from given expressions. Exportcolumn: Used to export a Image specific column from the database to a flat file. Fuzzygrouping: Used for data cleansing by finding rows that are likely duplicates. Fuzzylookup: Used for Pattern Matching and Ranking based on fuzzy logic. Importcolumn: Reads image specific column from database onto a flat file. Lookup: Performs the lookup (searching) of a given reference object set against a data source. It is used for exact matches only. Merge: Merges two sorted data sets into a single data set into a single data flow. Mergejoin: Merges two data sets into a single dataset using a join junction. Multicast: Sends a copy of supplied Data Source onto multiple Destinations. Rowcount: Stores the resulting row count from the data flow / transformation into a variable. Rowsampling: Captures sample data by using a row count of the total rows in dataflow specified by rows or percentage. Unionall: Merge multiple data sets into a single dataset. Pivot:Used for Normalization of data sources to reduce analomolies by converting rows into columns Unpivot: Used for denormalizing the data structure by converts columns into rows incase of building Data Warehouses. SSRS use different data source. Some of them are listed below. A control flow consists of one or more tasks and containers that execute when the package runs. To control order or define the conditions for running the next task or container in the package control flow, we use precedence constraints to connect the tasks and containers in a package. A subset of tasks and containers can also be grouped and run repeatedly as a unit within the package control flow. SQL Server Integration Services (SSIS) provides three different types of control flow elements: Containers that provide structures in packages, Tasks that provide functionality, and Precedence Constraints that connect the executables, containers, and tasks into an ordered control flow. Data flow consists of the sources and destinations that extract and load data, the transformations that modify and extend data, and the paths that link sources, transformations, and destinations The Data Flow task is the executable within the SSIS package that creates, orders, and runs the data flow. A separate instance of the data flow engine is opened for each Data Flow task in a package. Data Sources, Transformations, and Data Destinations are the three important categories in the Data Flow. Data set are the set of data which we want to show in report. Data creates on data source. Data source is the source of data from where we are receiving this data i.e. database server and database name joining string. When a data flow component applies a transformation to column data, extracts data from sources, or loads data into destinations, errors can occur. Errors frequently occur because of unexpected data values. Type of typical Errors in SSIS: Data Connection Errors: This error occurs in case the connection manager cannot be initialized with the connection string. This applies to both Data Sources and Data Destinations along with Control Flows that use the Connection Strings. Data Transformation Errors: This error occurs while data is being transformed over a Data Pipeline from Source to Destination. Expression Evaluation errors: This error occurs if expressions that are evaluated at run time perform invalid Report definition: A report definition contains information about the query and layout for the report. Report snapshot: A report snapshot is actually a report definition that contains a dataset instead of query instructions. Rendered report: A fully processed report that contains both data and layout information, in a format suitable for viewing (such as HTML). Parameterized report: A published report that accepts input values through parameters. Shared data source: A predefined, standalone item that contains data source connection information. Shared schedule: It is a predefined, standalone item that covers schedule information. Report-specific data source: Data source information which is defined within a report definition. Report model: A semantic description of business data, used for ac hoc reports created in Report Builder. Linked report: It is a report that derives its definition through a link to another report. Report server can lay up a copy of processed report in a memory and return the copy when a user opens the report. This server memory is called as cache and the process is known as caching. In visual studio RDL files has three parts. Data: It covers the dataset on which we write the query. Data set is associated with data source. Design: In design we can design report. We can create tables and matrix reports. We Drag columns values from source. Preview: It ia used to check the preview after the report run. It allows us to allocate different SQL server objects between different instances of SQL Server.Object incomes from table, stored procedures, user defined functions etc. Under normal circumstances, each level in a hierarchy in Microsoft SQL Server Analysis Services (SSAS) has the same number of members above it as any other member at the same level. In a ragged hierarchy, the logical parent member of at least one member is not in the level immediately above the member. When this occurs, the hierarchy descends to different levels for different drilldown paths. Expanding through every level for every drilldown path is then unnecessarily complicated. MOLAP stands for Multidimensional OLAP. This term designates a Cartesian data structure more specifically. In effect, MOLAP contrasts with ROLAP. In the former, joins between tables are already suitable, which enhances performances. In the latter, joins are computed during the request. Targeted at groups of users because it’s a shared environment. Data is stored in an exclusive server-based format. It performs more complex analysis of data Report builder is used to create small reports and it is a define interface. We can’t change the report interface in report builder it pre designed. We can just drag columns in the report. It creates reports on database objects available with report model project. To enhance the custom codes in report go to report tab on top then properties and there you will find the selections for custom code. Merge transformation can take only two inputs whereas Union all can take more than two inputs. Data has to be sorted before Merge Transformation whereas Union all doesn’t have any boundation like that. There are basically 4 Cache Modes available in SSIS Lookup Transformation SSIS package contain mainly have two types of errors: Procedure Error: It can be handled in Control flow bythe precedence control and redirecting the execution flow. Data Error: it is handled in DATA FLOW TASK by redirecting the data flow using Error Output of a component. The role of an Analysis Services information worker is the traditional “domain expert” role in business intelligence (BI) someone who understands the data employed by a solution and is able to translate the data into business information. The role of an Analysis Services information worker often has one of the following job titles: Business Analyst (Report Consumer), Manager (Report Consumer), Technical Trainer, Help Desk/Operation, or Network Administrator. Early Arriving Facts sometime get unavoidable because delay or error in Dimension ETL or may be due to logic of ETL. To handle Early arriving Facts, we can create dummy Dimension with natural/business key and keep rest of the attributes as null or default. To tune-up the Reporting Services, follow the below mentioned ways: – Expand the Server or utilizing the reporting services of another database server. For better embedding of report contents, report application’s logic and characteristics can have a duplicate copy of data. – Replication of data continuously. Using no lock, the issues of locking can well be resolved and the performance of the query can be improved. This can be done by using dirty read at the time of duplicating the data is unavailable. The difference between a derived measure and a calculated measure is when the calculation is performed. A derived measure is calculated before aggregations are created, and the values of the derived measure are stored in the cube. A calculated measure is calculated after aggregations are created, and the values of a calculated measure aren’t stored in the cube. The primary criterion for choosing between a derived measure and a calculated measure is not efficiency, but accuracy. Data Source Views (DSV) have been introduced in SQL Server Analysis Services (SSAS) 2005. A DSV is a metadata layer between the cube and the underlying data source that allows us to build our cubes without being connected to the underlying source. SSIS includes logging features that write log entries when run-time events occur and can also write custom messages. This is not enabled by default. Integration Services supports a diverse set of log providers, and gives you the ability to create custom log providers. The Integration Services log providers can write log entries to text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files. Logs are associated with packages and are configured at the package level. Each task or container in a package can log information to any package log. The tasks and containers in a package can be enabled for logging even if the package itself is not. It helps in communicating two applications with each other asynchronously. Specialty is two application may be built using dissimilar technology and it works even offline messaging. Sender will stock messages inside queue and reader reads it wherever required The languages used are: Structured Query Language (SQL) Multidimensional Expressions (MDX) :an industry standard query language orientated towards analysis Data Mining Extensions (DMX) :an industry standard query language oriented toward data mining Analysis Services Scripting Language (ASSL) : It is used to manage Analysis Services database objects. Report Builder supports relational SQL and Analysis Services data sources in SQL Server. To create a model for Analysis Services cube, go to Report Manager or Management Studio, create a data source for your Analysis Services database, and then select the Generate Model option to create the model. Processing is a critical and resource intensive operation in the data warehouse lifecycle and needs to be carefully optimized and executed. Analysis Services offers a high performance and scalable processing architecture with a comprehensive set of controls for database administrators. We can process an OLAP database, individual cube, Dimension or a specific Partition in a cube. Script task is control flow level item where as script component is data flow level item, both of the functionalities are same. This 2 are very powerful items in SSIS. Normally we can implement custom code by using these components. By using this two items we can perform any action like DML operations on the data from DB, file and we can do any operation using c# or vb.net code we can use it as Source, destination as well Rsconfig Utility (Rsconfig.exe): encrypts and stores connection and account values in the RSReportServer.config file. RsKeymgmt Utility: it is used to Extracts, restores, creates, and deletes the symmetric key used to protect sensitive report server data against unauthorized access RS Utility: this utility is mainly used to automate report server deployment. The Enable/Disable Writeback dialog box enables or disables writeback for a measure group in a cube. Enabling writeback on a measure group defines a writeback partition and creates a writeback table for that measure group. Disabling writeback on a measure group removes the writeback partition but does not delete the writeback table, to avoid unanticipated data loss To enhance the custom codes in report go to report tab on top then properties and there you will find the selections for custom code. Visual Studio / SSDT / BI Data Tools (Standard and Enterprise editions) contains a set of freely redistributable Report Viewer controls that make it easy to embed Reporting Services functionality into custom applications. Two versions of the Report Viewer exist, one for rich Windows client applications and one for ASP.NET applications. Crystal reports are processed by IIS while SSRS have a report server. Caching in Crystal reports is available through cache server. On the other hand, caching in SSRS is available for Report history snapshots. Crystal reports have standards and user defined field labels. SSRS allows only user defined field labels. Reporting Services provides data-driven subscriptions so that you can customize the distribution of a report based on dynamic subscriber data. Data-driven subscriptions are intended for the following kinds of scenarios: Distributing reports to a large recipient pool whose membership may change from one distribution to the next. For example distribute a monthly report to all current customers. Distributing reports to a specific group of recipients based on predefined criteria. For example send a sales performance report to the top ten sales managers in an organization A database is called an OLAP Database if the database satisfies the FASMI rules: Fast Analysis: It is defined in the OLAP scenario in five seconds or less. Shared: Must support access to data by many users in the factors of Sensitivity and Write Backs. Multidimensional: The data inside the OLAP Database must be multidimensional in structure. Information: The OLAP database must support large volumes of data. Tablular report: It is the most basic type of report. Each column corresponds to a column selected from the database. Matrix report: A matrix report is a cross-tabulation of four groups of data: For many years, Microsoft had no direct solution for reporting with the SQL Server besides Crystal Reports. Now, they have SQL Server Reporting Services, but it does have several drawbacks. It is still complex to understand the complete functionality and structure of this new component, and many users are still relying on the reporting application they are more familiar with, which is Crystal Reports. Also, components in SSRS like Report Builder and Report Designer are meant for different users for different aspects of the report process, yet complete understanding and exposure to both is important to utilize both functions fully and extensively. There are also issues when exporting very large reports to Microsoft Excel, as it can lead to a loss of data.What is MSBI?
Post a Question
What is BI?
What are the important tools used in MSBI?
What is SSAS?
What is SSIS?
What is SSRS?
What is PPS?
What is a Data warehouse?
What is a Data Mart?
What are the some advantages of MSBI?
What is RS.exe utility?
What are the Transformations available in SSIS?
What are the different types of data sources in SSRS?
Can you explain Control flow?
Can you explain Data flow?
Can you explain Data Set in report?
Can you explain Web service task in SSIS?
Can you explain how does Error-Handling work in SSIS?
What are the important terms can be used in the reporting services?
Can you define cache in SSRS?
What are the three different part of RDL file elaborate them?
Can you explain transfer SQL Server object task?
What is a RAGGED hierarchy?
Can you explain MOLAP?
What is report builder?
How to enhance the custom code in Report?
What is difference between Unionall and Merge Join?
What is the different Lookup Cache Modes Available in SSIS?
Explain how we can do Error Handling?
Can you explain Analysis Services Information Worker?
Can you explain Early Arriving Facts?
What is the need for SSAS component?
Can you explain how to fine-tune Reports?
What is the difference between a derived measure and a calculated measure?
What is the purpose of Data Source View?
Can you explain how to log SSIS Executions?
What is the advantage of using MSMQ?
What languages are used in SSAS?
How does Report Builder support Analysis Services cubes?
Can you explain processing?
What are the differences between Script Task and Script Component?
What are the Command Line Utilities available in Reporting Services?
Can you explain WriteBack?
How to enhance the custom code in Report?
How can I add Reporting Services reports to my application?
Compare between SQL Server Reporting Services and Crystal Reports?
What are Data Driven Subscriptions?
Can you explain FASMI?
What is difference between Tablular and Matrix report?
What are the drawbacks of reporting in SSRS?
Related posts:
- Microsoft Azure Interview Questions and Answers
- MySQL Interview Questions & Answers
- Power BI Interview Questions and Answers
- SCCM Interview Questions and Answers
- SQL Server Architecture Interview Questions and Answers
- SQLite Interview Questions and Answers
- SSIS Interview Questions and Answers
- SSRS Interview Questions and Answers