Yes, tabular server supports .Net Provider but only for those Data Sources which are mentioned in this article – Data Sources Supported (SSAS Tabular)
Take an example of Oracle, if you select Oracle in “Table Import Wizard” and click on Next, you will find Connect to an Oracle Database Windows -> on that windows select Advanced and it will provide an option of Selecting Providers, if you notice in screenshot as follow you can see Oracle Data Provider for .Net is present.
What about other data sources which are not appearing in the Table Import Wizard Relational Databases List, for such sources you can use Others (Oledb/ODBC)
This option should work if you are able to connect to your Data Source using ODBC, I have tested with SQL Engine and it worked like charm and same manner I had tested with Hadoop (HDInsight) as well
2) If ODBC Providers supported by Tabular Server, then why its not working for me?
ODBC is supported by Tabular Server but you need to configure 2 DSN one for 32 BIT and second for 64 BIT but with same name, otherwise you will get an error message while importing data.
Why we need 2 ODBC DSN?
32 Bit is for SSDT and 64 BIT is used by Tabular Engine while importing data in Tabular Server, further explained in this post Post Link
3rd August, 2013
Presented a Session about SQL 2012 Tabular Server for DBA’s in an Event Organized by http://sqlgeeks.com/.
There were around 250+ attendees majorly DBA’s came with an intent of learning SQL BI, as they are from Non BI background, so I kept it basic.
PPT for your reference……
Few Pictures from event…….
Most Awaited Tabular Performance Tuning Paper is out………….
Applies to: SQL Server 2012
Summary: Tabular models hosted in SQL Server 2012 Analysis Service provide a comparatively lightweight, easy to build and deploy solution for business intelligence. However, as you increase the data load, add more users, or run complex queries, you need to have a strategy for maintaining and tuning performance. This paper describes strategies and specific techniques for getting the best performance from your tabular models, including processing and partitioning strategies, DAX query tuning, and server tuning for specific workloads
To review the document, please download the Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services Word document.
When using ODBC as a Data Source in the Table Import Wizard to add data to a Tabular Server, we are getting the following error; however, preview shows the data correctly:
OLE DB or ODBC error: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application; IM014.
A connection could not be made to the data source with the DataSourceID of ’7300a711-299c-4367-98eb-b2e01b7e4ba5′, Name of ‘Custom 4′.
An error occurred while processing the partition ‘DimAccount 1_e8a5233f-406a-450d-8b2e-8bf86a321d2f’ in table ‘DimAccount 1_e8a5233f-406a-450d-8b2e-8bf86a321d2f’.
The current operation was cancelled because another operation in the transaction failed.
SSAS Tabular Server 2012 x64
The problem occurred because SQL Server Data Tools is 32-bit and the DSN that we used initially to import data (in Data Link Properties) is 32-bit ODBC, but the server is 64-bit.
Therefore you are able to use Test Connection successfully and are even able to select tables. However, when you try to import data, an error occurs.
Because the server build is x64, the Tabular Engine will look for a 64-bit DSN when attempting to import data, and in the absence of a 64-bit DSN, import will fail with the following error:
The Preview feature in SSDT uses a 32-bit ODBC DSN, so you can view the data but cannot import data.
To fix this issue, you need to configure two ODBC DSNs, one 32-bit and one 64-bit, and give them the same name.
How to Create Matching 32-bit and 64-bit ODBC DSNs:
A 64-bit version of the Microsoft Windows operating system includes the following versions of the Microsoft Open Database Connectivity (ODBC) Data Source Administrator tool (Odbcad32.exe):
o The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.
o The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.
Refer this link for full reference http://support.microsoft.com/kb/942976
I am pretty excited about this feature with SQL 2012 SP1 Cumulative Update 4, you can create Power View reports against existing Cubes.
Refer this blog for further information: http://blogs.msdn.com/b/analysisservices/archive/2013/05/31/power-view-connectivity-for-multidimensional-models-released.aspx
Informative Whitepapers covering operation of HDInsight including:
1) Compression in Hadoop
When using Hadoop, there are many challenges in dealing with large data sets. The goal of this document is to provide compression techniques that you can use to optimize your Hadoop jobs, and reduce bottlenecks associated with moving and processing large data sets.
In this paper, we will describe the problem of data volumes in different phases of a Hadoop job, and explain how we have used compression to mitigate these problems. We review the compression tools and techniques that are available, and report on tests of each tool. We describe how to enable compression and decompression using both command-line arguments and configuration files.
To review the document, please download the Compression in Hadoop Word document.
2) Hadoop Performance in Hyper-V
Compelling use-cases from industry leaders are quickly changing Hadoop from an emerging technology to an industry standard. However, Hadoop requires considerable resources, and in the search for computing power, users are increasingly asking if it is possible to virtualize Hadoop—that is, create clusters on a virtual machine farm—to build a private cloud infrastructure .
This paper presents the result of internal benchmarks by Microsoft IT, in which the performance of a private cloud using virtual machines was compared to the same jobs running on servers dedicated to Hadoop. The goal was to determine whether Hadoop clusters hosted in Microsoft Hyper-V can be as efficient as physical clusters.
The results indicate that the performance impact of virtualization is small, and that Hadoop on Microsoft Hyper-V offers compelling performance as well as other benefits.
To review the document, please download the Performance of Hadoop on Windows in Hyper-V Environments Word document.
3)Job Optimization in Hadoop
The Map/Reduce paradigm has greatly simplified development of large-scale data processing tasks. However, when processing data at the terabytes or petabyte scale in Hadoop, jobs might run for hours or even days. Therefore, understanding how to analyze, fix, and fine-tune the performance of Map/Reduce jobs is an extremely important skill for Hadoop developers.
This paper describes the principal bottlenecks that occur in Hadoop jobs, and presents a selection of techniques for resolving each issue and mitigating performance problems on different workloads. The paper explains the interaction of disk I/O, CPU, RAM and other resources, and demonstrates with examples why efforts to tune performance should adopt a balanced approach.
It includes the results of extensive experiments with performance tuning, which resulted in significant differences in the speed of the same Map/Reduce job before and after.
To review the document, please download the Hadoop Job Optimzation Word document.
4) Leveraging a Hadoop cluster from SQL Server Integration Services (SSIS)
With the explosion of data, the open source Apache™ Hadoop™ Framework is gaining traction thanks to its huge ecosystem that has arisen around the core functionalities of Hadoop distributed file system (HDFS™) and Hadoop Map Reduce. As of today, being able to have SQL Server working with Hadoop™ becomes increasingly important because the two are indeed complementary. For instance, while petabytes of data can be stored unstructured in Hadoop and take hours to be queried, terabytes of data can be stored in a structured way in the SQL Server platform and queried in seconds. This leads to the need to transfer data between Hadoop and SQL Server.
This white paper explores how SQL Server Integration Services (SSIS), i.e. the SQL Server Extract, Transform and Load (ETL) tool, can be used to automate Hadoop + non Hadoop job executions, and manage data transfers between Hadoop and other sources and destinations.
To review the document, please download the Leveraging a Hadoop cluster from SQL Server Integration Services (SSIS) Word document
Analysis Services is crashing intermittently and also producing min-dumps.
This could be one of the issues. For getting full analysis of Mini-Dump you can involve Microsoft Customer Support Services and ask them to analyze dumps.
In this case we found:
The issue is SMS client (CcmExce) does software inventory periodically, and it scans the data folder of Analysis Services. When a job needs to commit, it has to delete older version of data files. At the moment, the SMS client has a file handle on some of the database files, causing SSAS unable to delete the older version of the database, so commit fails and crashes SSAS
As you can see in this Process Monitor – CcmExec is browsing through SQL Folders:
We have resolved issue by making an exception in SMS client not to browse / scan SSAS Folders
Exclude Analysis Services folders from Virus Scans, File Monitoring Tools, Systems Management Server Client – CcmExec.exe or any other 3p File Monitoring or File Backup Tool.
For SQL engine follow recommendations are given in this link – http://support.microsoft.com/kb/309422
Look for the SQL Developer track. If you are at TechEd, do join.
From straightforward client/server designs to complex architectures relying on distributed Windows services, SharePoint applications, Web services, and data sources, Microsoft BI solutions can pose many challenges to seamless user authentication and end-to-end identity delegation. SQL Server technologies and data providers expect to use Windows authentication while SharePoint Server uses Web Services Security (WS-Security). Flowing a user identity from a Windows or browser-based BI client application through a claims-based SharePoint service to a Windows backend system is not always possible due to various limitations in data providers, security protocols, and identity services. Network, forest, and federation topologies also influence the authentication flows. Familiarity with the authentication protocols and capabilities, delegation limitations, and possible workarounds is an indispensable prerequisite to delivering a positive BI user experience across the entire Microsoft BI solution stack in enterprise environments.
To review the document, please download the Microsoft BI Authentication and Identity Delegation Word document.
Connectivity Issue: "A connection cannot be made to redirector. Ensure that ‘SQL Browser’ service is running"
SSAS Named Instance running on cluster with two nodes, on one instance we are able to connect SSAS using Name Instance but when you failover to other node and try to connect we get error message
SSAS Cluster Virtual Server Name – SSASVirtualServer
Instance is MySSAS
When NodeA is owner of SSASVirtualServer and we connect SSASVirtualServer\MySSAS it works but when we failover SSASVirtualServer to NodeB and try to connect SSASVirtualServer\MySSAS it fails with error:
A connection cannot be made to redirector. Ensure that ‘SQL Browser’ service is running.
The startup of SQL Browser account does not have permission to access msmdredir.ini. The startup account of SQL Browser should have both Read and Write permission to the ASconfig folder or its child objects.
By default, the SQL Browser will periodically check and update the 90\Shared\ASConfig\msmdredir.ini file to ensure it knows the named instance SSAS info(port..etc) and it will translate client who needs to connect to the named instance SSAS to the correct name and port.
a. If the SQL Browser is running under "NT Authority \local service" account, ensure the account has permission (read/write) to the C:\Program Files (x86)\Microsoft SQL Server\90\Shared\ASConfig folder and its child objects. If the SQL Browser is run under other account, please ensure the same.
b. If you are not sure about permission change from Local Service to Local System and restart services.
Today, we have resolved issue of same nature – thanks Saman Alaghehband for his time and patience
Note: On Cluster Environment it is always recommended to connect using SSAS Virtual Server Name