Analysis :: Dynamic MDX - Select From 2 Cubes
Sep 2, 2015
I 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?!
View 5 Replies
ADVERTISEMENT
Feb 13, 2003
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
View 6 Replies
View Related
Jan 6, 2006
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!
View 4 Replies
View Related
Aug 7, 2002
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
View 1 Replies
View Related
Oct 11, 2004
how can I process and update cubes automaticly every night ?
Thanks
View 1 Replies
View Related
Jul 20, 2005
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 Related
Apr 30, 2015
One 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.
View 6 Replies
View Related
Jun 5, 2007
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 Related
Oct 17, 2007
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.
View 3 Replies
View Related
Jun 2, 2015
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 Related
Nov 12, 2015
How 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 Related
Jun 19, 2015
I 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):
View 4 Replies
View Related
Jun 29, 2015
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
Feb 13, 2012
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 Related
Mar 12, 2008
I hope some of you can point me in the right direction. I am new to all this and
working on a project using Cubes.
The cubes need to be generated in a dynamic fashion as the contents
(that means field) in the fact tables change change every quarter and are
based off of a external application.
The base strucutre stays the same though. So we thought we would export the
XMLA representation of the cube and then munge (word for alter) the data in the
XMLA file and send it someplace to create the new cube on a SQL 2005 server.
This seemed easy enough but I have found no real information on how to
run some piece of code that would create the cube based on this XMLA code.
On another note is there a parsing object for the XMLA so i dont have to right one from
scratch?
Thanks,
Bob
View 1 Replies
View Related
Jul 15, 2015
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 Replies
View Related
May 7, 2015
I 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.
View 2 Replies
View Related
Sep 5, 2007
Hi,
I would like to know if is possible to create dynamic reports based on cubes. What i mean is,after creating a cube with a couple of dimensions and measure if is there any way to give the normal users on the report manager or report builder the freedom to choose their own dimensions/measure so they can output the report with the choosen criteria.
Thanks.
View 3 Replies
View Related
Apr 2, 2007
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
View 1 Replies
View Related
Nov 12, 2015
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.
View 3 Replies
View Related
Sep 29, 2015
Is 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 .
View 9 Replies
View Related
Aug 26, 2015
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 Related
Jul 7, 2015
we are using ASMCD to sync our cubes from a Cubebuild Server to a Reporting Server. We migrated some days ago from 2012 to 2014. We are also using rel. Engine with StoredProcs for calling and executing xp_cmdshell for the Ascmd.Actually all works fine for our smaller cubes. For the bigger one we got the following issue. The cube is being synced but the proc delivers error.In the log we found this entry:
(7/7/2015 11:26:22 AM) Message: The directory '?D:SSAS_DATAGPMR Local CNTR.510.dbLocal Controlling Cube.0.cub' could not be deleted. Please check the directory for permissions.
(Source: ?D:SSAS_LOGmsmdsrv.log, Type: 3, Category: 289, Event ID: 0xC1210020)
(7/7/2015 11:26:34 AM) Message: The directory '?D:SSAS_DATAGPMR Local CNTR.510.db' could not be deleted. Please check the directory for permissions.
[code]...
View 2 Replies
View Related
Jun 6, 2006
Süleyman writes "I want to transfer databases with cubes and virtual cubes from SQL 2000 Analysis Server to SQL 2005 SQL Express.
1.how i can transfer the cubes?
2.how i can transfer the virtual cubes?
3.Why i see the tables(saw)and the cubes in SQL Managment only under Tables together and nothing under cubes?
Please help me
many thanks
Süleyman from Germany/Frankfurt am Main"
View 1 Replies
View Related
May 8, 2015
I have a dimension called as DIM1 which has list of all measures and has an attribute called as ATTRMDX Formula. The formula will be like
([DIM1].[ATTR Measure Code].&[M1],[Measures].[ATTR MEASURE VALUE])+([DIM1].[ATTR Measure Code].&[M2],[Measures].[ATTR MEASURE VALUE]).
I want to pass this formula to a calculated measure as given below -
MEMBER [Measures].[FormMeasure] as ([Measure].[ATTRMDX Formula].currentmember.MEMBERVALUE)
but I get the string value itself as output, but when I put the formula as a string in the calculated measure I obtain the value.
View 2 Replies
View Related
May 27, 2015
Is there a way to write such a query where we can declare the variable dynamically ? Currently I am using the query as shown below :
declare @pYear_Internal as NVarchar(100)
set @pYear_Internal = [D FISCALPERIOD].[FP CODE].[FP CODE]
WITH
MEMBER MEASURES.[REVENUE] AS [Measures].[TOTAL REVENUE]
SET LAST5YEARS AS STRTOMEMBER(@pYear_Internal).Lag(4) : STRTOMEMBER(@pYear_Internal)
[code]....
While executing the above query, getting the error - Query (1, 9) Parser: The syntax for '@pYear_Internal' is incorrect. It looks like it doesn't recognize DECLARE keyword as it does with SQL queries. I just want a query that runs directly against the server.
View 3 Replies
View Related
Jun 29, 2006
I have a package where I need a dynamic connection string for an Analysis Services connection manager.
I have implemented this successfully for a Text data source, and a SQL data source, but the same approach does not seem to be working for an AS connection.
I set some expressions for the AS connection manager (ServerName, InitialCatalog, even the entire ConnectionString itself), but they don't take. I don't get any errors, but the task processes the cubes for the AS connection as it was established at run time. The design time connection string changes don't appear to get evaluated. This seems to be an issue only for AS connections.
Let me know if you have any ideas - thanks.
View 2 Replies
View Related
Jul 31, 2015
I have a requirement where in i need to create a Database dynamically in SSIS, Database name is given in table.
View 3 Replies
View Related
Jun 17, 2015
I have created cube. 1 fact table and few dimensions including dimDate
I need to create a calculated member for variance.
Variance = SUM([Measures].[Amt]) starting from financial year beginning(2015-04-01 to current date) -
SUM([Measures].[Amt]) for the same period last year(2014-04-01 to current date last year)
How can I create this calculated member?
View 5 Replies
View Related
Nov 11, 2015
I am trying to write a query for the default values for month, and I am stuck in bringing out only the parameter values, the query I have , brings back the month on row and value on column, and I am trying to get the value out. and will ssrs accept it as a valid expression for a default value
WITH
MEMBER
[MEASURES].[PARAMETERVALUE]
AS
[Account].[Month Short Name].CURRENTMEMBER.UNIQUENAME
[Code] .....
I tried using the exp
=Parameters!MONTH.Value(0)
so as to return the first parameter in the parameter dataset , but i have the error the expression that ref the parameter MONTH does not exists in the parameter collection.
View 2 Replies
View Related
Sep 28, 2015
I have an issue related to SSAS security. We have an SSAS multidimensional cube which needs 3 types of security:
- Access to the entire cube => OK, based upon a role
- Restricted access to one department (= dimension) => OK, based upon a role
- Access to the entire cube, but with dynamic security on 2 measures.
Let's say, we have 2 departments (food and non-food). Users within food are allowed to see sales and pieces from the food department, but not from the non-food department.
It is not an option to restrict access to the non-food department because there are other measure which they have access to. I tried cell security, but this is very slow and generates multiple empty rows on my selections.
View 3 Replies
View Related
Jul 14, 2015
I have a requirement to make all of my KPI goals configurable by the end user - I would like to add the data to my datawarehouse so that it is updated in the cube whenever the cube is processed. I'm thinking I will need a table for each KPI that includes two columns (I will be setting goal by year):
Year
Value
In my cube I then create a measure group for each KPI (for some I will add a calculations to break down the yearly goal to lower levels of detail).I will add each measure to my Dimension Usage and relate to each of my time dimensions at the Year level.I currently have around a dozen KPIs - my above approach seems a bit messy i.e. requiring a table and measure group for each KPI (I guess I could at least put them all into one table in my relational db).
View 2 Replies
View Related
Aug 6, 2015
It seems simple but I'm not able to make it works. I've got a fact table with sites (DimImplantation) with allowed users (DimDroit) :
1 user can have access to many sites. I've tried these codes but only one worked and only in case where the user had only one site :
a) =DimImplantation[DimImplantation_ID]= LOOKUPVALUE (DimDroit[DimImplantation_ID]; DimDroit[Utilisateur];USERNAME() ) ==> worked for 1user with 1 site
b) =DimImplantation[DimImplantation_ID]= LOOKUPVALUE (DimDroit[DimImplantation_ID]; DimDroit[Utilisateur];USERNAME(); DimDroit[DimImplantation_ID];DimImplantation[DimImplantation_ID] ) ==> doesn't work and says 'not able to to identifie the value of DimImplantation[DimImplantation_ID]
in the current context.
c) CALCULATE(VALUES(DimDroit[DimImplantation_ID], SUMMARIZE('DimDroit',DimDroit[Utilisateur]), DimDroit[Utilisateur]=USERNAME()) ==> version for SQL2012. I've had 'End of input reached'.
View 3 Replies
View Related