Business Intelligence,Technology, Thoughts, Thinking : DEV

Whats New in SSIS 2012 (SP1)–מה חדש ב

Ronen Chenn — Mon, 28 Jan 2013 15:30:36 GMT

גרסת SQL 2012 חושפת בפנינו יכולות חדשות אשר מחזקות את מעמדה בשוק בסיסי הנתונים ככלל והbi בפרט.

לקראת השתתפותי במספר כנסים בשנה הקרובה במערב אירופה וצפון אמריקה, אני שמח לשתף אתכם בתובנות שהועלו עד כה ב SolidBI.com .

במאמר זה, אתייחס ל integration services בגרסת sql2012 sp1.

למידע נוסף, הגדרות ארכיטקטורה, שיפורי ביצועים ו -best practices , הנכם מוזמנים ליצור קשר ב noya@solidbi.com או alberto@solidbi.com או דרך האתר שלנו ב http://www.solidbi.com

אז מה חדש בגרסת ssis 2012?

הפצת נתונים deployment

ניהול ופתרון בעיות באמצעות ממשק חדש

שיפורי פיתוח

מנגנון לשיפור איכות הנתונים וטיובם.

גישה לדוגמאות והדרכות

נתחיל ב deployment.

ובכן, גרסת 2012 הוסיפה יכולת של הפצת פרוייקט של (solution / project ). זוהי מתודולוגיה שההתפלגות של המפתחים בעולם נחלקת בדיוק לשניים. מדוע?

יש את אלו המצדדים בפיתוח של מערכת שלמה, בדיקתה באופן מלא והפצתה לסביבת הייצור. ויש את אלו התומכים בשיטה של העברת חלקי מערכת לייצור כדי לא ״לפגוע״ או לשנות משהו במודול הכללי. כלומר משהו בסגנון של תיקון תקלת ייצור קטנה ולא לפרק את המערכת כולה לגורמים....

אם כך, בגרסה זו אנו נוכל לבחור את השיטה - או הפצה מלאה הכל כולל הכל כלומר project deployment או הפצת חבילות בודדות כלומר package deployment.

כך או כך, למפתח תהיה האפשרות לבחור את השיטה.

כדי להשתמש ביכולת החדשה, עלינו לקנפג את המערכת כדי שתתמוך ב project deployment:

1. התקנת ssis catalog

2. בניית ה solution כך שיתמוך ב project deployment.

כבר עתה חשוב לציין שהמעבר בין השיטות אחרי שהחל הפיתוח לא טרויאני ולעיתים אף בלתי אפשרי. למשל אם בחרנו בדרך החדשה ובנינו פרמטר ברמת הפרוייקט, לא נוכל להמיר ל דרך המסורתית מכיוון שהיכולת פשוט לא קיימת שם.

איך ממירים בין 2 השיטות?

אם אנו ב ssdt , לוחצים קליק ימני על הפרוייקט ובוחרים את המתודולוגיה בה בחרנו.

אלו מכם שרגילים להשתמש ב bids helper יופתעו לגלות שלא ימצאו שם את ה deploy . ובכן אל חשש, הוא קיים רק בשיטה הקודמת. אם תמירו את הפרוייקט ל package deployment תוכלו לשוב ולהשתמש בdeploy.

עוד מנגנון חדש שעומד לרשותנו הוא ה ssis catalog.

זהו בעצם repository בו מאוחסנים כל נתוני המטהדטה של הssis.

זוהי יכולת בוגרת של מערכת שמשתמשת ב db כדי להחזיק את נתוני ה etl בדומה לאינפורמטיקה ודטהסטייג.

בתוך אותו ריפוסיטורי נחשפות לנו מתודות חדשות שנוכל הארכיטקטים ומפתחים להשתמש בהן כדי לנטר ולשפר את התהליכים שבנינו ואת השרת עצמו.

בMsdn תמצאו פירוט של היכולות : למשל:

This release provides additional views, stored procedures, and stored functions to help you troubleshoot performance and data issues.

Troubleshooting Capability	Related Views, Procedures, and Functions
Get performance statistics and other information for an execution	· catalog.executions (SSISDB Database) · catalog.execution_component_phases · catalog.executable_statistics · dm_execution_performance_counters (SSISDB Database)
Add, remove, and query data taps in a package data flow	· catalog.add_data_tap · catalog.add_data_tap_by_guid · catalog.remove_data_tap · catalog.execution_data_taps · catalog.execution_data_statistics
Create a dump for a running package	catalog.create_execution_dump
Set a parameter value in an instance of an execution and view parameter values that are used by a package during an instance of execution.	· catalog.set_execution_parameter_value (SSISDB Database) · catalog.execution_parameter_values (SSISDB Database)

המשך הסקירה בבלוגים הבאים, המשיכו לעקוב…

Microsoft® SQL Server® 2012 PowerPivot for Microsoft® Excel® 2010

Ronen Chenn — Tue, 13 Nov 2012 00:30:10 GMT

Microsoft PowerPivot for Microsoft Excel 2010 provides ground-breaking technology, such as fast manipulation of large data sets (often millions of rows), streamlined integration of data, and the ability to effortlessly share your analysis through Microsoft SharePoint 2010.
Microsoft PowerPivot for Microsoft Excel 2010 is a data analysis tool that delivers unmatched computational power directly within the software users already know and love — Microsoft Excel. You can transform mass quantities of data with incredible speed into meaningful information to get the answers you need in seconds. You can effortlessly share your findings with others. You can:

Process large data sets (often millions of rows) with about the same performance as processing a few hundred rows by leveraging the PowerPivot in-memory engine and its efficient compression algorithms
Use PowerPivot to directly access trusted premium and public domain data from DataMarket section of Windows Azure Marketplace
Integrate data from a multitude of sources, including corporate databases, spreadsheets, reports, text files, and Internet data feeds
Go beyond standard Excel expressions and use PowerPivot’s Data Analysis Expressions (DAX) language to perform powerful data manipulations. Follow relationships between tables as in a database and define sophisticated calculations using familiar and intuitive expressions
Interactively explore, analyze, and create reports without depending on expert knowledge and specialty training by using native Excel 2010 functionality such as PivotTables, slicers, and other familiar analysis features
Click here to download.

Microsoft® SQL Server® 2012 SP1 Data Mining Add-ins for Microsoft® Office®

Ronen Chenn — Tue, 13 Nov 2012 00:21:50 GMT

Microsoft SQL Server 2012 SP1 Data Mining Add-ins for Microsoft Office helps you take advantage of SQL Server predictive analytics in Office Excel and Office Visio. The download includes the following components:

Table Analysis Tools for Excel: This add-in provides easy-to-use tasks that leverage SQL Server 2012 data mining models within Excel using either your spreadsheet data or external data accessible through your SQL Server 2012 Analysis Services instance.
Data Mining Client for Excel: By using this add-in, you can create, test, explore, and manage data mining models within Excel using either your spreadsheet data or external data accessible through your SQL Server 2012 Analysis Services instance.
Data Mining Templates for Visio: This add-in enables you to render and share your mining models as annotatable Visio drawings.

If you choose, you can install each of these components separately.

To download click here.

SQL Server Data Tools - November 2012 is now available!

Ronen Chenn — Thu, 08 Nov 2012 21:55:16 GMT

Get it here

SSDT for Visual Studio 2012: http://msdn.microsoft.com/en-us/jj650015

SSDT for Visual Studio 2010: http://msdn.microsoft.com/en-us/jj650014

What's new?

Selective XML Index support

We've added support for selective XML indexes, enabling users to import, edit, add, refactor, build, and publish selective XML indexes in SSDT.

Add new selective XML index in Table Designer

Check for SSDT Updates

This release adds support for automatic and manual checks for SSDT updates. You can view information about your current SSDT installation and verify that it's up to date by selecting "Check for Updates…" under the top-level SQL menu in Visual Studio.

Check for Updates dialog

This feature also notifies users when a newer version of SSDT is available for download.

SSDT update notification

Updated Data-Tier Application Framework (DACFx)

SSDT – November 2012 includes the October DACFx redist (11.1.2816.0). The October DACFx release contains several feature enhancements and bug fixes outlined in this post: http://blogs.msdn.com/b/ssdt/archive/2012/10/10/sql-server-data-tier-application-framework-october-2012-available.aspx

Updated SQL Server 2012 components

SSDT – November 2012 is coordinated with the release of SQL Server 2012 Service Pack 1 and it chains in the latest SQL Server components.

Bug fixes

This release contains over 30 bug fixes, including fixes for customer-reported issues from SSDT - September 2012.

Power Tools Compatibility

The November 2012 update of SSDT is only compatible with the latest SSDT Power Tools (v 1.4). Please note that if you have an existing version of SSDT Power Tools, it will need to be upgraded in order to work with SSDT – November 2012.

SSDT Power Tools for Visual Studio 2012

SSDT Power Tools for Visual Studio 2010

SQL Server 2012-RTM demo

Ronen Chenn — Sat, 20 Oct 2012 19:00:05 GMT

The new SQL Server 2012-RTM demo is fully integrated with SQL Server 2012, SharePoint 2010 and Office 2010 including:

PowerPivot and MDS Excel integration
Reporting Services integration with SharePoint
Power View integrated with SharePoint
Data Quality Services
Master Data Services
Multidimensional and Tabular instances of SQL Server 2012 Analysis Services
Visual Studio 2010
Silverlight 5
Picnic Power View sample loaded

This image also includes demos of the RTM version of AutoSales, TailspinToys, Contoso Energy, Telco (PowerPivot and Power View), Contoso Schools and the Telco demo.

You can download the Virtual Machine to install and run locally from: http://www.microsoft.com/betaexperience/pd/BIVHD/enus/

The Base ImageX Server is a virtual machine that can be hosted in Hyper-V that allows a user to test out the latest Business Intelligence features of SQL Server 2012 – RTM. The virtual machine has been configured to support the use of PowerView reports, PowerPivot Excel documents and various content packs allowing the user to explore the capabilities of SQL Server 2012. Working with the demos that can be installed on the ImageX server the user will have a clear picture of how the SQL Server 2012, Office and SharePoint tools support and enhance the self-service business intelligence environment.

Microsoft SQL Server 2012 SP1 Feature Pack

Ronen Chenn — Thu, 11 Oct 2012 12:43:00 GMT

The Microsoft® SQL Server® 2012 SP1 Feature Pack CTP4 is a collection of stand-alone packages which provide additional value for Microsoft® SQL Server® 2012. The packages provided include the latest versions of:

Redistributable components for Microsoft® SQL Server® 2012 SP1 CTP4

Add-on providers for Microsoft® SQL Server® 2012 SP1 CTP4

It can be download here.

Linked Cubes

Ronen Chenn — Sun, 09 Sep 2012 00:29:43 GMT

This is something I used to use in SQLR2 to solve the perspective security challenge.

What is it?

A linked cube is an MDStore cube object with a Subclass Type of sbclsLinked. The contents of a linked cube are based on another cube that is defined and stored on a different Analysis server. Unlike a virtual cube, which can contain portions of one or more cubes, a linked cube references the entire contents of a single cube.

A subscribing server is an Analysis server that contains a linked cube. A publishing server contains the source cube upon which the linked cube is based. To be a subscribing server, the Analysis server service (MSSQLServerOLAPService) must run under an account that has query permissions on each publishing server to which it connects. This account can be an account that belongs to the OLAP Administrators group on the publishing server, or an account that has query permissions established by a role on each source cube to which the subscribing server needs access. There are no requirements an Analysis server has to meet in order to become a publisher. Any processed cube on the publishing server can be made available for linking, subject to network and cube security; the cube must be available for use by the subscribing server as if the subscribing server were a client querying the cube on the publishing server.

All dimensions in a linked cube are treated as private dimensions on the subscribing server. This means that other regular cubes in the subscribing database cannot use these dimensions. A linked cube can be included in a virtual cube.

Linked cubes cannot be created from regular cubes that employ shared or private ROLAP dimensions.

But, keep in mind that this feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.

Are there any alternative?

Keep following this blog for answers.

Ronen

BI User Group Meeting #33 (13.09.2012)

Ronen Chenn — Thu, 06 Sep 2012 12:35:52 GMT

מפגש ה – BI הקרוב יתקיים ביום חמישי- 13.09.2012 במשרדי מיקרוסופט רעננה, אולם דקל.

מצורף לו"ז לקראת המפגש.

להרשמה והבטחת מקום חניה, יש להירשם באתר : http://biug33.eventbrite.com/

לו"ז ל Bi User Group Meeting #33(13.09.2012)

17:30 התכנסות והרמת כוסית לראש השנה הממשמש ובא :-)

18:00 Excel 2013, כלי ה-BI הטוב ביותר. תחזיקו חזק-> ממריאים.

המרצה- מר דני חוטר, Senior Program Manager at Microsoft ILDC (Israel R&D)

19:15 הפסקה

19:30 המשך

20:00 שאלות ותשובות

עדכון מפגשי יולי ואוגוסט

Ronen Chenn — Mon, 23 Jul 2012 12:15:00 GMT

שלום לכולם,

בשבוע האחרון קיבלנו פניות רבות של חברי הקבוצה לדחות את מפגש יולי עקב חופשות והיעדרויות .

היות שמפגש אוגוסט מתוכנן לשבוע האחרון של אוגוסט
בו כבר חוזרים לבתי הספר,הוצע על ידי רוב הקבוצה לדחות את מפגש יולי ולקיים את מפגש
אוגוסט.

בסופו של דבר, הוחלט לקבל את ההצעה.מפגש יולי לא יתקיים עקב חופשות.

מפגש אוגוסט יתקיים.

על נושא מפגש אוגוסט נוציא הודעה מסודת.
תודה לכולם על שיתוף הפעולה והעדכונים השוטפים!!!

חופשה נעימה לכולם,

The advantages in deploying a model using DirectQuery mode

Ronen Chenn — Tue, 17 Jul 2012 01:46:21 GMT

There are many advantages to deploying a model using DirectQuery mode:

It is possible to have a model over data sets that are too large to fit in memory on the Analysis Services server.
The data is guaranteed to be up-to-date, and there is no extra management overhead of having to maintain a separate copy of the data. Changes to the underlying source data can be immediately reflected in queries against the data model.
DirectQuery can take advantage of provider-side query acceleration, such as that provided by xVelocity memory optimized column indexes.
Any security enforced by the back-end database is guaranteed to be enforced, using row-level security. In contrast, if you are using cached data, it can be difficult to ensure that the cache is secured exactly as on the server.
If the model contains complex formulas that might require multiple queries, Analysis Services can perform optimization to ensure that the query plan for the query executed against the back-end database will be as efficient as possible.

Tabular Models with DirectQuery Mode

Ronen Chenn — Tue, 17 Jul 2012 01:43:05 GMT

Tabular models are authored by using the model designer SQL Server Data Tools (SSDT). The model designer creates all models in memory, which means that when you are modeling, if your data is too large to fit into memory, you should import only a subset of data into the cache used by the workspace database.

When you are ready to switch to DirectQuery mode, you can change a property that enables DirectQuery mode. For more information, see Enable DirectQuery Design Mode (SSAS Tabular).

When you do this, the model designer automatically configures the workspace database to run in a hybrid mode that lets you continue to work with the cached data. The model designer will also notify you of any features in your model that are incompatible with DirectQuery mode. The following list summarizes the main requirements to keep in mind:

Data sources: DirectQuery models can only use data from a single SQL Server data source. When DirectQuery mode has been turned on for a model, you can use no other types of data in the model designer, including tables added by copy-paste operations. All other import options are disabled. Any tables included in a query must be part of the SQL Server data source. See Data Sources for more information.
Support for calculated columns: Calculated columns are not supported for DirectQuery models. However, you can create measures and KPIs, which operate over sets of data. See the section on validation for more information.
Limited use of DAX functions: Some DAX functions cannot be used in DirectQuery mode, so you must replace them with other functions, or create the values using derived columns in the data source. The model designer provides design-time validation for any errors that arise when you create formulas that are incompatible with DirectQuery mode. See the following sections for more information: Validation.
Formula compatibility: In certain known cases, the same formula can return different results in a cached or hybrid model compared to a DirectQuery model that uses only the relational data store. These differences are a consequence of the semantic differences between the xVelocity in-memory analytics (VertiPaq) engine and SQL Server. For more information about these differences, see this section: Formula Compatibility.
Security: You can use different methods to secure models depending on how they are deployed. Cached data for tabular models is secured by using the security model of the Analysis Services instance. DirectQuery models can be secured by using roles, but you can also use security defined in the relational data store. The model can be configured so that users who open a report based on a DirectQuery-only model can see only the data that is allowed to them under their permissions in SQL Server. See this section for more information: Security.
Client restrictions: When a model is in DirectQuery mode, it can only be queried by using DAX. You cannot use MDX to create queries. This means that you cannot use the Excel Pivot Client, because Excel uses MDX.
However, you can create queries against a DirectQuery model in SQL Server Management Studio if you use a DAX table query as part of an XMLA Execute statement.

SSAS Tabular Impersonation (Sql2012)

Ronen Chenn — Sun, 08 Jul 2012 13:42:55 GMT

Impersonation is the ability of a server application, such as Analysis Services, to assume the identity of a client application. Analysis Services runs using a service account, however, when the server establishes a connection to a data source, it uses impersonation so that access checks for data import and processing can be performed.

Credentials used for impersonation are different from the credentials of the user currently logged on. Logged on user credentials are used for particular client side operations when authoring a model.

It is important to understand how impersonation credentials are specified and secured as well as the difference between contexts in which both the current logged on user’s credentials and when other credentials are used.

Understanding server side credentials

In SQL Server Data Tools (SSDT), credentials are specified for each data source by using the Impersonation Information page in the Table Import Wizard or by editing an existing data source connection on the Existing Connections dialog.

When data is imported or processed, the credentials specified in the Impersonation Information page are used to connect to the data source and fetch the data. This is a server side operation running in the context of a client application because the Analysis Services server hosting the workspace database connects to the data source and fetches the data.

When you deploy a model to an Analysis Services server, if the workspace database is in memory when the model is deployed, the credentials are passed to the Analysis Services server to which the model is deployed. At no time are user credentials stored on-disk.

When a deployed model processes data from a data source, the impersonation credentials, persisted in the in-memory database, are used to connect to the data source and fetch the data. Because this process is handled by the Analysis Services server managing the model database, this is again a server side operation.

Understanding client side credentials

When authoring a new model or adding a data source to an existing model, you use the Table Import Wizard to connect to a data source and select tables and views to be imported into the model. In the Table Import Wizard, on the Select Tables and Views page, you can use the Preview and Filter feature to view a sample (limited to 50 rows) of the data you will import. You can also specify filters to exclude data that does not need to be included in the model.

Similarly, for existing models that have already been created, you can use the Edit Table Properties dialog to preview and filter data imported into a table. The preview and filter features here use the same functionality as the Preview and Filter feature on the Select Tables and Views page of the Table Import Wizard.

The Preview and Filter feature, and the Table Properties and Partition Manager dialog boxes are an in-process client side operation; that is, what is done during this operation are different from how the data source is connected to and data is fetched from the data source; a server side operation. The credentials used to preview and filter data are the credentials of the user currently logged on. Client side operations always use the current user’s Windows credentials to connect to the data source.

This separation of credentials used during server side and client side operations can lead to a mismatch in what the user sees using the Preview and Filter feature or Table Properties dialog (client side operations) and what data will be fetched during an import or process (a server side operation). If the credentials of the user currently logged on and the impersonation credentials specified are different, the data seen in the Preview and Filter feature or the Table Properties dialog and the data fetched during an import or process can be different depending on the credentials required by the data source.

Tabular AMO 2012

Ronen Chenn — Sat, 23 Jun 2012 15:07:53 GMT

Great news on codeplex.

it is very good to know that people are working hard to share their knowledge with the community.

Tons of credits to JuanPablo Jofre!

Tabular AMO 2012 is about creating and managing tabular models using the AMO api. It is a developer’s sample, for those interested in managing Analysis Services.
The sample is made of two project parts. The first part is a library of functions to manage tabular models -AMO2Tabular V2-. The second part is a sample to build a tabular model -AdventureWorks Tabular AMO 2012- using the AMO2Tabular library; the created model is similar to the 'AdventureWorks Tabular Model 2012', available for download here: http://msftdbprodsamples.codeplex.com/downloads/get/353143.
The intentions around the AMO2Tabular library were to provide the most complete guide on how to write AMO code to manage the different logical objects in a tabular model. Not all functions to manage tabular models are implemented in the library; but, I expect the necessary knowledge to complete the library is included in the AMO2Tabular source code. Also, I expect you to contribute to the library; once you find that certain functionality was not included and you have to implement that functionality, please consider donating your code to expand the library.
The scope of the library comprehensive, in the sense that it has functionality for all tabular objects. However, as mentioned earlier, it is not extensive; it does not necessarily cover all possible operations on every tabular object. If coding certain operation, on a particular object, is not an obvious solution, you can be certain that I have included that operation in the library; what remains to be seen is what you consider obvious vs. what I do (I just hope there is not too much difference here).
The 'AdventureWorks Tabular AMO 2012' sample was designed with two purposes in mind. The first purpose was for it to be a test bed for the library; where most of the functionality of the library could be tested. The second purpose was to have a sample on how to use the library to build and manage a tabular model; also, the model created by the sample should have enough complexities to be a good showcase of the capabilities of the library.
The downloadable file contains one solution -Tabular AMO- with both projects: AMO2Tabular and 'AdventureWorks Tabular AMO 2012'.

See the Readme file for a quickstart; or, see the Setup and Execute file for a detailed explanation on building the tabular model.

Microsoft® SQL Server® 2012 Master Data Services Add-in For Microsoft® Excel®

Ronen Chenn — Sat, 23 Jun 2012 15:02:05 GMT

The Master Data Services Add-in for Excel gives multiple users the ability to update master data in a familiar tool without compromising the data’s integrity in MDS. From within the Add-in, users can publish data to the MDS database with the click of a button. Administrators can use the Add-in to create new model objects and load data without ever launching any administrative tools, helping to speed deployment. With the Master Data Services Add-in for Excel, all master data remains centrally managed in MDS, while the ability to read or update the data is distributed to those who need it.
The Master Data Services (MDS) Add-in for Microsoft Excel is a data management tool that delivers a multitude of master data management capabilities with ease and efficiency. Manage MDS data within an interface you already know and love – Microsoft® Excel. Leverage existing Excel functionality to share your managed lists with others, knowing these lists can be secured and monitored with all of the features provided by Master Data Services.
The Master Data Services Add-in for Excel gives multiple users the ability to update master data in a familiar tool while maintaining the data’s integrity in MDS. From within the Add-in, users can publish data to the MDS database with the click of a button. Administrators can use the Add-in to create new model objects and load data without ever launching any administrative tools, helping to speed deployment. With the Master Data Services Add-in for Excel, all master data remains centrally managed in MDS, while the ability to read or update the data is distributed to those who need it.

Quickly and easily load new managed data sets into the Master Data Services database.
Administrators, easily create new entities to store sets of managed data.
Make bulk data changes to pre-existing models.
Correct all issues discovered through the validation process and seamlessly publish updates to the MDS server.
Add and update columns in your entities with a few clicks, creating constrained columns on the fly.
Send defined queries to other users through Outlook Email for a rich collaboration experience leveraging Master Data Services Security
Determine data matches with the new Data Quality Services for SQL Server 2012

Click here for download.

Microsoft® SQL Server Integration Services Balanced Data Distributor For Microsoft® SQL Server® 2012

Ronen Chenn — Sat, 23 Jun 2012 14:59:36 GMT

Microsoft® SSIS Balanced Data Distributor (BDD) is a new SSIS transform. This transform takes a single input and distributes the incoming rows to one or more outputs uniformly via multithreading. The transform takes one pipeline buffer worth of rows at a time and moves it to the next output in a round robin fashion. It’s balanced and synchronous so if one of the downstream transforms or destinations is slower than the others, the rest of the pipeline will stall so this transform works best if all of the outputs have identical transforms and destinations. The intention of BDD is to improve performance via multi-threading. Several characteristics of the scenarios BDD applies to: 1) the destinations would be uniform, or at least be of the same type. 2) the input is faster than the output, for example, reading from flat file to OleDB.
NOTE: An updated version of Microsoft SSIS Balanced Data Distributor (BDD) is available. This includes a fix for the problem causing BIDS to crash when removing the BDD transform.

For more details, and download information, see KB 2616527.