sql-server-2012,ssas,ssas-tabular , Do I need to import the full data in SSAS tabular mode from my SQL database in DirectQuery mode?


Do I need to import the full data in SSAS tabular mode from my SQL database in DirectQuery mode?

Question:

Tag: sql-server-2012,ssas,ssas-tabular

I'm trying to build a Analysis service tabular project in tabular mode and want to use DirectQuery mode so that the queries are executed at the backend. When I click on the model, and select import data from source, I see option to retrieve the full data. Now I have a billion rows in my fact table and I dont want to import the entire data when building the model. Am I missing something here? DirectQuery in tabular , from what I understand, is similar to ROLAP storage mode in Multi Dimensional world, where there is no need for the process step and queries get real time data. So what's the point of importing all the data when building the model? If it is just to get the schema of the tables, why not just query the DB for schema of tables instead of importing the full data? Can someone explain?


Answer:

When you go through the Import From Data Source wizard, select Write a query that will specify the data to import. Write a query that imports only one row, SELECT TOP 1 * FROM <table_name>. That will import just one row and the schema.

enter image description here


Related:


INSERT single quoted value for all column types in SQL Server


sql-server,sql-server-2008,sql-server-2008-r2,sql-server-2012
Say you have a column of int (also bit, etc.) CREATE TABLE TEST (a int) It seems it doesn't matter if you insert value with '1' or 2 INSERT INTO TEST VALUES ('1') INSERT INTO TEST VALUES (2) Is there any side-effect if I insert/update values by single-quoting them (e.g.'value')...

Intersect Select Query in MDX


ssas,mdx,olap
I want to have customers intersection in two mdx querys. 1. SELECT [Measures].[Cs] ON 0 ,NonEmpty([Customers].[Customer].MEMBERS) ON 1 FROM [sfe cube] WHERE ( [Calend].[Period].&[201506] ,[Customers].[BP Territory].&[38UZ1] ,[Materials].[Brand].&[Coca-Cola] ); 2. SELECT [Measures].[Cs] ON 0 ,NonEmpty([Customers].[Customer].MEMBERS) ON 1 FROM [sfe cube] WHERE ( [Calend].[Period].&[201506] ,[Customers].[BP Territory].&[38UZ1] ,[Materials].[Brand].&[Fanta Orange CSD] ); My attempted...

Finding records in main table that match records in another table in SQL Server


sql,sql-server,sql-server-2012
I have a SQL Server database with three tables: Trips, Slices, and Legs. Each Trip has a one to many relationship with Slices and Slices has a one to many relationship with Legs. Trips represents a full trip, a slice represents only the outbound or return portions of a trip,...

check multiple schedules for missing dates


sql-server,sql-server-2012
I have a range of dates that I want to see if someone is working or not. I would like to see an entry for each person against each date, with null or zero if they are not working E.g. if dates are last week, 6/1 to 6/7, then I...

Fastest way to add a grouping column which divides the result per 4 rows


sql,sql-server,tsql,sql-server-2012
If i have a resultset like this for example (just a list of numbers) : 1,2,3,4,5,6,7,8,9,10,11 and I would like to add a grouping column so i can group them per 4 like this : 1,1,1,1,2,2,2,2,3,3,3 (The last one in this examle does not have a forth element, so that...

Verify data integrity for varbinary column


sql,sql-server,image,sql-server-2012
I am inserting a .jpg into a varbinary(max) column in SQL Server 2012 using the following command: INSERT INTO Employees VALUES(5, (SELECT * FROM OPENROWSET(BULK N'C:\4.jpg',SINGLE_BLOB) AS image)) It inserts a record in the table. Then I used the following command to restore the file from db to file system....

Not getting the correct SUM values in SQL Server 2012 when using a PIVOT


sql-server,sql-server-2012,pivot,distinct,aggregate-functions
I am trying to create a query that will pivot over some rows but will SUM some columns and then group them together. I've used the PIVOT function before but I am running into issues when my result set contains similar values. This is SQL Server 2012. Sample code: CREATE...

SQL - Group by Elements of Comma Delineation


sql,tsql,sql-server-2012
How can I group by a comma delineated list within a row? Situation: I have a view that shows me information on support tickets. Each ticket is assigned to an indefinite number of resources. It might have one name in the resource list, it might have 5. I would like...

ASp.Net Identity Role manager


asp.net,vb.net,webforms,sql-server-2012,asp.net-identity
I am using Visual Studio 2013. I have a ASP.net (vb) Webforms site with asp.net identity. I trying to create a page that manages User role and create roles. I cant find any help online for this when it comes to web forms. This code works for asp.net membership but...

Parsing Dates in SQL


sql,sql-server-2012,sql-server-2014
I have a column in my database that is currently a varchar. the data looks like this: Thu, 4 Jul 2013 09:18:24 Thu, 11 Jul 2013 10:07:01 Tue, 28 Jan 2014 11:38:37 Fri, 26 Jul 2013 14:13:42 I want to be able to convert that to a date so that...

Format Numbers in Multiple columns of SSRS Report


reporting-services,sql-server-2012,ssrs-2008
Currently I am working on a ssrs report. The table in report is having about 30 columns. For each column I have to modify the number format (to either 2 decimal or no decimal numbers). I can do it by click on every column and modify the number property in...

SSRS Subscription: newly defined fails, old one works


email,sql-server-2012,subscriptions,reporting-services-2012
Newly Created subscription for new report fails with error " Failure sending mail: The specified string is not in the form required for an e-mail address.Mail will not be resent." It is defined in the same way the existing one, using same ReportServer with unchanged config since it started working....

SQL Server : Recursive Advanced Query on two conditional logics Not showing hierarchy relationship


sql,sql-server,tsql,sql-server-2012,common-table-expression
Recently I got a challenge to resolve in SQL Server 2012. This is the background of the problem. We are maintaining a self referencing entity (hierarchy) in our product entity. Each product is having parent and child relationship. Product is having special grouping called master products which derive based on...

Approaching mixed granularity date dimensions for operational periods in cube design


ssas,olap,olap-cube
I am building a cube in SSAS, modelling (amongst other things) activity of engineering teams. I have a fact table (TeamActivity), with facts such as Mileage and TimeOnSite on a DAILY granularity. This references a date dimension table (DimDate). DimDate contains typical attributes so data can be analysed by calendar/fiscal...

create single query to fetch data from tables as seperate rows


mysql,sql-server,sql-server-2012
i am thinking if there is a better way to query product, product options and product option choices from database my database structure is like: product table (product_id, product_name) product_options table (option_id, option_name, product_id) product_optionChoices (Choice_id, choice_name, Option_id) i want a single query that return all product and options in...

How to reference SQL snippet multiple times in a query?


sql-server,tsql,sql-server-2012
I need to convert some stored procs to views and the stored procs have a lot of DECLARE statements which create constants that get referenced later in the query. For example SELECT @FIRSTDAYLASTYEAR = DATEADD(YEAR, DATEDIFF(YEAR, '1901-01-01', DATEADD(YEAR, -1, getdate())), '1901-01-01') I need to refer to @FIRSTDAYLASTYEAR in a single...

How to extract *Area Code* from a phone number in SQL [closed]


sql,sql-server-2012
I have a SQL table with following for Phone Number column. (201) 411-5200x4002 1 (5410) 651-8868 x 5 1-308-987-5920 x8031 2118477656 2195992321x289 313-877-5916 I need to extract out Area Code out of these phone numbers. Area code is first three digits of number. But if we have country code too...

How to search a column name in all tables in a database in SQL Server 2012? [duplicate]


sql,sql-server,sql-server-2012
This question already has an answer here: SQL Server search for a column by name 5 answers Is it possible to search like below: Like '%tax%' ...

Symmetric Key Decryption returning values with space between characters


c#,asp.net,sql-server,sql-server-2012,encryption-symmetric
I am using following Scaler-valued function to decrypt data that is encrypted using a symmetric key: USE [DBNAME] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[DECRYPTDATA] ( @CipherText NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @Result NVARCHAR(MAX) SELECT @Result = CONVERT(VARCHAR(MAX),DECRYPTBYKEY(@CipherText)) RETURN @Result END And I...

Combine columns from three different tables into a single column


sql,sql-server-2012
I am new to SQL and I am not sure what to Google. I have three tables with different numbers of columns. I would like to combine these following three tables into a single column(no duplicates). Table1 Col1 Col2 Col3 1 a aa 2 b ab 3 c bb Table2...

SQL Server 2012 How to change the data type of a column from bit to datefield?


datetime,sql-server-2012,bit,data-type-conversion,sqldatatypes
I have a table Person with a column called onvacation. This column is of data type bit since it's a boolean in the code. It has values null, 0 and 1. I would like to change the data type of this column from bit to datetime so that all values...

Rename table via sp_rename or ALTER SCHEMA


sql,sql-server,sql-server-2012
I am going to perform a table-wide update on a huge table (+200Millon records) so I am going to populate the data via SELECT into a separate table, than drop the original and rename the new one. In one of the articles someone mentioned that it is better to create...

How to use OFFSET and Fetch without Order by in SQL Server


sql-server,sql-server-2012,sql-order-by,fetch,offset
I want use OFFSET and Fetch in my SQL server 2012 query.But without any order by.I can not use order by.Because my sort order will be lost. How can I use OFFSET and Fetch without order by and row number and where in my query? My 2 select tables have...

Errors in the OLAP storage engine: The attribute key cannot be found when processing


ssas,foreign-key-relationship,data-warehouse,olap-cube,dimensional-modeling
I know this is mainly a design problem. I 've read that there is a workaround for this issue by customising errors at processing time but I am not glad to have to ignore errors, also the cube process is scheduled so ignore errors is not a choice at least...

Split string and number columns


sql,sql-server,sql-server-2012
Let's say I have a table such as ItemID ClassID ------------------------ 1 10, 13, 12 2 5, 7 and would like to copy the data to another table like so ItemID Numbering ClassID ---------------------------------- 1 1 10 1 2 13 1 3 12 2 1 5 2 2 7 Separating...

TSQL Table Value Function Parameter different behavior if parameter a variable


sql-server,tsql,sql-server-2012
I have a table value function in TSQL that works well if I pass a string but not if I pass a varchar. For example, this works: SELECT * from [dbo].[SplitString]('ta;aa;qq', ';') This does not work: declare @invarchar as varchar set @invarchar = 'ta;aa;qq' SELECT * from [dbo].[SplitString](@invarchar, ';') If...

Group Count in T/SQL


sql,sql-server,sql-server-2012
Source: CREATE TABLE #TempTab (Value INT, Value1 varchar(10), Value2 varchar(10), GRP varchar(10)) INSERT INTO #TempTab SELECT 1,'One','One','One' UNION ALL SELECT 1,'One','One','One' UNION ALL sELECT 1,'One','One','Two' UNION ALL SELECT 2,'One','One','One' UNION ALL SELECT 2,'One','One','Two' UNION ALL SELECT 2,'One','One','Three' UNION ALL SELECT 3,'One','One','One' UNION ALL SELECT 3,'One','One','One' Current query effort: SELECT Value,...

Compare only DATE from 2 datetime columns - sql


sql,sql-server-2012
How can I code to compare only the date (not time) from two different columns that is storing the date in 2015-01-10 01:20:00 format? Example. Select * from Admissions Where AdmitDateTime = DepartDateTime Above will try to compare 2015-01-10 01:20:00 to 2015-01-10 01:20:00 instead of only date 2015-01-10 to 2015-01-10....

How to group record by range and put it on rows


ssas,mdx
Below query compute the percent of work employee wise. i.e. employee on rows and percent of work on columns With Member [Measures].[EmployeeWisePercent] AS [Measures].[Hours] / ([Employee].[Employee].[All], [Measures].[Hours]) * 100 Member [Measures].[TotalHours] AS ([Employee].[Employee].[All], [Measures].[Hours]) SELECT NON EMPTY { [Measures].[Hours], [Measures].[EmployeeWisePercent], [Measures].[TotalHours] } ON COLUMNS, NON EMPTY { ([Employee].[Employee].[Employee].ALLMEMBERS ) }...

Make rows to columns in SQL Server for each record


sql-server,sql-server-2012
I have a large(ish) DB. Simply put customer records. Now I have two tables; one is CustomerInfo and PhoneNumbers. Some sample data would be, CustomerInfo ````````````` CustID | CustName | CustomerLocation --------+---------------+-------------------- 1 | Paul | Bristol 2 | Eugin | Bournemouth 3 | Francis | London PhoneNumbers ```````````` PhoneID...

SQL Query using FOR XML PATH that works right


sql,tsql,sql-server-2012
I have read the many posts regarding collapsing multiple table columns into a single column using FOR XML PATH, but I am not a database person and cannot make other posts' suggestions work. I expect my problem is easy to solve for an experienced SQL DB person. I have MS...

Dynamic Where Condition Based on Parameter


sql,sql-server-2012
I have a parameter based on which I need to include or exclude one condition in a where clause. If the CustomerType parameter is blank, then I need to include a condition CustomerType is null, otherwise I need to exclude that CustomerType is null condition. The SQL query is: IF(@CustomerType...

SQL varchar variable inserts question mark


sql,sql-server-2012
I've a database value that when inserted into a SQL variable, shows with question mark at the end !! can't find a reason?! declare @A varchar(50) = 'R2300529‏' select @A Results: R2300529? any explanation? i'm using SQL server 2012....

Where to declare a SET in SQL?


sql,sql-server-2012
I have been attempting to figure out where I can SET a variable in an SQLQuery. I have 2 different select statements that will have a different WHERE clause depending on the answer to an IF statement, but I would like to only have to run the query to make...

Photos as Varbinary(MAX) in SQL Server 2012 causes error 502753 in Access 2010


sql,ms-access,sql-server-2012,ms-access-2010,varbinarymax
I have an Access 2010 odbc front end with a SQL Server 2012 back end. My images (.bmp) are stored as Varbinary(Max). When I insert an image using my bound object frame in an Access form, the image displays on the form and report perfectly. I had a large number...

SQL Transaction Stuck open


sql,sql-server-2012
Hello I was creating a Stored PROC, so I went into a new Query typed out the following SQL to test before I create the Stored PROC and now when I type select * From InvoicesT; it just keeps trying without returning anything. I believe that the transaction is still...

SQL Server 2012 & Polybase - 'Hadoop Connectivity' configuration option missing


sql-server,hadoop,sql-server-2012
As described in the title, I am using SQL Server 2012 Parallel Data Warehouse with Polybase feature to try to access a HDInisght Hadoop cluster. As a starting point for every connection to Hadoop from SQL Server, I find to execute the command sp_configure @configname = 'hadoop connectivity', @configvalue =...

One Table - Two Fields - Same value - T/SQL


sql-server,tsql,sql-server-2012
Source: CREATE TABLE #TempTab (ID INT, Value INT, Value1 varchar(10), Value2 varchar(10), value_ INT, Value1_ varchar(10), Value2_ varchar(10), Value3_ varchar(10), Limit INT) INSERT INTO #TempTab SELECT 1, 1,'One','One',1,'One','One','Yes',2 UNION ALL SELECT 1, 1,'One','One',1,'One','Two','No',2 Current query try: SELECT t1.ID, CA.Value3_ FROM #TempTab t1 CROSS APPLY( SELECT Value, Value3_ FROM #TempTab t2...

SQL update statement:Subquery returned more than 1 value


sql,sql-server-2012
I have table named dbo.Movies and now i want update 3 rows This statement works only with one record declare @movietype nvarchar(100) = 'Thriller' declare @price real = 10 if @price < (select Price from dbo.Movies where MovieType = @movietype) begin update mo set mo.Price = Price - @price FROM...

SQL Insert Date Mystery on 2012 from 2005


sql-server,sql-server-2005,sql-server-2012
I have migrated a 2005 db to 2012 There is a stored procedure that run to insert todays date into a table IF NOT EXISTS(SELECT * FROM tblTime WHERE [Day] = DATEPART(day,GETDATE()) AND [Month] = DATEPART(month,GETDATE()) AND [Year]= DATEPART(year,GETDATE()) ) BEGIN INSERT INTO tblTime (Period,[Day],[Month],[Year],MonthPrefix) VALUES (CONVERT(VARCHAR(10), GETDATE(), 105), DATEPART(day,GETDATE()),...

Unable to execute the create statement generate by cursor


sql-server,sql-server-2012
DECLARE @COLNAME VARCHAR(100) DECLARE @CREATE VARCHAR (1000) DECLARE @TYPE VARCHAR(1) SET @COLNAME = '' SET @TYPE = 5 SET @CREATE = 'CRATE TABLE TABLE_TYPE_' + @TYPE + '(' DECLARE MyCursor CURSOR FOR SELECT Name FROM LAYOUT WHERE RecordType = @TYPE ORDER BY Start OPEN MyCursor FETCH NEXT FROM MyCursor INTO...

Retrieve the Return Result with Chronological Order Based on Parameter


tsql,stored-procedures,sql-server-2012
Goal: My request is the retrieve the return result from sp_Test as 8, 2, 4, 1 ,3 (take a look at picture 1) based on the chronological list from User-Defined Table Type dbo.tvf_id. Problem: When I execute the stored procedure I sp_Test I retrive the list that is from 1...

SQL Server Versions - 2012 vs 2014 [closed]


sql-server,oracle,sql-server-2008,sql-server-2008-r2,sql-server-2012
My team needs to get SQL Server as our backend database. I would like to find out if we should get 2014 or 2012 R2. The rest of the company is probably still SQL Server 2008 R2, Oracle and probably MySQL. I wonder if we get 2014 version which is...

Merge two SQL query results into one result


sql,sql-server,sql-server-2008,sql-server-2012
I have query like below: SELECT COUNT(*) AS AppleSupports FROM VendorItemPricing WHERE VendorName = 'Apple' SELECT COUNT(*) AS HpSupports FROM VendorItemPricing WHERE VendorName = 'HP' Above queries give me results like below: AppleSupports 63 HpSupports 387 How can make my query to get results in one row like below? AppleSupports...

Zeroth Row for a OLTP table


sql,sql-server,sql-server-2008,sql-server-2012
I have come across a OLTP Database where all the tables has a default row,value 0 for a primary key and other columns with its default value or a null value.I am not sure why is this row required in first place? can anyone explain what are the advantages and...

How to go backwards through the previous 12 months?


sql,sql-server,sql-server-2012
I need to create a report that checks through the previous 12 months only. I have this code which is working: DECLARE @date DATETIME = DATEADD(year,-1,GETDATE()) WHILE @date < GETDATE() BEGIN --do marvelous things SET @date = DATEADD(MONTH,1,@date) END ...however it brings the results back starting from June 2014 (as...

How to install ssas AdventureWorks DW with SQL Server 2008 R2


sql-server,sql-server-2008,sql-server-2008-r2,ssas,adventureworks
I am doing a proof of concept to explain the team using cubes improves the application's query performance. I am allowed to use VS 2008 BI tools. I am in need of the AdventureWorks2008R2 DW to be installed to write some mdx queries to query the cubes. I have landed...

Convert SSIS (Visual Studio 2013) to work on SSIS 2012


visual-studio-2013,ssis,sql-server-2012
Is there a way of converting a SSIS package created in Visual Studio 2013 to work on SSIS 2012? The start of my package looks like this: <?xml version="1.0"?> <DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:refId="Package" DTS:CreationDate="12/18/2014 8:53:33 AM" DTS:CreationName="Microsoft.Package" DTS:CreatorComputerName="LOCAL111" DTS:CreatorName="username1" DTS:DTSID="{C6D60123-5529-4BC1-B426-B219A0709EB7}" DTS:ExecutableType="Microsoft.Package" DTS:LastModifiedProductVersion="12.0.2430.0"...

SQL server Delete script order


sql,sql-server,sql-server-2012
My tables have the following relations As you can see FirstEntity can have multiple Transactions related record. Transaction is divided into two tables because it represents an inheritance hierarchy (Table Per Type in Entity Framework). I need to create a script which delete all record from FirstEntityTransaction and Transaction given...

how to sort alphanumeric data in sql..?


sql,sql-server,sql-server-2008,sql-server-2012
i have items table which is having Size as follows: 1sq.mm. 1.5sq.mm. 0.5sq.mm. 2.5sq.mm. 0.75sq.mm. 4sq.mm. 20mm 25mm 20mtr 75mm x 50mm 100mm x 50mm 100mm x 100mm 75mm x 75mm i wanted to display it has 0.5sq.mm. 0.75sq.mm. 1.5sq.mm. 2.5sq.mm. 4sq.mm. 20mm 20mtr 25mm 75mm x 50mm 75mm x...