I have two cube and i would like to get data from both cube and combine the results from both cubes to get final result to display result in SSRS reports like we can do in Stored procedure using temporary tables/Joins.Is there any way in SSAS to combine the data from multiple cubes? Data needs to be retrieved from the cubes based on the user inputs.
View 3 RepliesI need to extract data from SSAS' cubes into a SQL Server table.
I already read examples using Linked server (with openquery), SSIS, etc. However, the result always return as many columns per dimension as levels. I need to extract all members of a dimension in a column. E.g., when excecuting the following MDX query in Adventure Works 2014:
select [Measures].[Sales Amount] on columns,
Non Empty [Date].[Calendar].members on rows
from [Adventure Works]
I would like to get this result (MDX query in SSMS), but with keys displayed intead of names:
But I get this (MDX thru openquery or SSIS):
I'm exploring creating local cube files, .cub from an excel sheet with tables.Would SSAS be able to create one cube from taking data from a 1 way table (A, B, C), a 2 way table (AxB, BxC, AxC) and a 3 way table (AxBxC)?
View 4 Replies View Related
Hi,
Can anybody tell me How to access Analysis Service 2005 cubes through normal Stored Procedure.
Basically can write a stored procedure that we normally write in database service and use it access the Analysis Service 2005 cubes.
Is it possible
Regards,
gokul
I have two ssas databases with same number of cube in them. Cube names are also same. I want to merge/combine these two as one so that my reporting application may see a single database having measures/dimensions from both databases.Is it possible?I am doing this to achieve loose coupling between base and regional development work. if above is not possible, any way to achieve minimum dependency between a base and a regional project.
View 3 Replies View RelatedIs there any way to track the information about the connections to SSAS cubes through local Excel files (BI usage).
OPreviously, we are tracing the information about the BI usage through the BI SharePoint site. Now we want track the users who are using through local excel files .
Can applications connect to SSAS Tabular mode cubes through ODBC drivers? I have been asked the question specifically regarding SAS, and I can see (for example) SAS Enterprise Guide has the ability to connect to sources via ODBC, so I now need to find out if there are such things as ODBC divers for SSAS (specifically Tabular).
View 3 Replies View RelatedI want to call my stored proc for that last 4 months. Basically all I need to do is pass each month's first date and it will do the rest. Should I shove this into a UDF first? I'm not sure if I can do that. The struction is here behind my stored proc: http://www.webfound.net/storedproc.txt
EXEC IT_Get_Dashboard_Monthly '2006-05-03 12:03:43.910' <-- change to UDF or leave it? Then how can I loop and change each month to cover the last 4 months?
I also need to ensure all 4 values returned in each interation show up in one row in the final result set that is produced
I have defined a stored procedure with one parameter. With this parameter I'm able to controll which year of the sales amount data should be selected. This works fine.
Now I want to implement this stored procedure as the source of the partitions. But if I do this I get an error. The syntax-check says, that everything is fine. But if I want to calculate the partition with this command: "exec dst.fact_umsatz_year 0" get the following error (in German):
OLE DB-Fehler: OLE DB- oder ODBC-Fehler : Falsche Syntax in der Nähe von ')'.; 42000; Falsche Syntax in der Nähe des exec-Schlüsselworts.; 42000.
Fehler im OLAP-Speichermodul: Fehler beim Verarbeiten der FACT Umsatz Pivot View-Partition der Anzahl Kunden-Measuregruppe für den Vertrieb-Cube aus der OLAP AS-Datenbank.
New to SSAS so, have some not so intelligent ???
We have created Dimensional Model within SQL 2005 and created SSIS packages to load. We have Pk's on Dimensions and FK's on one Fact table. We are thinking about adding clustered index to Fact that consists of the FK's and order by the Hierarchy within the Fact by Dimension. IS THIS A GOOD IDEA IF WE DRILL DOWN AND DRILL THROUGH = QUESTION #1
We build a CUBE from the dimenionsal model and we do not know how to build indexes on the cube. SO DOES SSAS AUTOMATICALLY BUILD INDEXES IT THINKS IT NEEDS ON THE CUBE AND DO YOU KNOW WHAT THEY ARE?? ALSO IF WE HAVE INDEXES ON DIMENSIONAL MODEL DO THOSE GET USED BY SSAS CUBE THAT WE CREATED WITH AUTO BUILD??? = QUESTION #2..
THank you
Hi,
I would like to build an SSRS report from two different cubes (SSAS 2005). These cubes are contained in two different SSAS databases.
To do this, i created a dataset for each cube throught MDX expressions.
These datasets use common parameters defined in my report.
Thus, i try to integrate in my report chart datas from the two datasets, but my chart only target one dataset.
Is it possible to create a kind of dataset view to join my two datasets ?
Or is it possible to join the cubes from two different SSAS databases in an MDX expression to put all my datas in a single dataset ?
thx for your help.
Zohir
I never got an answer on this so I am reposting this issue. Also, please note that I have consistently reproduced this issue on multiple perspectives within and accross multiple cubes on 3 different SQL Server Instances. I have also had no problem with perspectives within Report Models that were not generated from an SSAS Cube.
Specifics:
=========
I have created a simple cube in BI Studio against an Oracle Relational Data Warehouse. In this cube I have created a perspective in which I have selected (included) only a small subset of measures and dimension. When I view this perspective on the Browse Tab of BI Studio, only the entities that were included in the perspective were available for report construction as was expected.
Next, I generated a model for this cube in Report Manager. Now here is the problem, I then opended the model in Report Builder and selected the same perspective from above, but all of the Entities in the cube were displayed including the fields I explicitely did not include in the perspective. I then looked at the .smdl file describing the Model and it looked like in the Perspective description section all of the cube's entities were included, even the ones that should not have been included. It seems as if the problem is ocurring durring the model generation. I also tried generating the model in Management Studio and it seems to be doing the same thing.
Any ideas on how to fix this? Could I be doing something wrong(probably)? I have to give a presentation soon and this is a big deal for the Project Stakeholders.
By the way, I am using the 180 Day Evaluation of Sql Server 2005 with SP2 CTP installed.
Hi,
I have two databases DB2006, DB2005.I have the Stored Procedure getdata which has the 2 parameters startdate and end date.This Stored procedure exist in all databases.
STored procedure CallGetdata
@startdate datetime
@enddate datetime
If startdate < 1/1/2007 the call getdata in the DB2006
if startdate <1/1/2006 then call getdata in the DB2005.
Here the problem is if startdate is 6/1/2005 and Enddate is '3/1/2006' then combine the stored procedure results from the DB2006 and DB2005 databases.
I have one idea i.e create a temp table and insert the two Stored procedure results into it.
Create #table1(name varchar(20))
insert into #table1 exec DB2006.dbo.getdata
insert into #table1 exexc DB2005.dbo.getdata
Select * from #table1
drop table #table1.
Anyone please give me better idea than creating temp table.
Thanks in advance
help please-combine tow stored procedure to one
i explain
1) i have one stored procedure that create mod cycle shift pattern from middle of the month to end of the month
2) second stored procedure that create mod cycle shift pattern from middle of the month to START of the month
i need to create ONE table for One full month
so i need to run the first stored procedure and go to the second stored procedure
at the end filling only one table !! and put one date the '18/02/2008'
this is my stored procedure that create mod cycle shift pattern from middle of the month to end of the month
Code Block
DECLARE
@shifts_pattern TABLE
(
[PatternId] [int] IDENTITY(1,1 ) NOT NULL, [patternShiftValue] [int]NOT NULL
)
declare
@I int
set
@i=0
while
@i < 5
BEGIN
INSERT INTO @shifts_pattern ([patternShiftValue] )
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8
set
@i=@i+1
end
-- select * from @shifts_pattern
--- end shift pattern
--------------------------------
DECLARE
@End_Date datetime
SET
@End_Date = DATEADD(m ,2,GETDATE()-DAY (GETDATE()) + 1)- 1
declare
@empList
TABLE
( [empID] [numeric](18, 0) NOT NULL,[ShiftType] [int]NULL,[StartDate][datetime]NOT NULL,[EndDate] [datetime] NOT NULL)
INSERT INTO
@empList ([empID], [ShiftType],[StartDate],[EndDate])
SELECT 111111,8,CONVERT(DATETIME, '18/02/2008', 103), CONVERT(DATETIME, @End_Date, 103)
--SELECT 222222,2,CONVERT(DATETIME, '14/02/2008',103), CONVERT(DATETIME, @End_Date,103) UNION ALL
--
--SELECT 333333,5 ,CONVERT(DATETIME, '27/02/2008',103), CONVERT(DATETIME, @End_Date,103) UNION ALL
--
--SELECT 444444,5 ,CONVERT(DATETIME, '26/02/2008',103), CONVERT(DATETIME, @End_Date, 103)
--------------------------------
-- create shifts table
declare
@empShifts
TABLE
( [empID] [numeric](18, 0) NOT NULL,[ShiftDate] [datetime]NOT NULL,[ShiftType] [int]NULL
,[StartDate] [datetime]NOT NULL,[EndDate] [datetime]NOT NULL)
DECLARE
@StartDate datetime
DECLARE
@EndDate datetime
Declare
@current datetime
DEclare
@last_shift_id int
Declare
@input_empID int
----------------- open list table for emp with curser
DECLARE
List_of_emp CURSOR FOR
SELECT
emp.empId,emp.ShiftType,emp.StartDate,emp.EndDate FROM @empList emp
OPEN
List_of_emp
FETCH
List_of_emp INTO @input_empID , @last_shift_id ,@StartDate,@EndDate
SET @current = @StartDate
-----------------
-- loop on all emp in the list
while
@@Fetch_Status = 0
begin
-- loop to insert info of emp shifts
while
@current<=@EndDate
begin
INSERT INTO @empShifts ([empID],[ShiftDate],[ShiftType],[StartDate] ,[EndDate])
select @input_empID ,@current,shift .patternShiftValue ,@StartDate,@EndDate
from @shifts_pattern as shift where PatternId=@last_shift_id+1
-- if it is Friday and we are on one of the first shift we don't move to next shift type .
if (DATENAME(dw ,@current) = 'Friday' ) and
EXISTS(select ShiftType from @empShifts where ShiftDate=@current and empID= @input_empID and ShiftType in ( 1,3,5))
-- do nothing
--set @last_shift_id=@last_shift_id
print ('friday first shift')
ELSE
set @last_shift_id=@last_shift_id+ 1
set @current=DATEADD( d,1, @current)
end
FETCH
List_of_emp INTO @input_empID ,@last_shift_id,@StartDate,@EndDate
-- init of start date for the next emp
set
@current = @StartDate
end
CLOSE
List_of_emp
DEALLOCATE
List_of_emp
select
empID,shiftDate,DATENAME (dw,shift.ShiftDate ), shiftType from @empShifts as shift
RETURN
TNX
Hi all,
I am working on datawarehouse
using sql server analysis manager.
I created a cube ..that is working fine
but now i have to distribute to end users
so how to do it and how many ways we can do that
1)can we make that .cub file
2)how can give access to endusers
without giving access to database
3)how to host a cube and access from
excel or any other software
Thanks
J
I need to use cubes in VS 2005 web project. I have no clue how to create cubes or use them in my web page. I am familiar with Reporting Services but not cubes.
thanks in advance!
Hi, i want to back up my SQL Server database and the cubes i've made on the anaysis services
i have backed up the SQL server database wihtthe wizard, and i do not know how to back up the cubes..
Is it possible, and if so, HOW??
thanx
reply @
nouman@emirates.net.ae
how can I process and update cubes automaticly every night ?
Thanks
How to get data from cubes in analyses manager to MS access using forexample ODBC ? Does existe any other posibilities to get data from cubesdirectly to the accees ?thanksKris
View 1 Replies View RelatedOne of my client requires TDS for all databases. My BI product has reports with OLAP Cube as data source.
How about using BitLocker for the HDD where the abf is physically located.
I work with 2 cubes on live and one for development. I've set the roles in the live cube (using SQL Server management studio). If i go to the design mode of the cube the new roles are not there, so deploying the cube will not update the development cube with the roles. How one can easily copy roles between cubes. (adding them manually would be a lot of work because there are quite a lot of different roles).
View 5 Replies View RelatedI am trying to select from 2 cubes. in one condition - select .. From CubeX, and in sec Condition Select .. From CubeY.
I tried to put the name of the Cube as parameter:
SELECT ...
FROM @CurrentCube
in ssrs source and i got the message:
------------------------------
Failed to parse the query to detect if it is MDX or DMX. Error: 'Query (148, 6) Parser: The syntax for '@CurrentCube' is incorrect.' (MDXQueryGenerator)
------------------------------
Also tried to get the name of the Cube as Member (I have a property with the name of the cube),and than using the member to the select :
WITH MEMBER [CurrentCube] AS
...[dim name].CURRENTMEMBER.PROPERTIES("Cube")
SELECT ...
FROM [CurrentCube]
It didnt work too.. Is it Possible at all make the Cube-name dynamic?!
Hi,
I have some questions about SQL Servers 2000 and 2005 compatibility.
In my configuration I have to use both servers.
The cubes are stocked in 2005 server.
May I transfer from 2005 to 2000 Analysis Services the cubes?
If yes, what is the procedure? The result of migration is the same in the two different versions?
If not, how can I solve this problem?
Thanks in advance.
I am getting partition process error in one of my cubes. I don't have any clue what could be the workaround with this.
View 2 Replies View RelatedHow can I UNION two MDX query results which are deriving from 2 cubes?MDX queries will return same ROW information and only Measure names will be different.
View 8 Replies View RelatedHi All
I have a stored procedure, sp_GetNameDetail, which return a one row, multiple columns result set.
Yet I have another storede procedure which would call sp_GetNameDetail, and would like to access this result set. Is there a way I can do this?
Thanks,
I am attempting to create som OLAP cubes using the NAV 2009 SP1 database. I am using Jet Enterprise with TimeXtender4 to build the cubes.
View 5 Replies View RelatedI am running into a problem where Excel 2010 is freezing when I try to work with data coming from a Microsoft Dynamics AX 2012 R3 pre-built cube.
What is happening is that when I go to add a second dimension to my pivot table, excel locks up and becomes completely unresponsive for at least 2 hours. If I add the dimension to the table before the measure though, everything is fine.
For Example, I am trying to build a sales report with the product name and customer name as dimensions, and Sales as the measure. If I add the product name to my pivot table as a column and then sales as a measure, Excel will freeze when I try to add the customer dimension. However, if I add both dimensions to the pivot table first and then the measure, Excel seems to work fine.
My Power view reports do not show the grand totals correctly. I think my issue relates perfectly to [URL]. However we have updated service pack 2 on both the sql data warehouse and the ssas box. They are 2012. However the power view is still incorrect. Pivot tables seem to work just fine. In AS I have a 2 separate measure groups that has a total count. I then use mdx to bring those two together. Something I have done many times in past with as 2008. My mdx looks like this below. I then hide Total Count EC (from measure group 1) and Total Count SF (from measure group 2). This way I have 1 total count value and they can use another dimension to see the two separate if needed.
CREATE
MEMBERCURRENTCUBE.MEASURES.[Total
Count New] AS
//
([Measures].[Total Count] + [Measures].[Total Count SF]),
DISPLAY_FOLDER
=
'Totals',
ASSOCIATED_MEASURE_GROUP=
'Report',
FORMAT_STRING
=
"#,#0",
VISIBLE=
1;
The measure Total Count EC works just fine but the Total Count (MDX) does not . All in Power Pivot works fine but in Power View the grand totals are not correct.
Hello evry1.
i m new to SQL Server2000. plzz tell me where i m
wrong in this SP . this procedure is not giving any
error but it is not showing any result. plzz help me n
give me any idea if u know how i can solve my problem
best regards
sadaf
n here is the procedure
CREATE PROCEDURE Search12
( @signup char(50),
@user_name1 [char](50),
@gender1 [char](6),
@place1 [char](100),
@email_address1 [char](50),
@relegion1 [char](50),
@political_view1 [char](50),
@passion1 [varchar](150),
@sports1 [varchar](150),
@activities1 [varchar](150),
@books1 [varchar](150),
@music1 [varchar](150),
@tv_shows1 [varchar](150),
@movies1 [varchar](150),
@cuisines1 [varchar](150),
@intrested_in1 [varchar](150),
@education1 [char](100),
@college_university1 [char](50),
@occupation1 [char](50),
@industry1 [char](50),
@job_desc1 [char](50),
@career_intrest1 [char](100),
@latitude1 [decimal],
@longitude1 [decimal])
AS
if (@user_name1 is not null and len(@user_name1) > 0)
begin
select * from [profile] where [user_name] like
'%@user_name1%' and place like '%@place%' and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@gender1 is not null and len(@gender1) > 0)
begin
select * from [profile] where gender = @gender1 and
place like '%@place%' and latitude=@latitude1 and
longitude=@longitude1 and signup_name != @signup;
end
if(@email_address1 is not null and
len(@email_address1) > 0)
begin
select * from [profile] where [email-address] like
'%@email_address1%' and place like '%@place%' and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if(@relegion1 is not null and len(@relegion1) > 0)
begin
select * from [profile] where relegion = @relegion1
and place like '%@place%' and latitude=@latitude1 and
longitude=@longitude1 and signup_name != @signup;
end
if (@political_view1 is not null and
len(@political_view1) > 0)
begin
select * from [profile] where political_view =
@political_view1 and place like '%@place%' and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@passion1 is not null and len(@passion1) > 0)
begin
select * from [profile] where passion like
'%@passion1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if(@sports1 is not null and len(@sports1) > 0)
begin
select * from [profile] where sports like '%@sports1%'
and place = @place1 and latitude=@latitude1 and
longitude=@longitude1 and signup_name != @signup;
end
if (@activities1 is not null and len(@activities1) >
0)
begin
select * from [profile] where activities like
'%@activities%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@books1 is not null and len(@books1) > 0)
begin
select * from [profile] where books like '%books1%'
and place = @place1 and latitude=@latitude1 and
longitude=@longitude1 and signup_name != @signup;
end
if (@music1 is not null and len(@music1) > 0)
begin
select * from [profile] where music like '%@music%'
and place = @place1 and latitude=@latitude1 and
longitude=@longitude1 and signup_name != @signup;
end
if(@tv_shows1 is not null and len(@tv_shows1) > 0)
begin
select * from [profile] where tv_shows like
'%@tv_shows1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@movies1 is not null and len(@movies1) > 0)
begin
select * from [profile] where movies like '%@movies1%'
and place = @place1 and latitude=@latitude1 and
longitude=@longitude1 and signup_name != @signup;
end
if (@cuisines1 is not null and len(@cuisines1) > 0)
begin
select * from [profile] where cuisines like
'%@cuisines1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@intrested_in1 is not null and len(@intrested_in1)
> 0)
begin
select * from [profile] where intrested_in =
@intrested_in1 and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@education1 is not null and len(@education1) > 0)
begin
select * from [profile] where education
like'%@education1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@college_university1 is not null and
len(@college_university1) > 0)
begin
select * from [profile] where institution like
'%@college_university1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@occupation1 is not null and len(@occupation1) >
0)
begin
select * from [profile] where occupation like
'%@occupation1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@industry1 is not null and len(@industry1) > 0)
begin
select * from [profile] where industry like
'%@industry1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@job_desc1 is not null and len(@job_desc1) > 0)
begin
select * from [profile] where job_desc like
'%@job_desc1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@career_intrest1 is not null and
len(@career_intrest1) > 0)
begin
select * from [profile] where career_intrest like
'%@career_intrest1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
GO
Hi!How can I save a result set from a stored procedure into a table?Can you show me some examples?Thank you!Darko
View 2 Replies View Related
i made a new stored procedure and execute it. it works great but where can i see the result ?
i want to see the number of Count(*) in a window inside the SQL Server Managment studio, can i ?
my stored procedure is as follows :
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[GetTotalMovieCreations]
AS
select count(*)
from tbl_Animations
where statusID = 1
and ToolID = 1
and ToolID = -1
and isDeleted = 0
thanks...
In Sql Server 2005 Express I have this table:CREATE TABLE [dbo].[Sections](
[SectionID] [int] NOT NULL,
[DocumentNo] [smallint] NULL,
[SequenceNo] [smallint] NULL,
[SectionNo] [smallint] NULL,
[DocumentTypeID] [smallint] NULL,
[SectionText] [ntext] NULL)
Each paragraph of text (SectionText) is in its own row
(SectionNo) Each primary document has a DocumentTypeID of 1 with
three subdocument types (2=Index, 3=Background, 4=Report).I run this query and return a collection of single rows from various documents grouped together by DocumentNo: SELECT *
FROM Sections
WHERE CONTAINS (SectionText, 'exercise')
ORDER BY DocumentNo
For each row that contains the search term, I would like to
return the full document (all rows as parapraphs within one row of
returned data). In other words, I want to reconstitute the full
document as it existed prior to being inserted into the database with
paragraph separation. For exampe, if the search term is in row 3
of DocumentNo=5, DocumentTypeID=2, I want to return all the rows of
that document in one block of text that retains paragraph format
(preferablly with a line break and carriage return between
paragraphs). How can this be done?