BIDS Helper

Last updated
BIDS Helper
Developer(s) Darren Gosbell, Greg Galloway, John Welch, Darren Green, Scott Currie [1]
Initial releaseMay 27, 2007;16 years ago (2007-05-27) [2]
Stable release
1.7.0 / March 17, 2015 (2015-03-17) [2]
Written in C#
Operating system Microsoft Windows
Available inEnglish
Type Integrated Development Environment
License Shared Source Permissive License (SS-PL)
Website bideveloperextensions.github.io

BIDS Helper is a Visual Studio open source extension with multiple features that extend and enhance business intelligence development functionality in all editions of Microsoft's SQL Server 2005, 2008, 2008 R2 and 2012. [3] [4] BIDS Helper improves the development environment for integration, analysis and reporting services. [5] BIDS Helper is hosted on GitHub. [6]

Contents

History

BIDS Helper was initially released on May 27, 2007. [2] BI Developer Extensions for Visual Studio 2015, 2017 and 2019 is published in the Visual Studio Gallery. [7]
The last version for SQL 2005 (Visual Studio 2005), SQL 2008 (Visual Studio 2008), SQL 2008 R2 (Visual Studio 2008), BIDS Helper 1.7.0 was released on March 17, 2015. [2]

In Version 1.5, released on June 7, 2011, Varigence contributed key portions of the Biml engine, including dynamic package generation to BIDS Helper. [8] [9] Business Intelligence Markup Language (Biml) is an XML-based language that allows a developer to describe a BI solution in a declarative fashion, similarly to using HTML to describe how a web page should appear. [10] Version 1.5 also had the Expression Editor contributed by Konesans.

Features

BIDS Helper has dozens of features that improve the functionality of BIDS, SSIS and SSAS. They are classified into one of five categories listed below based on where they increase functionality in SQL Server: [11]

Analysis Services Multidimensional

Aggregation Manager - an advanced interface for manually editing aggregations
Calculation Helpers - enhances the Calculations tab of the cube editor
Column Usage Reports - opens two reports about column usage: Unused Columns Report + Used Columns Report
Delete Unused Aggregations - automates the process of detecting which aggregations are unused and deleting them
Deploy Aggregation Designs - deploys just the aggregation designs in a cube. It does not change which aggregation design is assigned to each partition
Deploy MDX Script - allows for right clicking on a cube in an Analysis Services solution and deploy just the calculation script
Dimension Data Type Discrepancy Check - runs checks that DSV data types match the data types on the KeyColumns and NameColumn of dimension attributes. It displays any discrepancies and lets the user fix them with the click of a button.
Dimension Health Check - checks various indications of dimension health
Dimension Optimization Report - This report lists all dimension attributes and hierarchies on rows. On columns, it lists various properties which can be used to optimize dimensions.
Duplicate Role - copies a role with all of the associated settings and permissions
Many-to-Many Matrix Compression - Analyzing the data in a m2m relationship to determine whether it can be compressed significantly requires building a complex SQL query. This feature automates this process and returns a report showing how much each m2m relationship can be compressed.
Measure Group Health Check - checks various indications of measure group health.
Non-Default Properties Report - generates a report which shows all properties which have been changed from their defaults.
Parent-Child Dimension Naturalizer - aids in converting parent-child dimensions into natural hierarchies.
Printer Friendly Aggregations - allows for printing or exporting to PDF a report that lists every aggregation in a cube.
Printer Friendly Dimension Usage - allows viewing and printing of a report encompassing all the information from the Dimension Usage tab.
Roles Report - recursively list the members of the role and the members of groups in order to easily determine which members actually have access via each role
Similar Aggregations - allows viewing a report that lists any aggregations which are very similar to each other.
Smart Diff - compares versions of a SSAS, SSIS, and SSRS files. BIDS Helper pre-processes XML files so that the diff versus source control is more meaningful.
Show Extra Properties - exposes hidden properties on several Analysis Services objects. It also provides a better UI for editing descriptions on Analysis Services objects.
Sync Descriptions - if the source database has descriptions for relational tables and columns (for example, using the Kimball Dimensional Modeling Spreadsheet) this function will import those descriptions to the dimension in Analysis Services.
Test Aggregation Performance - test the performance of aggregations
Tri-State Perspectives - this feature operates in the Perspectives tab of the cube designer. It highlights any measure groups or dimensions in which not all visible children are part of the perspective
Update Estimated Counts - updates the EstimatedCount property of every dimension attribute and every partition with exact counts
Validate Aggregations - quickly check whether any aggregations violate restrictions or best practices
Visualize Attribute Lattice - allows for visually seeing the attribute relationships that have been defined for a dimension in an Analysis Services solution

Analysis Services Tabular

Smart Diff - compares versions of a SSAS, SSIS, and SSRS files. BIDS Helper pre-processes XML files so that the diff versus source control is more meaningful.
Tabular Actions Editor - provides a UI for editing actions for Tabular models. For example, this feature allows the model designer the ability to customize the columns returned by drillthrough.
Tabular Display Folders - provides a UI for editing display folders on measures, columns, and hierarchies. All display folders are edited in the same place.
Tabular HideMemberIf - allows HideMemberIf setting to be changed
Tabular Pre-Build - catches the build event and checks features for BIDS Helper settings that have been lost. Because these settings were backed up in annotations, they can be restored, and the user will be prompted if this is necessary.
Tabular Sync Descriptions - if the source database has descriptions for relational tables and columns (for example, using the Kimball Dimensional Modeling Spreadsheet) this function will import those descriptions to the table in a Tabular model in Analysis Services.
Tabular Translations Editor - allows for coding in one language but display the model to users in another language. For example, the model might be coded in English but display to users in Spanish.

Integration Services

Batch Property Update - Allows for updating multiple packages properties at once
Biml Package Generator - provides the ability to create packages from Business Intelligence Markup Language (Biml)
Create Fixed Width Columns - allows for the use of an Excel spreadsheet to create the column definitions in a few simple steps.
Deploy SSIS Packages - quickly deploy SSIS packages directly from BIDS without having to create a deployment manifest and use the Package Installation Wizard.
Design Warnings - provides similar functionality to the Design Warning feature in Analysis Services 2008. It compares the current package against a list of design guidelines, and adds warnings to the Error List in Visual Studio for any items that need to be investigated.
dtsConfig File Formatter - watches for when a window is activated or created for a file with a .dtsConfig extension and automatically initiates the Visual Studio formatting feature
Expression and Configuration Highlighter - gives a visual indicator so that the influence of expressions and package configurations can be seen at a glance
Expression List - provides a window that lists all the expressions defined in a package
Fix Relative Paths - helpful in setting up packages to use relative paths in connection managers and in the path to dtsConfig files
Non-Default Properties Report - displays on one screen all properties which have been changed from their defaults
Pipeline Component Performance Breakdown - automates the methodology for determining which piece of a data flow task is the bottleneck and shows a trend of component performance as different settings and design alternative are tested
Reset GUIDs - resets the IDs for all tasks, connection managers, configurations, event handlers, variables, and the package ID itself
Smart Diff - compares versions of a SSAS, SSIS and SSRS files
Sort Project Files - adds a "Sort by name" menu option to the "SSIS Packages" folder of an SSIS project in Visual Studio
Sortable Package Properties Report - This report shows the following properties for every SSIS package in the project or solution: Package, Name, ID, Description, Creator Name, Creation Date, Creator Computer Name, Version Build, Version GUID, Version Major, Version Minor
SSIS Performance Visualization - shows a graphical gantt chart view of the execution durations and dependencies for a package to help visualize performance
Variables Window Extensions - is designed to extend the Variables window in the SSIS package designer

Reporting Services

Dataset Usage Reports - displays a list of used and unused Reporting Services datasets
Delete Dataset Cache Files - automates the deletion of the .rdl.data files
Smart Diff - compares versions of a SSAS, SSIS, and SSRS files

General

Enable/Disable features - allows for the enable and disable individual features
Preferences - allows for the configuration of features via a Preferences screen
Version Notification - assists in staying current by displaying an alert when a new BIDS Helper version is released

Related Research Articles

Online analytical processing, or OLAP, is an approach to answer multi-dimensional analytical (MDA) queries swiftly in computing. OLAP is part of the broader category of business intelligence, which also encompasses relational databases, report writing and data mining. Typical applications of OLAP include business reporting for sales, marketing, management reporting, business process management (BPM), budgeting and forecasting, financial reporting and similar areas, with new applications emerging, such as agriculture.

<span class="mw-page-title-main">OLAP cube</span> Multidimensional data array organized for rapid analysis

An OLAP cube is a multi-dimensional array of data. Online analytical processing (OLAP) is a computer-based technique of analyzing data to look for insights. The term cube here refers to a multi-dimensional dataset, which is also sometimes called a hypercube if the number of dimensions is greater than three.

<span class="mw-page-title-main">Data Transformation Services</span> Microsoft database tool

Data Transformation Services (DTS) is a Microsoft database tool with a set of objects and utilities to allow the automation of extract, transform and load operations to or from a database. The objects are DTS packages and their components, and the utilities are called DTS tools. DTS was included with earlier versions of Microsoft SQL Server, and was almost always used with SQL Server databases, although it could be used independently with other databases.

Essbase is a multidimensional database management system (MDBMS) that provides a platform upon which to build analytic applications. Essbase began as a product from Arbor Software, which merged with Hyperion Software in 1998. Oracle Corporation acquired Hyperion Solutions Corporation in 2007. Until late 2005 IBM also marketed an OEM version of Essbase as DB2 OLAP Server.

<span class="mw-page-title-main">Microsoft Data Access Components</span> Framework

Microsoft Data Access Components is a framework of interrelated Microsoft technologies that allows programmers a uniform and comprehensive way of developing applications that can access almost any data store. Its components include: ActiveX Data Objects (ADO), OLE DB, and Open Database Connectivity (ODBC). There have been several deprecated components as well, such as the Jet Database Engine, MSDASQL, and Remote Data Services (RDS). Some components have also become obsolete, such as the former Data Access Objects API and Remote Data Objects.

A pivot table is a table of values which are aggregations of groups of individual values from a more extensive table within one or more discrete categories. The aggregations or summaries of the groups of the individual terms might include sums, averages, counts, or other statistics. A pivot table is the outcome of the statistical processing of tabularized raw data and can be used for decision-making.

Multidimensional Expressions (MDX) is a query language for online analytical processing (OLAP) using a database management system. Much like SQL, it is a query language for OLAP cubes. It is also a calculation language, with syntax similar to spreadsheet formulae.

Microsoft SQL Server Analysis Services (SSAS) is an online analytical processing (OLAP) and data mining tool in Microsoft SQL Server. SSAS is used as a tool by organizations to analyze and make sense of information possibly spread out across multiple databases, or in disparate tables or files. Microsoft has included a number of services in SQL Server related to business intelligence and data warehousing. These services include Integration Services, Reporting Services and Analysis Services. Analysis Services includes a group of OLAP and data mining capabilities and comes in two flavors multidimensional and tabular, where the difference between the two is how the data is presented. In a tabular model, the information is arranged in two-dimensional tables which can thus be more readable for a human. A multidimensional model can contain information with many degrees of freedom, and must be unfolded to increase readability by a human.

Microsoft SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks.

Business intelligence software is a type of application software designed to retrieve, analyze, transform and report data for business intelligence. The applications generally read data that has been previously stored, often - though not necessarily - in a data warehouse or data mart.

SQL Server Reporting Services (SSRS) is a server-based report generating software system from Microsoft. It is part of a suite of Microsoft SQL Server services, including SSAS and SSIS.

Azure DevOps Server is a Microsoft product that provides version control, reporting, requirements management, project management, automated builds, testing and release management capabilities. It covers the entire application lifecycle and enables DevOps capabilities. Azure DevOps can be used as a back-end to numerous integrated development environments (IDEs) but is tailored for Microsoft Visual Studio and Eclipse on all platforms.

Microsoft SQL Server is a proprietary relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network. Microsoft markets at least a dozen different editions of Microsoft SQL Server, aimed at different audiences and for workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users.

Business Intelligence Development Studio (BIDS) is the former IDE from Microsoft, and was used to develop data analysis and business intelligence solutions utilizing Microsoft SQL Server Analysis Services, Reporting Services and Integration Services. It is based on the Microsoft Visual Studio development environment, but customized with the SQL Server services-specific extensions and project types, including tools, controls and projects for reports, ETL dataflows, OLAP cubes and data mining structure.

CubePort is a commercial software application that converts from Oracle Essbase to the analogous Microsoft product Microsoft Analysis Services, which is built into Microsoft SQL Server. This application achieves this through various analogy mapping techniques, and is a standard client-server application that runs on a Windows computer but may connect to non-Windows servers. CubePort converts the various OLAP structures and syntaxes in the source through an extraction process, interprets, and recreates in the target. The objective is to simulate exactly the behavior of the original source system to the target system.

<span class="mw-page-title-main">XLCubed</span>

XLCubed is a business intelligence software and consulting services company. Established in 2001, XLCubed develops business intelligence software and provides business intelligence and performance management consulting services. The company is privately held and based out of the United Kingdom in the Thames Valley IT corridor.

Power Pivot, formerly known as PowerPivot, is a feature of Microsoft Excel, a computer software spreadsheet. It is available as an add-in in Excel 2010, 2013 in separate downloads, and as an add-in included with the Excel 2016 program. Power Pivot extends a local instance of Microsoft Analysis Services tabular that is embedded directly into an Excel Workbook. This allows a user to build a ROLAP model in Power Pivot, and use pivot tables to explore the model once it is built. This allows Excel to act as a self-service business intelligence (BI) platform, implementing professional expression languages to query the model and calculate advanced measures.

<span class="mw-page-title-main">Business Intelligence Markup Language</span>

Business Intelligence Markup Language (BIML) is a domain-specific XML dialect for defining business intelligence (BI) assets. Biml authored BI assets can currently be used by the BIDS Helper add-on for Microsoft SQL Server Business Intelligence Development Studio (BIDS) and the Varigence Mist integrated development environment; both tools translate Biml metadata into SQL Server Integration Services (SSIS) and SQL Server Analysis Services (SSAS) assets for the Microsoft SQL Server platform. However, emitters can be created to compile Biml for any desired BI platform.

Data Analysis Expressions (DAX) is the native formula and query language for Microsoft PowerPivot, Power BI Desktop and SQL Server Analysis Services (SSAS) Tabular models. DAX includes some of the functions that are used in Excel formulas with additional functions that are designed to work with relational data and perform dynamic aggregation. It is, in part, an evolution of the Multidimensional Expression (MDX) language developed by Microsoft for Analysis Services multidimensional models combined with Excel formula functions. It is designed to be simple and easy to learn, while exposing the power and flexibility of PowerPivot and SSAS tabular models.

The history of Microsoft SQL Server begins with the first Microsoft SQL Server database product – SQL Server v1.0, a 16-bit relational database for the OS/2 operating system, released in 1989.

References

  1. ↑ "BIDS Helper - People". Code Name. Retrieved November 21, 2012.
  2. 1 2 3 4 "BIDS Helper 1.6.5". Codeplex. Retrieved January 29, 2014.
  3. ↑ Tok, Wee-Hyong; Parida, Rakesh; Masson, Matt; Ding, Xiaoning; Sivashanmugam, Kaarthik (2012). Microsoft SQL Server 2012 Integration Services. O'Reilly Media, Inc. p. 511. ISBN   978-0735665859.
  4. ↑ Ferrari, Alberto; Webb, Chris; Russo, Marco (2009). "Chapter 2: Building Basic Dimensions and Cubes". Expert Cube Development with Microsoft SQL Server 2008 Analysis Services. Packt Publishing. p. 40. ISBN   978-1-847197-22-1.
  5. ↑ Beresford, James (December 18, 2009). "BIDS Helper". BI Monkey - James Beresford on Microsoft BI and Consulting in Sydney, Australia. Archived from the original on June 16, 2012. Retrieved November 5, 2012.
  6. ↑ Rad, Reza; Perfeito, Pedro (2012). "Chapter 15: Performance Boost in SSIS". Microsoft SQL Server 2012 Integration Services: An Expert Cookbook. Packt Publishing. p. 564. ISBN   978-1-84968-524-5.
  7. ↑ "GitHub - BIDS Downloads". GitHub. Retrieved October 15, 2018.
  8. ↑ "BIDS Helper 1.5". Codeplex. Retrieved November 21, 2012.
  9. ↑ Leonard, Andy; Masson, Matt; Mitchell, Tim; Moss, Jessica; Ufford, Michelle (2012). SQL Server 2012 Integration Services Design Patterns. APress. p. 302. ISBN   978-1430237716.
  10. ↑ Welch, John (June 7, 2011). "Biml Package Generator". BIDS Helper. Retrieved September 30, 2011.
  11. ↑ "BIDS Helper - Documentation". Codeplex. Retrieved November 21, 2012.
This page is based on this Wikipedia article
Text is available under the CC BY-SA 4.0 license; additional terms may apply.
Images, videos and audio are available under their respective licenses.